In this post I’ll explain how my budgeting spreadsheets work. The first is the spreadsheet I use every pay day to determine how much I can spend and save that month. The second is a more long term budgeting tool that lets me see how much I need to pay in bills each year and how those bills affect my disposable income.
Every Pay Day
I’ve included an example of an Excel file in this post that you can download: Example Budget Spreadsheet. Remember that this spreadsheet is set up for someone who is paid monthly. You’d need to change it if you are paid weekly or fortnightly. The amount of the bills and the income are made up.
Hopefully it’s not too difficult to understand. The Recurring Monthly Expenses are expenses and bills that occur every month that are the same every month. Expecting this month are expenses that you know you’ll have to pay this month (or more specifically, this pay cycle) that aren’t recurring, they don’t happen every month.
Planned Savings is how much you’re going to try to save right from the beginning of the pay cycle. You don’t have to have this but it’s always nice to at least have a base saving each pay cycle.
The spreadsheet takes the total of all items in these two lists and the planned savings item. This total is the amount you would have to pay in bills in expenses. This money is for all intents and purposes gone. The money is still in your bank account at the beginning of the pay cycle but since you will have to pay those bills before your next pay day, the money is pretty much off limits for you to spend.
The next time is the take-home pay. The line after that is the Monthly Allowance which is the pay minus the total in bills and expected expenses. This allowance is how much you would be allowed to spend for the whole month, until you are paid again.
The next line is Daily Allowance and obviously this denotes how much you are able to spend each day. This figure is calculated by dividing the monthly allowance figure by the number of days until the next pay day. Since I don’t know how to determine the number of days between two dates in Excel (which I’m sure is possible with enough Googling) I’ve included the Days until next pay to make the calculation easy.
The Daily Allowance is the magic number. Using this number you can assign yourself a recurring allowance that’s evenly distributed. I like having weekly allowances and I like my allowance to start on Thursdays. In the example I’ve manually set the weeks with start and end dates based on Thursday being the first day of the week. Notice how there are 5 week entries with the first and last weeks having less than 7 days in them? Week 1 is like that because May 14 was a Friday and therefore there would only be 6 days until Thursday. Week 5 ends on Jun 13, which is the day before pay day and therefore should be the end of the week in terms of this pay cycle.
Again, I’m too lazy to figure out how to make Excel determine how many days there are between two dates so I’ve manually entered the number of days in the Days column. The amount column is simply the Daily Allowance multiplied by the number of days in the allowance week.
What this spreadsheet allows you to do is determine how much you can spend each week during your pay cycle by entering your monthly bills and how much you are being paid. As good as this is it still requires a bunch of manual steps: determining the number of days in the month, entering the weeks in the month and the number of days for each ‘week’. Most of those manual steps can be automated in Excel and I might do that and upload a new example in the future.
Spreading out longer term expenses
What I really would like to do is determine how much I should save each pay day for things like car registration, which is a yearly expense. It’s a simple thing to do at first. You take how much the bill costs, let’s say $600 for argument’s sake, and divide it by 12, because I’m paid monthly. I would have to set aside $50 a month to pay for my car’s registration.
The benefit of doing this is obvious; you don’t have to worry about having enough money for those yearly bills because you’ve already saved up the money to pay for it.
Download the Example Budget Forecast Spreadsheet. You’ll notice 7 worksheets in this spreadsheet. The first is the Outlook worksheet which shows you a summary of your pay versus your known expenses throughout the year. The other worksheets are lists of expenses for different frequencies. For example the Weekly Expenses tab contains all expenses that you know you have to pay every week.
In each expense worksheet the expenses are added together and averaged to be a single Daily Cost figure. The Daily Cost in the Weekly Expenses worksheet is how much all the expenses for the week will cost you per day.
On the Outlook worksheet the Daily Cost figures for all the expense worksheets are added together to give you the Daily Expense Figure. This is how much all your expenses for all frequencies (monthly, yearly, every six months etc) cost you per day. Sobering isn’t it? Despite the depressing feel you’ll get from this I believe it’s imperative to find out how much you need just to pay the bills. Only then can you make an informed decision about whether you can take a loan or buy that TV.
The Daily Allowance shows you how much per day you have left after all your bills are paid. The theory is that if you only spend that amount per day, you’d have saved enough money for all bills, even the ones that come in only once or twice a year.
Yes, but …
Now this is all well and good if you’ve been following this plan from the day you started earning money and paying bills. The unfortunate situation is that you haven’t.
Let’s look at a simple example; the $600 car registration. Let’s say I paid my rego (‘rego is Australian slang for Car Registration Fee) in January and it’s now July. I’ve decided that I need to start saving for my next rego, which is January next year. If I had started saving in January I’d only have to save $50 every month (remember I’m paid monthly). Now it’s July and rego is 6 months away, not 12! I have to save $100 a month if I want to have the money ready to pay it without using my credit card.
But I used my credit card to pay my last rego and I haven’t finished paying that off! Now I have to save another $100 per month while paying off the credit card bill for the previous rego?
Yeah … I kinda do, unfortunately. The fact is that if I want to adopt this save-for-future-bills technique I need to go through a period of transition where I’m putting away a lot of money, paying off a bunch of debt and somehow still affording rent and food. It helps if you have some savings left over after every pay day. That surplus can go into saving for future bills. You’re going from paying for things after you’ve gotten them to paying for things before you’ve gotten then. There’s going to be overlap for a period time.
At some point, if you stick with it, your finances will normalise and you will always have enough money to pay off your bills.
What started off as a way to see how much I was spending month to month has become a complicated web of dollar signs and dates! This is why I’ve chosen this subject matter as the basis of a software based passion project. I want to make this stuff easy for me (and others I guess :P) to do.
My next post will be about how I’ve handled time frequencies in PayDay. It’ll be a bit technical but hopefully people with non-programming backgrounds will find it entertaining.