Tracking Your Finances with a Rolling 12-Month Average

Here's a guest post from Gerry at Sailing Kitty.

The most basic measure of your financial situation is comparing your income to your expenses. It’s a simple equation. You take your income over a set period of time and then subtract your expenses for that same period. If the result is positive, you’re making more money than you’re spending; if the number is negative, you have some work to do.

The complicating factor is the period of time you use to measure. A year gives you an accurate picture but it’s too long a timeframe. Waiting until year-end to find out how you’re doing robs you of any chance to make changes in the meantime. You don’t want to find out after the fact that you’ve been spending more than you’re making over the past several months.

Tracking each month gives you the flexibility to make changes, but it tends to be inaccurate. Some months you get a windfall and your income looks great; other months you may have car repairs and income tax payments and your expenses skyrocket. In neither case, are you getting an accurate reflection of your situation.

To take advantage of the year’s long-term view and the month’s short-term flexibility, I track my finances with a Rolling 12-month Average. The Rolling Average flattens out the month-to-month peaks and valleys in my income and expenses, yet gives me a monthly measure of how I’m doing.

Here’s how it works. You need to know your monthly income and expenses for at least the past 13 months and preferably for 24 months or more. If you use Quicken or one of the web-based applications like Wesabe, these numbers are easy to come by.

• First, list the months for which you have data in the first column, put the monthly income in the second column, and record the monthly expenses in the third column. You can follow along on the Excel spreadsheet example attached below this post.

• Next, add up your total income for the first 12 months and record that total in the fourth column across in the same row as the 12th month. In the example below, the total is $65,421. Calculate your expenses for the same 12 months and put that total ($66,131) in the fifth column along the same row

• Now, subtract your 12 months of expenses from your 12 months of income and put the result in the sixth column. That number, -$710 in the example, is the dollar difference between how much you made in those 12 months and how much you spent.

• Finally, divide that dollar difference in the sixth column by 12 and record the result in the seventh column. This number tells you how much more (or less) you earned than you spent on average for each month during the period. In the example below, expenses exceeded income by an average of $59.17 each month.

• Now, repeat the process for the next 12-month interval, deleting the first month from your calculations and adding the 13th month. In the example below, you get a monthly average of $6.58. The average increased because we deleted a month in which expenses exceeded income by almost $500 and added a month in which earnings were almost $300 more than expenses.

• Complete the calculations for each successive 12-month interval until you get to the current month. Now you have your Rolling 12-Month Average. The progression of the monthly average numbers gives you an accurate picture of the state and direction of your finances.

If the trend is positive and the dollar numbers are increasing, you’re heading in the right direction. On the other hand, if the dollar numbers are negative and/or the trend is heading down, you need to get your finances on track.

One word of caution: Because the Rolling Average tracks your financial performance in successive 12-month increments, it’s difficult to see a dramatic impact in a single month. Each month has only a 1/12 impact on the average. When you string together several positive months, however, your Rolling Average will start to climb and you’ll know you’re heading in the right direction.

Download fmf_rolling_average_graph.xls

Comments

It should be noted that, once you've input the first set of formulas (one row in columns 4-7) into Excel or a similar spreadsheet, you can grab the corner of the cells and drag them downward and the formulas will automatically fill in.

[Adam]

What about a WMA (weighted moving average), or EWMA (exponentially-weighted moving average)? See: http://en.wikipedia.org/wiki/Weighted_moving_average#Weighted_moving_average. This applies weights to more recent months, allowing trends in spending (both good and bad) to show up more quickly and with more impact.