Wednesday, June 17, 2015

My Magical Budget Spreadsheet

Budgets are the basic building block of personal finance. You can't realistically achieve any financial goals without one. They are also boring. It's just the nature of the beast. But it pays to remember that boring often creates wealth. Socking away a boring 10% of your income paycheck after paycheck, year after year, will make you a millionaire. In long term investing, boring is often good.

The best thing about budgets is that they take all the emotion of out decisions. Once you set up a budget (and make the commitment to stick to it), you'll never again find yourself going into debt from impulse purchases. The best budgets also aren't too constricting. You can't realistically budget every cent of your income and expect to stick to your plan. You have to allow some flexibility.

I developed a spreadsheet for my budget that I have refined over the years and I thought I'd share it will you (with fake data, of course). Here's a link to the Google Sheets document. You can copy it and modify it to suit your needs. The basics are all there - you record your monthly income and monthly expenses and you can see how much you have left over each month (or how much you are going in the hole). What I like about my spreadsheet, however, is the goals section:

Click to embiggen

But I'm getting ahead of myself. Let's start with the basics. In the upper left, enter your monthly income. My wife and I both work, so there are two places to put income. This is your after-tax income, or take-home, pay. If you get paid twice a month, enter 2 times your net paycheck here. If you get paid every other week, also enter 2 times your net paycheck here. (Getting paid every other week means there are 2 months each year when you will get 3 paychecks a month instead of two, so if you budget based on two per month, you'll have a couple months where you get an entire extra paycheck to keep!)

Enter all your expenses in the column titled, appropriately enough, "Expenses." These are the expenses my family has. You may have more or less. Also notice, as a note off to the right indicates, there is no entry for expenses for clothes or gifts. You may want to add specific entries for those. Next to the Emergency Fund Savings, you can see "10%". Enter a savings percentage here and the expense amount will be calculated based on the two income figures you entered above. For the other figures, just enter the monthly amount. For things that vary, such as your electric bill, I simply added up my total bills for the past 12 months and averaged them to get a monthly figure. If you don't have your previous bills, you may be able to get them from your utility company online.Obviously, the more accurately you enter your expenses, the more accurate your budget will be (and the more likely you will be able to follow it).

Now the fun stuff! Enter your savings goals on the right side. If you have no time frame, enter them towards the end of the list, where I have entries for "IRA 1" and "IRA 2." Enter the monthly amount where there is a red number. If you have goals that have a timeframe, such as next year's vacation, you'll enter these a bit differently.

The magic section!

Enter the total amount you want to save in the "Amount Needed" column. Enter the dates you want to start and end saving for this goal in the appropriate columns. This will cause the corresponding Monthly and Weekly expense cells for the goal to be calculated so that you will reach the goal amount by the End date. The "Budget effective date" cell is used to calculate the weekly and monthly savings amounts for that particular date.

For example, in the above image, the effective date is 5/29/15. I am not planning on starting saving for "Big Project 2017" until Jun 1, so that goal amount is zero. However, I need to save $6,000 for "Big Vacation 2016" starting on 3/30/15 and ending on 9/1/16. That works out to $322 a month, or $81 a week. (My wife and I get paid on alternate Fridays, which means we get a paycheck every week. This make is easier for me to work with weekly figures instead of monthly.)

Another piece of vital information is the amount in the little green box labeled "Monthly Funds Leftover". This is how much of your income is NOT budgeted. This is your extra money.

The Budget effective date makes planning a snap.To see if your savings goals are realistic, simply enter a different date. For example, look what happens when I enter a date of 8/1/15:
Danger Will Robinson!

My Monthly Funds Leftover went negative. That means I'm going in the hole $112 this month. You can see that now my savings for Big Project 2017 and Goal 2, kicked in. In order to get my budget to balance, I'll need to cut back elsewhere, change my goal amounts, or increase the time frames for achieving my goals, which will lower the monthly payments. Play around with your figures and dates until you reach a leftover funds figure you think you can live with - something not too high, but enough that you won't blow the budget if you splurge on something extra during the month or go over budget in a category. Personally, I aim for $200 - $300 as my monthly leftover figure.

A big help in achieving goals is to have a separate savings account for each goal. Each time you get paid, simply open the spreadsheet, enter the day's date in the Budget effective date cell, and divide your money between accounts according to the spreadsheet. No further thought is required. If you can do this consistently, your savings will be on auto-pilot and you'll meet all your savings goals!

I hope you  find this useful. Let me know if you have any suggestions.

(On a technical note, date arithmetic is a pain in the ass, especially in Excel, so I fudged a bit. In calculating the weekly savings figures for goals, I use the number of 7 day periods between the start and end dates,which may not be equal to the number of weeks between the dates, depending on how you count weeks. Likewise, the monthly figure is simply 4 times the weekly figure. As some months have 5 weeks, this is also slightly inaccurate. However, these inaccuracies will result in more money being saved, so I felt it was a valid trade off.)

Update: A couple days after I wrote this (but before it was published), Slate came out with a column against personal budgets.  The reasoning is that most people's incomes are not regular enough to stick to a budget. They suggest instead that people simply monitor their spending more frequently. The author of the article was a freelance writer, so I can certainly understand her irregular income and how hard that makes it to stick to a budget. But this is one reason why my budget spreadsheet includes a cell showing how much extra you have each month. This is your safety net, if you will. It's extra money not budgeted for anything particular that can be used in an emergency. My budget also includes paying into an emergency fund to help with sudden expenses or sudden drops in income.

As for more closely tracking expenses, I agree. That goes a long way to helping you get your spending under control. Perhaps I just assumed people would do this, but as soon as I made a budget, I starting keeping closer track of my expenses and checking them more frequently. I did this just to make sure I stayed within my budget. Tools like help immensely with this. (I've written about Mint before here.)


  1. In lieu of budget, I've been monitoring my spending every month since July 1992 via spreadsheet.

  2. And that is probably why you are retired already :-)

  3. Your approach seems very disciplined. That's probably key to the success of your Financial Model Spreadsheet