Retirement Planning Spreadsheet

He knows nothing; and he thinks he knows everything. That points clearly to a political career.

- George Bernard Shaw

Figure 1: We are all getting older.

Figure 1: We are all getting older.

In a previous post, I discussed standard rules of thumb that you're told when you first look for pension advice & retirement planning. I am interested in this topic for a number of reasons:

  • I help my mother (who is retired) manage her money.
  • I am not getting any younger and I am saving for retirement.
  • I am encouraging my sons to save for retirement.

As I thought about it, people really need a spreadsheet that will allow them to try different retirement options – I have not been happy with any of the web-based retirement planners that I have worked with.

I have prepared a spreadsheet that allows modeling for:

  • Wage increases over time
  • Taxes and inflation while working – I do not model inflation during the retirement years.
  • Age you start saving and when you retire
  • Pension and Social Security Income
  • Some canned scenarios that will allow you to see examples of how it works – use Excel's Scenario Manager to see these examples
  • Amortization tables that show on a payment-by-payment basis how money is invested while working and expended when retired.

With this spreadsheet, I can use Excel's other functions (like Goal Seek and Solver) to experiment with different financial parameters (e.g. savings rate, rate of return) to ensure that I have a plan that will meet my objectives. Just as importantly, I can used the spreadsheet to test the sensitivity of my plan to deviations in market performance from my assumptions.

This spreadsheet was interesting to prepare because I could not use the standard Excel formulas for future value – I needed to model payments that were changing with time because of wage increases. I ended up using an array function, which did a very nice job.

The spreadsheet is here and uses no macros. You can find my previous post here.

This entry was posted in Financial. Bookmark the permalink.