Photo by action datsun

“Where have all the dollars gone?
Long time passing
Where have all the dollars gone?
Long time ago
Where have all the dollars gone?
Gone to Starbucks, every one
When will I ever learn?
When will I ever learn?”

- Pete Seeger, “Where Have All The Flowers Gone?” (paraphrased)

To balance your household budget, you have to be able to accurately track your expenses. So you might make a spreadsheet listing your taxes, 401(k) and IRA contributions, mortgage, insurance, food, utilities, etc., and add them up to find your total expenses. When you see that your expenses are less than your income, you breathe a sigh of relief. You’re in the black! Or are you?

There’s an invisible force wreaking havoc on many household budgets, turning black ink to red ink and vanishing with hardly a trace. They call it The Latte Factor. David Bach, author of The Automatic Millionaire, coined this term to refer to our many small expenses that add up to large amounts over time. A coffee here, a magazine there, and before you know it, you’ve spent a staggering amount of money without realizing it.

When I finished school and landed my first job, I whipped up a spreadsheet to track my expenses. I entered every expense I could think of, and they all added up to less than my salary, so I thought I was running a profit. And yet, that didn’t seem to be the case when I saw my bank account balance dropping every month. On closer inspection, I found that I had missed $600 of monthly expenses in my spreadsheet.

I decided that I needed a better system to see where the dollars were really going. I needed two spreadsheets, one to track my budget (a high level view of where my money was going), and one to track my expenses (the amount I was actually spending). Open up the sample worksheets Budget.xls and Expenses.xls, or follow along with the HTML versions at the bottom of this post.

These are hypothetical numbers for a single person. I have no idea if they’re reasonable, but they’ll get the point across. Of course, you’ll need to replace them with your own numbers.

First let’s look at Budget.xls. At the top we have our monthly salary of $5,000. Then the monthly expenses are split into two parts. The first part consists of our fixed expenses. These are things that are fixed in the short term, like taxes, mortgage, and insurance. (I also put 401(k) and Roth IRA contributions here. While technically not fixed, everyone should strive to max out on these). Everything is expressed as a monthly value regardless of how frequently the bill is actually paid.

The second part of the expenses consists of our variable expenses. These are the discretionary expenses that vary according to our spending habits each month: electricity, food, gas, etc. I like to separate these from the fixed expenses because we have control over them in the present moment; every day we make choices that determine our variable expenses. To estimate them, just take your best guess based on your most recent bills.

Now you see the problem. The expenses we’ve entered here are not everything we’re spending. We’ve forgotten all the lattes, scones, movie tickets, etc., and maybe a few big expenses such as car maintenance.

That’s why we have a line item called “Miscellaneous.” This is a magical fudge factor, defined as all variable expenses not already listed, which is $346.16 in this case. Let’s come back to this later. For now, just assume that $346.16 is correct.

Now we can calculate our total expenses, and by subtracting expenses from income we see that we have $85.51 left at the end of the month. Had we not accounted for The Latte Factor we would have come up with $431.67. Our $85.51 may be less encouraging, but it’s much more accurate.

In addition to seeing how much money is left at the end of the month, we might like to see what percent of our income we’re saving. Our investments here consist of a 401(k) and a Roth IRA, and I assumed a 401(k) match of 5% of our salary (if you want to get technical, the 5% 401(k) match should be counted in the Income section at the top, but we won’t do that here). This works out to us investing 39.2% of our income. (At this point I’m seeing that these numbers aren’t realistic, but like I said, they’re just hypothetical numbers to make a point.) Considering our investments plus the $85.51 we have left at the end of the month, we’re saving 40.9% of our income.

Now, back to that magical fudge factor, the $364.16 in miscellaneous expenses. Where did we get that number from? That’s what the expenses spreadsheet is for.

Open up Expenses.xls. This is a list of our monthly variable expenses over the last 12 months. We want to look at the average over the last 12 months to smooth out any anomalies such as holiday spending. To find your expenses for a given month, just look at your checkbook. Add up every check you wrote and every electronic debit you made during that month, but don’t count fixed expenses like your mortgage payment.

Expenses.xls tells us that our average monthly variable expenses are $836.16. Now look at Budget.xls. If we did our budget perfectly, the variable expenses we listed would add up to exactly $836.16. But of course, there’s no way we accounted for everything. Surely we missed some things, and that’s why we need to set the miscellaneous expenses to [$836.16 - the variable expenses we've accounted for = $364.16]. It’s set up as a formula in Excel, so it will automatically update to reflect any changes we make to our expenses, and the total expenses will come out exactly right. That’s why I called it magical.

Now Budget.xls accurately reflects how much money we’re spending, only we don’t know exactly where the $364.16 in miscellaneous expenses is going. But if we think about it and realize that we spend $150 a month on lattes, we can add a new line item for lattes, and then the miscellaneous expenses amount will automatically drop to $214.16. The closer we get the miscellaneous expenses to $0, the better we can see exactly what we’re spending money on, but the “remaining income” value is accurate no matter what.

After I got my budget under control, I eventually stopped tracking my expenses so carefully, and I haven’t used these spreadsheets in years. However, back when I was bleeding red ink and not knowing why, I found this system to be very helpful in answering the question: Where have all the dollars gone?

Budget.xls

Important: the “miscellaneous” line item is determined by setting up an Excel formula to calculate [average monthly variable expenses - those variable expenses already listed]. The value for the average monthly variable expenses comes from the expenses spreadsheet.

Income
Salary	$5,000.00
Expenses
Taxes	($1,400.00)
401(k)	($1,291.67)
Roth IRA	($416.67)
Mortgage	($900.00)
Car Insurance	($50.00)
Homeowner’s Insurance	($20.00)
Natural Gas	($42.00)
Cable	($100.00)
Electricity	($25.00)
Cell Phone	($25.00)
Food	($200.00)
Gasoline	($80.00)
Miscellaneous	($364.16)
Total Expenses	($4,914.49)
Remaining Income	$85.51
Investments
401(k)	$1,541.67
Roth IRA	$416.67
Total Invested	$1,958.33
Percent Invested	39.2%
Percent Saved	40.9%

Expenses.xls

(Excluding fixed expenses)

February 2007	$772.77
March 2007	$803.45
April 2007	$759.53
May 2007	$1,007.12
June 2007	$830.99
July 2007	$705.42
August 2007	$756.74
September 2007	$525.39
October 2007	$1,142.95
November 2007	$922.97
December 2007	$604.42
January 2008	$1,202.13
12 Month Average	$836.16

(This post appeared in Carnival of Personal Finance #142 - The Homeless Edition, hosted by The Bag Lady.)

Related Posts

• The Millionaire Mindset
• 4 Steps To Financial Freedom
• Is It A Good Time To Buy Real Estate? Does It Matter?
• What Is An Ebook Worth?
• When Good People Give Bad Financial Advice
• The Science Of Getting Rich
• How To Create A Seven Figure Residual Income
• The 6 Levels Of Wealth