Budget Tracking and Projections (with Quicken Tricks)

This post has been cited in the Carnival of Personal Finance published on 3 August 2009.

This is the third in a series of posts on investment planning. For those who haven’t read the first post (or have forgotten), I’m soliciting feedback (tips, tricks, links, etc.) that I will cite and use in the final post of the series. Here’s a list of the other posts in the series:

1. Investment Planning: The Series
2. Household Budgeting the Easy Way
3. Budget Tracking and Projections (with Quicken Tricks) [this post]
4. Willingness, Ability, and Need
5. Estimating a Retirement Budget
6. Need for Risk: The Details
7. Multi-Period Planning and Asset Allocation
8. Investment Planning: Reader Tips, Tricks, and Links

I assume all readers of this post have constructed a descriptive household budget with positive cash flow (if not, see the prior post). The bottom line of such a budget is a monthly (or other period) average net income. That is, it is income less all necessary, regular expenses. For simplicity, I will call this amount your surplus and I will assume it is a monthly value (this is not important; if you prefer a yearly value, that’s fine).

Your surplus represents how much you can spend on extra stuff or invest per month. Therefore, if you have a goal, like saving $20,000 for a new car, you can use your surplus value to see how many months it will take you to do so. If you have multiple goals you can set up a spreadsheet to allocate fractions of your surplus to each of them and then compute how long it will take to reach those goals (or vice versa: set the time by which you need to reach the goal and compute what fraction of surplus is required). These examples illustrate that surplus is the key to investment planning. I’ll have a lot more to say about that in subsequent posts. First, let’s apply our budget to the fundamental problems of budget tracking and projection.

I use Quicken to track and project my budget, but you can do all of the following with other software. (Until five or so years ago I used Excel for these functions exclusively. Now I use Quicken 2007.) Since I hate to read software guides I’ve made this entire approach up myself. It would not surprise me if there were other ways to do the following in Quicken (feel free to suggest them or provide links to such things).

I use Quicken as an electronic checkbook. Since I write checks or have electronic payments drawn on and deposited into two accounts, a checking account with Bank of America and a taxable money market account at Vanguard, those are the only two real-world accounts I bother to track (i.e. download/reconcile) in Quicken. Thus, the sum of values of those two accounts is all that is relevant for tracking actual net household cash flow. Let’s call this sum my actual balance. All the other investments I have are not relevant and we can ignore them for this post.

In addition to these two accounts in Quicken, I have set up a third, which I will call my projected balance. This projected balance account is fictitious in that it does not correspond to any real-world account. Using Quicken’s functionality to schedule automatic bills and deposits I have translated my budget (see spreadsheet example) into automatically recurring Quicken transactions into and out of my projected balance account. I have set the timing to correspond to when such payments are actually made in the real world.

At any point in time I can compare my actual balance with my projected balance. The former reflects my real-world income and payments. The latter reflects my budget. The two should match, or at least be close. If they are not, it indicates a problem (e.g. bank error) or, more likely, that I forgot about a big purchase or a large financial gift. When the two are off by more than about $1,000 I search my brain and records for the source of the discrepancy. This doesn’t happen often and when it does it doesn’t take much time to find the source. Then I adjust my projected balance accordingly to bring it back in line with reality.

This real-time budget tracking is very handy. I can always tell if my household is overspending or if we’re saving at a greater rate. This really puts my budget to work.

Another way to use Quicken to put your budget to work is to use it for projections. The “Overview” tab of my projected balance account has an “Account Balance” graph with an “Options” menu. One of the options is to “Forecast my future account balances.” By entering my budget into this tool I can immediately see my projected surplus growth path. It also automatically calculates the monthly surplus (Quicken calls it “net savings”). In fact, I don’t use a spreadsheet for budget projections anymore. I only made this one for illustrative purposes for this investment planning series of posts. It is based on the information I’ve already entered into Quicken. (One gripe I have with Quicken is that it doesn’t seem to be able to base its forecast on the transactions I’ve already entered in my projected balance account. I have to reenter them in the forecasting tool. If anyone knows how to connect the two explicitly please let me know.)

As I said, one can do all of the above with other tools. Being able to reconcile one’s real-world cash flow with one’s budget and to forecast surplus are worthwhile capabilities. In subsequent posts in this series I will show how to use these capabilities in the service of investment planning.