Skip to main content
No. of Recommendations: 0
I don't have the updated version at work right now, but I can explain what I wrote. This will probably be long-winded.

First of all, I used openoffice (free, downloadable at So no special software other than a free spreadsheet program.

I make a row for each month for the next 50 years (a bit of an overkill there). I ended up doing by hand if I remember right, since I spent far too much time trying to figure out how to do it automatically.

Anyway, I made a column called "Total Value". Then I made columns for every single thing I could think of. I figure as time goes on, I'll add columns as I think of them, but it seemed to come up with a somewhat accurate picture.

I'll explain what I put in each column then.

"Total Value" is just the addition of all the columns to the right of it. So it adds up "Mortgage" (Negative number), "Discover Card" (Negative number), "National City Stock" (Positive number), etc. Then it theoretically gives me the total of my current value for each month.

For the current month, I just type in the exact values for each column as I know them. So I go online, check the values of my stocks, bank accounts, 401k's, etc. Then I checked my mail at home to get my mortgage balance. It doesn't need to be exact to the month I figure, because after 20 months any blips should be just "data noise" (IE, 200 bucks more or less shouldn't make a difference).

For the future months, I just think of how the number is likely to change. I'll give examples:

For my discover card, I subtract $200 each month, and once it hit 0, I just changed the formula to 0's (Since I'd like to believe I'll not add back onto it). FYI, this wasn't very accurate since I paid off around $1000 the first month, but I like being conservative.

For my drips, I do something like this: =(V15*1.005)+100

V15 is the previous month's value for the drip. 1.005 is my return for the month (I calculated that as the monthly return for a 6% annual return rate). Then 100 is the amount I added that month. If I increase my input rate, I'll have to change the formulas. Once again, I check the stock prices and replace the formula with the actual value on the 1st of the month. Once you change to the actual values, all future calculations automatically correct to the new price point.

I use the 1.005 return value for all stock positions, and then I use additions as above to add values.

For mine and my wife's income, I split it out the following way:

Additions to drips and 401k's I just made rough calculations. I increased the 401k contributions by 3% each year to account for salary increases. Once it hit 14k per year I cut off the increases in the event the 401k limits aren't increased. Being conservative again.

I added the known limits into the Roth for the next few years, and then stopped deposits and just let the money grow. Then if we do pass the limit due to some wonderful raises, it won't hurt our Roth balance, and if we can continue to deposit, I'll correct it.

As for tracking expenses/income, it's certainly not perfect. I have a set of columns far to the right of my "balance" columns. One column for "my income - deductions", and then another for my wife's income minus deductions, and then columns for each expense I could think of. The total value left over is called "Extra Cash". I don't add this into our "total value" since it's possible that the "Extra Cash" will end up going into a vacation, new DVD's, etc. So I just use this to make sure that the extra cash column for each month doesn't go negative. I add in extra purchases, so I can see how much our "extra purchases" changes the balance as well. A few months in a row I noticed almost no extra purchases and I found our c-card bills dropping quickly. FYI, I put in another column next to Extra purchases just to put text in to explain what the purchases were (IE, we just had a $600 trip to Denver for the weekend, so that's in there. I figure we'll also be able to see the explanation of why our food bills went up this month, since eating out in Denver was expensive).

I have the netflix, power, water, car loan payments, phone, internet, drips, roth, etc monthly bills coming out, plus food allowances, etc. It just helps to show me keep track of how much an additional monthly expense subtracts from our extra cash flow. I can also then see how easily we could afford to add more to our investments. FYI, I do have a column with our "balances" that keeps track of our checking account, so if we don't spend the extra it does go into our balances.

Here are the actual columns I have at the beginning of the spreadsheet:

Date (1 month per row)

My age, Wife's age (ages were added just so I didn't have to figure it out in my head every time I looked at the chart)

Goal (so I could change our goal if I realized that I wanted more/less money, it's set at 2.5 million right now)

SWR (it's our "Total Value" column multiplied by .04 to get 4% of our total value)

Total Value (Which is the value of our investments, I do not include mortgage or car equity anywhere, since I view them as things we cannot make money off of)

Left to save (which is just the goal minus the total value)

So the most interesting things I've found is to increase how fast I pay off a loan, and notice our Total Value skyrocket 20 years down the road.

I also find it's very interesting to see that while our "Left To Save" may not equal 0 at the time I wanted (Which is understandable since I'm conservative on everything I could think of), if we were willing to accept a lower SWR, I can see a much earlier time when we could retire.

I also can see where to address problems in the future. I'd like to have our mortgage paid off before we retire, so we'll need to think about that. I also never increased our Drip allocations (only putting $100 into a couple drips), so increasing those makes a huge difference in the final numbers.

I think the best thing about spreadsheets is if you use enough simple formulas, you can change a single number and have it make a big difference everywhere else. I also found that I hate having car loans even more than before, since it makes a large difference on my "Total Value". It also subtracts a lot from my "Extra Cash" on my expenses columns.
Print the post  


The Retirement Investing Board
This is the board for all discussions related to Investing for and during retirement. To keep the board relevant and Foolish to everyone, please avoid making any posts pertaining to political partisanship. Fool on and Retire on!
What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.