Retirement Planning by the Numbers

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

Updated: July 8, 2019 6:11 PM

There are many unknown factors when planning for retirement including, health, travel, hobbies and money. A spreadsheet can be useful in shedding some light on the money part. “Will I have enough money to …” is one essential question to try and answer.

The spreadsheet, available through FedSmith evolved over time and can help answer that question. An explanation of how to use it is provided first and then some disclaimers follow. Even with all the numbers generated, the results should be taken as an indicator of the financial future, not as a precise prediction. Some basic knowledge of spreadsheets is useful.

Enclosed is a spreadsheet in Microsoft Excel. This spreadsheet is in the xls format. Here is a version of the spreadsheet in the xlxs version of Microsoft Excel. Re-calculation is important in this spreadsheet. In Excel, use the F9 key to re-calculate.

Once downloaded, start adding your own data. Most entries are on the first tab called “Q&A” and the places to be filled in are highlighted in yellow. Sample numbers are already provided but these should be personalized. Note that every entry causes a re-calculation. Obviously, the more realistic your income, expenses and expectations are, the more realistic the outcomes will be. One set of the inputs is in the tab “Expectations.”

This is where you get to think about things such as travel, health and housing.

As an example, enter the number 3 in the years for travel where you may want to travel extensively. If you want to make a once-in-a-lifetime trip then that might alternatively be captured on the spreadsheet by going to the “option” tab and entering your cost there. That tab can also be used for special, non-recurring expenses. If you do some consulting or other work, that can be captured in the “PT work” tab.

What do you get out of this?

The graphs show whether or not you will have positive cash flow for the data that you’ve used to personalize the spreadsheet. You won’t be able to sustain a negative cash flow so some adjustments will need to be made to your expectations.

There are two graphs on the “Q&A” tab that show cash flow for 10 years and 35 years. With this spreadsheet, a single calculation and resultant graph may not be meaningful. Re-calculate many times. If you don’t like the answers, change your expenses, work longer or don’t buy that motor home.

Time for the disclaimers. This spreadsheet is not perfect. It was created for FERS employees but can be modified for others. Taxes will change but the impacts probably won’t be as bad as most folks fear. Use of random numbers in the calculations may appear to be arbitrarily applied but more elaborate methods were looked at and didn’t seem to provide more realistic results. The tab on Social Security could be more clear. There is a tab on rental property that could also cover royalties or other investment income.

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.