Make Your Own Retirement Spreadsheet

In my last post, I encouraged those so inclined to create their own financial planning spreadsheets.  However, I didn’t provide enough detail to allow the motivated reader to go and set one up.  This post fills that gap, providing enough specifics to enable anyone who knows how to use a spreadsheet to map out her financial future on a home computer.  If this sounds like something you might want to do, read on!

Why Go to the Trouble?

But, you may ask, why should you put the time and energy into making your own planning spreadsheet from scratch when there are plenty of powerful software programs out there that will do this analysis for you — complete with bells and whistles such as Monte Carlo analysis that are difficult to replicate on your own?   True, your homegrown version will almost certainly be more basic than some of the slick tools available for free on the Internet.  But the Achilles heel of relying on other people’s software tools is that you don’t really know what they’re doing.  In most cases, they are set up to be easy to use; you enter a few parameters – your age, your current salary or expenses, your savings – and the software does the rest for you, spitting out probabilities of success with striking visuals for you to admire.  Even if you read the available documentation, there will still be many unexplained analytical choices – many you aren’t even aware of — that someone else has made for you.  This is why different online tools will sometimes give you dramatically different results for reasons that can be mystifying.

Rather than rely on the man or woman behind the curtain to be making the right choices, why not do this analysis yourself?  In addition to making your financial analysis completely transparent, a personal financial spreadsheet gives you as much flexibility as you desire to build in your own idiosyncratic circumstances and assumptions (e.g., retirement dates, college expenses, care-giving costs, social security timing).   You can do sensitivity analysis on key assumptions, such as your expected retirement spending level or rate of return.  You can even use it to test “what-if” scenarios – different retirement dates, alternative Social Security claiming strategies, the impact of downsizing or moving to Costa Rica.

Your personal spreadsheet should not be your only financial planning tool.  While you can do sensitivity analysis with a spreadsheet, it’s far easier to do historical analysis or Monte Carlo analysis, with its thousands of simulations, using online or other software tools.  Still, your financial spreadsheet can be a very useful and powerful planning tool, a core piece of your retirement planning that you control. 

Gather Critical Data

So let’s get started.  The first step is to collect some key information.

Savings/Investments data, including:

• Your current savings, divided into retirement (tax-advantaged) and other (taxable);
• The amount you save each year for retirement (if you’re not yet retired); and
• Any windfalls you anticipate (e.g., an inheritance).

Spending data, including:

• Your current budget, and your expected budget in retirement (if different – see this post); and
• Anticipated big-ticket items, such as college expenses for your kids, a new roof, or a wedding. 

Income information and estimates, including:

• Anticipated Social Security retirement benefits (go to My Social Security to see yours);
• Income from any pensions you have coming; and
• Salary or income from work (if not retired, or planning to work in retirement).

Your plans in retirement, including:

• When you (and your partner, if you’re part of a couple) plan to retire;
• When you intend to take Social Security and any pensions (see this post and this one); and
• Other plans, such as downsizing, moving to Italy, or starting a business, that will have a significant effect on your finances.

Create the Spreadsheet

Now let’s create the spreadsheet.  It will be wide one – one column for each year, starting with the current one.  It’s helpful to label the columns with your age also; otherwise, it may not be apparent that you turn 70 ½ in, say, 2049.  Since you want to plan conservatively for a comfortable retirement and a long life, you should run the spreadsheet out to age 95 or 100 (rather than, for example, your life expectancy). 

The rows will be divided into two sections: (1) savings and investments, and (2) annual income and expenditures.  The savings/investment section starts with your current savings (earmarked for retirement and otherwise), entered in the far left-hand column (the present).  Future estimated savings grow (hopefully) as you augment them year by year and they increase over time at an assumed rate of return.  The income/expenditure section will contain a projected annual income statement for each year, with a bottom line amount to be added to savings (if expenditures are less than income) or withdrawn from savings (if expenditures are greater).   Here’s an example of the first five years of a planning spreadsheet for a couple, one of whom is currently 45:

Retirement Planning Spreadsheet (first five years)

The inputs needed to populate your spreadsheet are quite simple:

• Your current savings;
• Your anticipated annual income and expenditures;
• Your planned annual contribution to retirement savings; and
• Your expected rate of return.

It’s a good idea to create a separate input table so you can alter your assumptions and examine what-if scenarios without diving into the guts of your spreadsheet each time.  The spreadsheet can simply use whatever input values are currently in your input table, which you can easily see and alter at will.  Here’s an example of an input table:

Calculations in this spreadsheet are also simple:

• In the income/expenditure section, you will need to calculate your net income or loss for the year.  This is simply addition and subtraction. 

Example: Gross annual income – annual expenditures – any unusual expenses = net income

• In the savings/investment section, you will multiply the prior year’s value by your assumed rate of return, and add any additional new savings from the year just completed.

Example: (Prior year savings X Rate of return) +/- net income or loss = New “other savings” amount

Once your calculations are constructed for a column or two, and you’re confident they’re doing what you intend them to do, you can simply copy them out until age 95 or 100 via the magic of Excel.  Here’s an example of a complete spreadsheet (showing one of every ten years):

Retirement Spreadsheet

Using our base assumptions (listed above), this couple’s plan looks pretty good.

Are we done? Sorry, no. We’ve covered the mechanics of setting up a planning spreadsheet, but we still need to understand how to use it to get useful results. My next post will complete the story, covering how to handle inflation, choose an estimated rate of return, perform sensitivity and what-if analyses, and check your results.

Thanks for stopping by. Please come back soon to read Part II.