Planning For Retirement

View this article online at and visit to sign up for free news updates
By on May 19, 2010 in Retirement with 0 Comments

I have become an expert at planning for my retirement—at least in my own mind. I can’t pretend to be an expert at retirement since I haven’t yet taken that step. About 5 years ago, I started the planning process by trying to find out just how much money I would need in order to retire.

I quickly discovered several rules of thumb. One prominent one is that you need 70% of your income but I also found that I might need 110%. Also, I shouldn’t withdraw more than 4% from my investments. These kinds of guides left me with more questions than answers. Later, I discovered on-line calculators but they also didn’t provide the kind of answers that made me feel comfortable because I didn’t know what assumptions were behind the calculations.

With an education in engineering (we’re known to like making calculations) and with a business degree (aware of the time value of money) I decided I could do some of my own planning. Here are a list of ideas I decided to include, in no particular order:

  • Plan to have cash income until death.
  • Needs for travel, medical, entertainment, etc. will change over time.
  • Investment returns could be averaged over time but timing of peaks and lows will significantly affect cash flow.
  • The timing of peaks and lows of expenses is also significant.
  • Taxes must be considered.
  • Itemization and separate treatment of expense categories is important.
  • Itemization and separate treatment of income is important.
  • There is no single set of possible future outcomes. A year-by-year forecast could be informative.
  • Oversimplification can lead to wrong conclusions.
  • Inflation rates change.

Hand calculations would be tedious and could never capture all the variables implied by this list. In order to provide enough detail, a spreadsheet is the best approach.

How best to handle all those discrete items? Individual tabs in the spreadsheet work. (Check out this spreadsheet in Microsoft Excel.) I started by collecting my actual expenses for a year and grouping into categories. Each category then became an individual tab in the spreadsheet, e.g.,food, travel, housing, medical, etc. I then used the same process for income.

One tab is for the FERS income. This income is uncommon in the work place today and is significant because it is adjusted for inflation although it doesn’t match inflation. The Social Security income tab is similar. A net worth tab is also included because that may be a resource to convert into cash in later years or perhaps become an inheritance. Then I created and “expectation” tab. I created summary expense and income tabs. I was generous in use of tabs and ended up with 23 different tabs.

The “expectations” tab is an innovative detail in application. Others came up with the concept. This is a matrix, i.e., a table for each year and each category where I could capture, and easily change, future plans. It is an attempt to capture the concept of go-go, slow-go, no-go retirement phases. For example I could plan extensive travel in the first few years after retirement then taper off to no travel in 20 years. Entertainment is likely to taper off in later years. Gift giving might increase. Medical expenses might increase. Each expense category has an expectation for each year.

Inflation fluctuates as everyone knows. Most people might think that it will average out and it’s okay to just use an average rate. A little study shows that’s not the case and higher inflation earlier in a retirement plan will cause more damage to available cash than it would later in the plan. Likewise, lower inflation rates earlier will cause less impact on expenses later. Similar logic holds true for rates of return on investments. I handled these impacts by using random numbers to select rates from an expected range of inflation rates, say between 2% and 16%. Actual historical inflation doesn’t change dramatically from year to year so the random number selection was modified to resemble historical curves. These were conveniently added to the summary tabs for summary income and summary expenses.

Calculations that take all those variables into account for each year result in lots of data that can easily overwhelm anyone and provide little meaning. Meaning is provided in a summary graph that shows cash flow, income and expenses for each year. One graph, however, can be as misleading as a simplified hand calculation. The value of all this work comes from causing the spreadsheet to re-calculate multiple times and then observe the resultant graph. The human mind is very good at picking up a pattern from all those calculations.

What does it all mean though? Obviously, if all re-calculations and graphs always show a positive cash flow, then I can cruise blissfully into retirement. If my results show some some positive cash flow and some negative cash flow, this it will be quite prudent to re-evaluate my discretionary spending. If I always show negative cash flow, then I have to postpone retirement or drastically change spending.

Now that it’s created, how good is the spreadsheet? I’ve tested it by using extreme numbers to see if results are as expected. I’ve also shared it with anyone interested and welcome their critique. Some of these folks are also planning retirement and have used some commercial tools and services and report that my spreadsheet provides comparable results.

This example graph is for a hypothetical person who needs to watch expenses very closely and be prepared to somehow convert some worth into something that will provide cash by age 76 where cash flow turns negative.

Mr. Stevens has been a federal employee for 24 years and was planning to retire at age 62, four years ago. The economic downturn delayed that and gave him time to refine his retirement planning.

© 2020 Andrew Stevens. All rights reserved. This article may not be reproduced without express written consent from Andrew Stevens.