Eric Murano

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 ) 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.

Note: I originally called the project ‘PayCycle’ but since this is the name of an existing commercial product from Australia.

In my last post I wrote about how I’m managing my finances by using some simple maths in Microsoft Excel.  As a way to learn C# and .Net I’ve started writing the logic of the spreadsheets into a simple desktop application.  I’ll also be extending the features of the application to help with forecasting how much non-fixed cost bills will cost in the future.  Things like car repairs and how much you spend on lunch at work.

I’m calling the software PayCycle since the central idea is your pay cycle and how your budgeting revolves around how much and how often you’re paid. Since finding out there is already a product out there called PayCycle I’ve decided to name the software ‘PayDay’.

I could keep using my Excel files to organise my budget if I didn’t have the urge to have a software project to tinker with in my spare time.  The project deals with money (which I’ve found is more involved than just using numeric data types), with time periods and frequencies (yearly, monthly etc) in what appears to be straight forward from the 10,000 foot view but ongoing work will reveal the devils in the detail.

The project will initially deliver a desktop application that uses WPF for the user interface, WiX for the installation project and NUnit for unit testing. I’ll be using both SharpDevelop and Visual C# Express as IDEs.  SharpDevelop because it has integrated NUnit support along with WiX integration.  Visual C# Express because is has WYSIWYG for WPF.  I would have used Visual Studio as it does everything I need but I can’t afford the license fee right now so.

SharpDevelop will contain the library project which is where the business logic of the software will be.  This is the project with the unit tests.  The windows application project will be in Visual C# Express and it will reference the built DLL file that the library project produces.  Hopefully there will be no surprises with debugging!

Once the desktop application is up and running I’ll look into writing a web based version.  With this intention in mind I’ll be very careful with how I write the core of the software.  I can not have any business logic in the WPF project. One of the lucky benefits of having two different IDEs is that there’s a clear delineation between the ‘display’ and the ‘logic/model’.

I’ll post new articles about my progress.  I’ll write about design decisions and problems I encounter.  I’ve already started programming so there are already things about money, currency and time frequencies that I’d like to share.

My next post will a little boring; it’s a walk through of the spread sheets I use for personal budgeting.  It will give you an understanding about what I’m trying to achieve with the software.

Work pays me monthly and as a result being careful with how much I spend is challenging. It’s actually not that bad. Most of my bills are monthly so I get a sense of how much I can spend each month by subtracting those monthly bills from my monthly wage. I enter that figure into an iPhone app called Balance then throughout the month I enter everything I spend into it. The app shows me how much of that allowance I have left each time I enter an expense.

Recording your expenses is a good thing to do. In fact recording anything you need to do in moderation is a good thing. I’ve lost weight in the past merely because I was recording everything I ate and was trying to keep a daily calorie budget of 2000 calories. When I record expenses the remaining allowance serves as a mental check to make sure I don’t end up spending more money than I was paid. As that number gets smaller the less likely I am to spend money when I don’t have to.

As empowering as that is I was still ending up spending more than my monthly allowance. Towards the beginning of the month my allowance would be relatively large so I wouldn’t feel any remorse about spending money. As the month progressed I’d have less and less of the allowance left. When the remaining allowance was low I’d start to tighten my belt but there’s a point where the remaining allowance is so low that I know I will need to spend more money than I have left until next pay day. This acknowledgement effectively opened the flood gates and made me spend money as haphazardly as I had done at the beginning of the month! On my credit card, of course.

How weird! I guess it’s like trying to diet then succumbing to a doughnut for morning tea then saying to yourself “I’ve already eaten a doughnut today, I may as well have a cheat day”.

When there’s a positive number as my remaining allowance there’s still a chance that I could make it to the end of the month with some money left over for savings. The challenge to save money at the end of the month is still there. If I’m in the red then I’ve already blown it so there doesn’t feel like there’s any challenge left.

It’s like when you see a football game and one side is so far ahead of the other team that there’s a point in the game where everyone knows the other team just can not crawl back and win the game. The losing team knows it and it effects their drive to perform. The difference between the football analogy and my allowance is that when I’m in the red it’s is even more important to spend less!

I didn’t want to fight this seemingly irrational mental situation. I know what I’m like; at times I have to guide myself into doing the right thing. The thing that makes me stop spending so much money is the urge to have a little bit left over at the end of the month. Tracking my allowance by the month was only giving me 12 of these urges every year. That’s not a lot, especially when the urge has a window that closes once the allowance hits the red.

If I could make it so that the ‘urges’ were more frequent then I’d save more money.

If you’ve never been paid monthly then what’s the one thing that scares you the most about the prospect of being paid monthly? For me it was always the ‘Live like a rock star for a week and a pauper for 3′ phenomenon (thanks to my friend Mat for telling me that one). You know that if you’re paid monthly that it’s going to be easy for you to spend most of your cash in the first week then have to be broke until the next pay day.

Obviously being paid weekly is the ideal situation for most people. If you spend all of your pay it’s not that big of a deal. You will have, at most, 7 days until you’re paid again. If you all your monthly pay on day one you’ll have to wait a whole month before money comes in again!

Being paid weekly isn’t a perfect situation either. If you have monthly bills then there will be some weeks where you’ll have to pay more bills and as a result have less budgeted allowance. No matter how often you’re paid there are always bills that are more or less frequent than your pay cycle.

What I do at the moment is add up all the bills and expenses that I know I’ll have to pay between two pay days then subtract that total from my actual pay.  I then divide that number by the number of days until the next pay day.  The result is the total allowance I can spend that pay cycle.  I take that allowance and I allocate it into weekly allowances.

That figure is the amount, on average, that I can spend per day.  I then allocate that allowance into weeks and enter the weekly allowances into the Balance app each week.

It’s like I’m being paid weekly now.  Even better than that, my bills are accounted for each month so I don’t have to worry about paying bills with my weekly allowances.

I’ve started going even further with this idea.  I now have a separate allowance for fuel.  I set aside a fixed amount for fuel each month and whenever I fill up I record that against my fuel allowance.  Right now I end up not using all of my fuel allowance so obviously I’m allocating too much.  What I’ll end up doing is averaging how much I’ve paid for fuel over the course of a few months and come up with a more lean fuel allowance.  For extra points I might even apply an inflation rate so that my fuel allowance increases as the cost of fuel does.

The fuel allowance is working so well that I’m considering carving off more of my weekly allowance and creating other specific allowances.  I’d love to do this for groceries but the amount I spend on groceries changes every week.

The point is that the more constant a recurring expenditure is the easiest it is to make it into its own allowance.  And the more specific allowances I have, the more disposable my weekly allowance is.  Of course the more allowances I add, the smaller my weekly allowance is, but that’s ok.  I would have to spend the money anyway.  What’s left is money that I can spend on what ever I like.

The reason why I’m talking about this is because I’m going to write an application that manages my finances based on the process in the post.  Right now I use an excel spreadsheet to calculate my daily allowance and ultimately the weekly allowance but I want to do more.  I want to be able to specify all the bills I would have throughout the year and with that information figure out how much I should be putting away every pay cycle so that I have money saved up to pay for bills that do not come that often but are still predictable.

My next post will introduce this new project.  That’s enough from me for now