How to calculate money needed for bills, by day

I'm trying to figure out how to budget my money so I can look to see how much money I need on that particular day for bills.

I've had an excel sheet I've used for a while that has all my bills listed out, how much they are, how often they occur, and some totals that for each month tell me how much my bills were. This worked for a while when I was using two separate accounts (one for bills, and one for "spending") but now I am switching to just one account.

Because of this change I would like to be able to just look at my spreadsheet and see that for the 10th day of the month (generalized months) I should have X amount of money in my account for bills and the remaining is available for spending.

Theoretically this amount needed for bills would be added to when each paycheck is received, and then subtracted from when each bill is paid. This process assumes bills are paid the exact same day each month.

I just can't wrap my head around how to setup a spreadsheet for this. OR Maybe the question should be is this even a wise way to go about tracking? It all boils down to how much spending money do I have available on this day?

• Why the switch to just one account? Jun 6, 2017 at 19:08
• Monthly account charges, and having to swap money around all the time. Jun 6, 2017 at 19:09
• I don't follow why it would be different for 1 account vs 2, does your current spreadsheet show how much you need on a given day for bills? Jun 6, 2017 at 19:10
• No it doesn't. Technically I had this need for specific day budget for a while, it's just the switch to one account for whatever reason made it feel more needed now. Jun 6, 2017 at 19:21
• A question like this would be on topic for Stackoverflow.com, using the Excel tag. You should attempt the question yourself [preferably, searching for similar questions first], and then post any difficulties you have as a new question there. Something like this would also likely get good responses from Superuser.com [Both SO.com & SU.com are part of the stackexchange network, if you aren't aware]. Jun 6, 2017 at 20:00

3 Answers

I think you might benefit from adopting a zero-sum budget, in which you plan where each dollar will be spent ahead of time, rather than simply track spending or worry about the next expense. Here's a pretty good article on the subject: How and Why to Use a Zero-Sum Budget. This is the philosophy behind a popular budgeting tool You Need a Budget, I am not advocating the tool, but I am a fan of the idea that a budget is less about tracking spending and more about planning spending.

That said, to answer your specific question, one method for tracking your min-needed for upcoming expenses would be to record the date, expense, amount due, and amount paid as shown here:

Then the formula to calculate the min-needed (entered in E1 and copied down) would be:

``````=SUM(\$C\$2:C2)-SUM(\$D\$2:D2)
``````

As you populate amounts paid, the `MinNeeded` is adjusted for all subsequent rows. You could get fancier and only populate the `MinNeeded` field on dates where an expense is due using `IF()`.

• I think you hit the nail on the head. This is what I was looking for, thanks! Good to have a name for this type of budget! Jun 6, 2017 at 20:54

If I understand you right, what you need is the minimum amount in the account until your next deposit. So for example, if today is the 10th and you get paid on the 15th, how much do I need to have in the account, so I know how much I can spend? That amount should be all of the bills that will be paid between today and the 15th. An alternative would just to keep a running balance and see what the minimum value is.

My personal finance software does that for me, but it's possible, although a little more complicated, in Excel. You'd have to find the date of the next deposit, and do a `SUMIF` looking for dates between today and that date. That's about as far as I can get without getting off-topic.

• Yes that is exactly what I was trying to say. The Sumif feature is interesting and might be what I need to look into. What finance software are you using for this feature? Jun 6, 2017 at 19:56
• @BlueCaret Quicken. Jun 6, 2017 at 19:59

Trying to figure out how much money you have available each day sounds like you're making this more complicated than it needs to be. Unless you're extremely tight and you're trying to squeeze by day by day, asking "do I have enough cash to buy food for today?" and so on, you're doing too much work.

Here's what I do. I make a list of all my bills. Some are a fixed amount every month, like the mortgage and insurance premiums. Others are variable, like electric and heating bills, but still pretty predictable.

Most bills are monthly, but I have a few that come less frequently, like water bills in my area come every 3 months and I have to pay property taxes twice a year. For these you have to calculate how much they cost each month. Like for the water bill, it's once every 3 months so I divide a typical bill by 3.

Always round up or estimate a little high to be safe.

Groceries are a little tricky because I don't buy groceries on any regular schedule, and sometimes I buy a whole bunch at once and other times just a few things. When groceries were a bigger share of my income, I kept track of what I spent for a couple of months to figure out an average per month. (Today I'm a little richer and I just think of groceries as coming from my spending money.)

I allocate a percentage of my income for contributions to church and charities and count this just like bills.

It's a good idea to put aside something for savings and/or paying down any outstanding loans every month.

Then I add these up to say okay, here's how much I need each month to pay the bills. Subtract that from my monthly income and that's what I have for spending money.

I get paid twice a month so I generally pay bills when I get paid. For most bills the due date is far enough ahead that I can wait the maximum half a month to pay it. (Worst case the bill comes the day after I pay the bills from this paycheck.)

Then I keep enough money in my checking account to, (a) Cover any bills until the next paycheck and allow for the particularly large bills; and (b) provide some cushion in case I make a mistake -- forget to record a check or make an arithmetic error or whatever; and (c) provide some cushion for short-term unexpected expenses. To be safe, (a) should be the total of your bills for a month, or as close to that as you can manage. (b) should be a couple of hundred dollars if you can manage it, more if you make a lot of mistakes.

If you've calculated your expenses properly and only spend the difference, keeping enough money in the bank should fall out naturally.

I think it's a lot easier to try to manage your money on a monthly basis than on a daily basis. Most of us don't spend money every day, and we spend wildly different amounts from day to day. Most days I probably spend zero, but then one day I'll buy a new TV or computer and spend hundreds.

Update in response to question

What I do in real life is this: To calculate my available cash to spend, I simply take the balance in my checking account -- assuming that all checks and electronic payments have cleared. My mortgage is deducted from my checking every month so I post that to my checking a month in advance. I pay a lot of things with automatic charges to a credit card these days, so my credit card bills are large and can't be ignored. So subtract my credit card balances. Subtract my reserve amount. What's left is how much I can afford to spend.

So for example: Say I look at the balance in my checkbook today and it's, say, \$3000. That's the balance after any checks and other transactions have cleared, and after subtracting my next mortgage payment. Then I subtract what I owe on credit cards. Let's say that was \$1,200. So that leaves \$1,800. I try to keep a reserve of \$1,500. That's plenty to pay my routine monthly bills and leave a healthy reserve. So subtract another \$1,500 leaves \$300. That's how much I can spend.

I could keep track of this with a spreadsheet or a database but what would that gain? The amount in my checking account is actual money. Any spreadsheet could accumulate errors and get farther and farther from accurate values. I use a spreadsheet to figure out how much spending money I should have each month, but that's just to use as a guideline. If it came to, say, \$100, I wouldn't make grandiose plans about buying a new Mercedes. If it came to \$5,000 a month than buying a fancy new car might be realistic. It also tells me how much I can spend without having to carefully check balances and add it up. These days I have a fair amount of spending money so when, for example, I recently decided I wanted to buy some software that cost \$100 I just bought it with barely a second thought. When my spending money was more like \$100 a month, lunch at a fast food place was a big event that I planned weeks in advance. (Obviously, I hope, don't get stupid about "small amounts". If you can easily afford \$100 for an impulse purchase, that doesn't mean that you can afford \$100 five times a day every day.)

Two caveats: 1. It helps to have a limited number of credit cards so you can keep the balances under control. I have two credit cards I use for almost everything, so I only have two balances to keep track of. I used to have more and it got confusing, it was easy to lose track of how much I really owed, which is a set up for getting in trouble.

1. Never ever ever rely on what the bank says your balance is. There are always transactions that haven't cleared yet and so don't show up in your bank's calculation of the balance. You know you wrote those checks or scheduled those payments. Keep an accurate checkbook. Every now and then I see how much money the bank thinks I have and I always find myself saying, "I wish I really had that much." Well, if you're talking about keeping a spreadsheet and tracking expenses, you're probably doing this.
• My main issue is, okay I need to buy that TV or Computer, then taking into account bills that have already been paid for the month and those that haven't, do I have enough today to buy it. That's more what I'm going for. Jun 7, 2017 at 14:45
• @BlueCaret See long comment.
– Jay
Jun 7, 2017 at 21:29
• That was helpful thanks! I think I am going to need to just rethink entirely how I'm going about budgets. Jun 7, 2017 at 21:37