Excel Spending Plan / Monthly Budget Spreadsheet
|File Type:||Excel Spreadsheet (.xls & .xlsx); OpenOffice (.ods)|
|File Size:||~191kb (.zip)|
I adore Quicken. I wouldn't know what to do without it. However, Quicken's single biggest weakness is its lack of a simple, easy-to-use, easy-to-modify budgeting feature.
I figured out long ago that Quicken was Best in Show at tracking all my bank accounts, showing me where my money went, keeping me abreast of my net worth and investments, and helping me stay on top of my bills. But when it came to planning my spending in the future — budgeting for the month ahead, for example — and letting me know where how much money was left for the month, it had little to offer. (Well, except unnecessary complication.)
So I took it upon myself to create an Excel spreadsheet to bridge that gap. That's where this Spending Plan spreadsheet got its start. (Screenshots are from the Excel 2007 version.)
If that looks interesting to you, click below to download the file. Then keep reading for more instructions on using the Spending Plan!
I've used this Spending Plan spreadsheet every single month, all the way back to early 2002. (Well, at least until I got my hands on Quicken Deluxe 2008, which had just enough of a cash-flow budgeting setup to handle my needs.)
During those years, this spreadsheet suited my household perfectly. Sure, I tweaked it here and there, but overall, this version adds up all your income and subtracts all your expenses pretty much the same way it did on Day One. Good stuff, if I do say so myself.
- Preface: The Basics
- Header Info: Date and Days Left
- Section 1: Income
- Section 2: Freedom Account Contributions
- Section 3: Recurring (Fixed) Outflows
- Section 4: Other (Variable) Outflows
- Section 5: Summary
- Section 6: Comparison Chart
- What's a 'zero-based budget,' and can this spreadsheet do it?
- How can I make a donation?
— George Kinder,
The Seven Stages of Money Maturity
As you probably figured out, I plan my family's spending on a monthly basis. (If you're brand new to the concept of budgeting and spending plans, and perhaps aren't sure what the point is, then you might wish to take a look at my Spending Plan overview page.)
The idea? At the beginning of each month, you'll sit down and open up a new Spending Plan, copying over (using safe copy 'n' paste!) most of your expense descriptions and planned amounts from the previous month. You'll then fill the spreadsheet out with your expected income for the upcoming month, as well as make any adjustments to planned expenses that you feel necessary.
One point that I'd like to make from the start:
Budgeting is not a waste of time, and for some folks, it may be the only viable way to dig yourself out of the mess you're in. If your money is constantly slipping through the cracks, your Spending Plan is the way to seal up those cracks. For good.
Spending plans work ... but only if you let them.
The Spending Plan spreadsheet consists of two worksheets, and one of those is for example purposes only. In this tutorial, the "Monthly Spending Plan (Example)" worksheet is the one we'll use. Obviously, when the time comes for you to enter your own data, you'll want to switch over to the "Monthly Spending Plan (Blank)" worksheet.
At the top, you'll see a place — a yellow cell — in which you'll always enter the date when you last updated your Spending Plan:
Whenever you make changes to your Spending Plan, plug the current date in the yellow cell.
Beside that, the spreadsheet tells you how many days are left in the current month, including today. (I always found that it was nice to know how many more days this month's money had to last!)
The first main section on the left side of the screen, shown below, is where you list all of the income you expect to bring in for the month.
When I'm typing in my estimated income figures, I prefer to use my take-home pay. Alternately, you could use gross (before tax) pay, and then list out all your taxes and other deductions in one of the expense sections.
I've included space enough for ten income items. You just never know when extra money will show up at your door...
- Found money
- Lotto winnings
- Delusional Aunt Doris thought it was your birthday and sent you a $100 check
- Long-since-forgotten product rebate came in
In the screenshot above, the family has listed all its expected paychecks for the month. They've also received one insurance reimbursement check, and listed it as well.
As with all entries in the Spending Plan, as the month passes, make sure to change your income figures to reflect reality. If, for instance, earlier in the month, you estimated a paycheck would come in at $500, and then it arrived with an actual total of $492, you should change the $500 to $492 as soon as it's official.
After all, the whole point of a Spending Plan is to keep you neck-and-neck with — or better yet, one step ahead of — reality.
That means being absolutely honest about your income as well as your expenses!
I wholeheartedly believe in saving, and I wholeheartedly believe in Mary Hunt's Freedom Account concept. I believe in it so much that I have a whole section of my Spending Plan spreadsheet devoted to Freedom Account deposits.
In short, a Freedom Account is a savings account (physical, or on paper only) into which you make monthly deposits in order to assure that you can cover expected non-monthly expenses ... whenever they arrive.
What sorts of bills or expenses would qualify? In my case, our auto insurance is due every six months. Our life insurance is due annually. Christmas itself is a "bill" of sorts — it happens every year, no matter what, like any other bill, and it costs money. (I lump this in with my so-called "Gift Fund," which I'll use to cover holidays, birthdays, weddings, and any other Hallmark-encouraged events that come along.)
So, in this example, I'd make Freedom Account contributions each month as follows:
- Life Insurance #1 ($396 per year) = $33 per month
- Life Insurance #2 ($180 per year) = $15 per month
- Auto Insurance ($796 every six months) = $133 per month
- Home Alarm Monitoring ($87 every three months) = $29 per month
- Christmas / Gift Fund ($492 per year) = $41 per month
Though these items aren't billed to me on a monthly basis, I've effectively made saving up for them a monthly bill. That is what a Freedom Account does.
Coincidentally, I find this also to be an effective place to list any amounts that you wish to dedicate / transfer to your savings or Emergency Fund. When I do this, I give it a description of "Directed Savings."
So, when you're filling this out for yourself, in the "PLANNED AMOUNT" column, enter your planned amount or estimate for the given Freedom Account item.
In the "SPENT AMOUNT" column, enter the amount you actually end up contributing. Usually, in the case of Freedom Accounts, your PLANNED and SPENT amounts won't differ.
Recurring ("Fixed") Expenses
Now we've got our month's income estimated and logged, and our Freedom Account deposits entered, in the sections above. It's time to talk expenses.
Pictured above is the next section of my Spending Plan spreadsheet. This is where I list my recurring, fixed expenses.
In my life, examples would include my mortgage payment, the heating-gas and electrical bills, our daughter's health insurance payment, and so on. These expenses are going to be with us, month-in and month-out. Their amounts don't fluctuate all that much. I can pretty easily carry these expenses, and their planned amounts, from one month to the next with very little modification.
In this section, enter descriptions of each of your fixed expenses in the first column.
In the second column, enter the "PLANNED AMOUNT" for each item — that is, the amount you estimate the expense will cost. (We're planning ahead here, remember?) Of course, if you already know the amount ... then you should enter the amount you know. (Duh.)
In the third column ("PAID AMOUNT"), when you actually pay each item, enter the amount the expense actually turned out to be. But don't do this until you've actually paid the expense!
Why? Because in this way, the Spending Plan offers a nice fringe benefit: It can also assist you in determining which of your regular monthly bills you have and have not yet paid for the month. If you've been filling out your Spending Plan diligently, then it stands to reason that any blank items in this column have yet to be paid for the month. In the screenshot above, our fictitious household has apparently not yet paid their bills from MCC Corporation and the City of Lubbock.
Probably that's just because they haven't yet received those bills.
Variable ("Nonfixed") Expenses
The final expense section of the Spending Plan spreadsheet contains your "Variable Expenses." In this case, "Variable Expenses" describes those expenses that are more irregular in price and/or occurrence.
Examples might include groceries, household consummables, doctor visits and other medical expenses, automobile and transportation expenses — or pretty much any expense that isn't listed or accounted for in the fixed-expenses section above. (You can click the image below to get a larger view.)
In the first column, enter descriptions of each of your expense categories. You have 35 rows to use, which hopefully will be plenty of room for you to list any items — expected or otherwise — as they come up throughout the month.
In the second column ("PLANNED AMOUNT"), enter the amount you estimate each item will cost you (in total) throughout the course of the month.
In the third through seventh columns ("SPENT AMOUNTS"), enter your actual expense amounts for each item/category as they occur throughout the month. Why so many columns? Well, I don't know anyone who buys "Groceries" or "Household Items" just once per month. And if I can find places for my spreadsheet to "do the math" for me, you can bet I'm going to let Excel do the number-crunching.
The little gray and blue box at the top right of the page is where all the math gets tallied, your income and expenses get together (all intimate-like), and the numbers tell the story ... good or bad.
What does all this tell us?
- Planned Spending This Month: Pretty self-explanatory, I would think. This is the total of all your "PLANNED" items.
- Spent So Far This Month: Again, pretty self-explanatory. Totals up all your "PAID" amounts.
- [Income] - [Planned Amounts]: This calculation takes your Total Income and subtracts your Total Planned Expenses. This is where you'll stand at month's end if your planned spending amounts and your income match your estimates. The earlier in the month you are, the more important this number is.
- [Income] - [Spent Amounts]: This might be better termed "Where you stand right now." If you suspected that it takes your Total Income and subtracts from it your Total Paid Expenses, you were dead-on. The closer you are to the end of the month, the more important this figure is.
If you're one of those folks who doesn't take any data seriously until it's poured out and smeared all over a chart, well, this is for you. Here's where you get a graphical representation of just how wonderfully (or disastrously) your month is shaping up.
And just in case the logic is a little fuzzy for you, life goes a lot better when you make sure the green rectangle ends up taller than the red rectangle at month's end.
Just a helpful tip there.
What's a 'zero-based budget,' and can this spreadsheet do it?
A zero-based budget is one in which your Total Income minus your Total Expenses equals zero — meaning that when you sit down at the start of the month to set up your budget, you give every expected dollar of income a job to do. You allocate every dollar of income toward something in your Spending Plan, whether it's food, clothing, shelter, debt, savings, or whatever. As far as the Spending Plan spreadsheet goes, your (Income) minus (Planned Expenses) should always equal zero, as shown in the Summary box here:
In a zero-based budget, since (Total Income) minus (Total Expenses) must always equal zero, money for one bill or expense category cannot be increased without money from another bill or expense category being decreased, and vice versa. (Well, it can, but then you could be in the red. And that's a no-no.)
The idea behind zero-based budgeting is this: You give every single dollar of income a job to do, and you do this before you actually get the money.
Thus, when your family is in the middle of a drastic debt-paydown or a hardcore savings project, you eliminate the nebulous "wiggle room" and guesswork regarding how much income you have "left over" (read: ready to get blown on frivolous stuff) at any given time.
How can I make a donation?
Like its predecessors, Version 4.0 of my Spending Plan spreadsheet is absolutely free to curious 'netizens. I hope that readers find it even more useful than the earlier versions.
Anyway, if you use the Spending Plan and think it worthy of a donation to the upkeep of IYM, then simply click the button below to make a donation (any amount) via Paypal.
NOTE: I would ask, though, that if you're someone who's in debt, and working like crazy to get out, that you please refrain from making a donation to IYM. Instead, use your intended donation amount as an extra contribution toward your highest-interest debt ... and then drop me an email to tell about how my site or my spreadsheets have helped in some way. That, I assure you, is more than enough payment for me.
I am, as always, grateful and appreciative of any and all contributions.