Tuesday, October 27, 2009

A Retirement Planning Calculator / Spreadsheet

.

Putting it All Together


Retirement planning can be thought of as consisting of two phases:
  1. The accumulation phase -- saving for retirement
  2. The distribution phase -- living in retirement
In the previous two posts in this series, we looked first at establishing a target for the savings needed to cover expenses during the retirement years, and then at creating a savings plan to reach that target. In this post, we'll add some enhancements to our retirement planning Excel spreadsheet in order to create a consolidated accumulation and distribution plan -- we'll put it all together. This, in turn, will give us a foundation to do some additional analysis in future posts.

Note: For a quicker estimate of how much you'll need to save, see the graphs in How Much Money Will You Need to Retire?,  What Percent of Your Salary Should You Save For Retirement? (by starting age), or percent to save with higher incomes.


A Consolidated Retirement Accumulation & Distribution Plan



Retirement Planning: Graph showing retirement plan from Excel spreadsheet/calculator
Consolidated Retirement Saviings Accumulation & Distribution Plan

The graph above shows a retirement plan for a 35 year old, Alex, who hopes to retire at age 65 and live to age 90 (click on graph to expand it. See the end of the post for a link to the spreadsheet). Alex has no savings yet, but is planning to save 13 percent of salary from now on. Alex plans to put 3% of pay into taxable accounts and split the remaining 10% equally between
a tax-deferred employer-sponsored 401k and a tax-free Roth account. (For a full description of this scenario, see the appendix at the very end of this post.)

The graph shows the accumulation of savings up until age 65, followed by withdrawals. Withdrawals are assumed to be made first from the taxable account (light blue), then the tax-deferred 401k account (medium blue), and finally from the tax-free Roth account (dark blue). Note that the balance in the Roth account goes to zero before Alex is 90 years old. The bright red slice is an estimate of the additional savings Alex would need in order for the savings to last until age 90.

Start Planning Retirement Early


This graph suggests that, under reasonably conservative assumptions, saving 13% of your income may not generate enough retirement savings if you wait until age 35 to start. Note, however, that if Alex were lucky enough to have an employer who provided a pension and matched 401k contributions up to 3% of employee salary, the situation would be remarkably better. Waiting until age 67 and receiving full social security benefits would also make a significant difference. (Note: for more on the importance of getting an early start, see Start Investing When You're Young).

How to Use Traditional Retirement Planning Calculators More Effectively


Traditional, so-called "deterministic," calculators/spreadsheets like this one help to identify the fundamental inputs to retirement planning, and the basic calculations and issues involved. In fact, this is a modified version of the spreadsheet I developed to do my own retirement planning early in my career. However, they must be used with care. In particular, I think it is important to be conservative in your assumptions regarding stock market returns -- especially if you are near, or in, retirement. To understand why, see Don't Plan Retirement Assuming Average Stock Market Returns, or The Variability of 20-Year Stock Market Returns, in Dollars.


Related Materials

My SIMPLE Retirement Savings Calculator/Spreadsheet: my simpler, "back-of-the-envelope" approach to calculating how much you need to save; links to easy-to-use graphs to determine how much you'll need on your retirement date, what percent you need to save, how much you should have by age, etc.
How Long Will I Live?  One of the most vexing issues in retirement planning.
Build Your Own Pension Using Immediate Annuities: An increasingly popular component of retirement plans.
How Much Money Will You Need to Retire? for a discussion of the "needed savings" section of the current spreadsheet
What Percent of Your Salary Should You Save? for a discussion of the "planned savings" section of the current spreadsheet
100 Years of Inflation History: Inflation if one of the biggest threats to retirement.
Retirement Spending & Income Plan adds the income calculation to the model.
Do You Need a Personal Strategic Plan? to help you set goals and establish priorities.

Link to Spreadsheet

Here's the link to download the Excel spreadsheet: The Observations Retirement Planning Spreadsheet (version 3). If you have any problems accessing or using the model, see this post. Fundamentals of Investment Valuation introduces the fundamental calculations.

Some Other Retirement Planning Links

Social Security Income Estimator : the official site.  For an approximation, see this site.
Many versions of Quicken include a basic retirement calculator (see Planning - Financial Calculators in the Quicken menus)
The calculator at Bankrate.com is similar.

Other Spreadsheets

Inflation Calculator: calculates inflation rate between any 2 years, and converts dollars to equivalent purchasing power.
Planning to Buy a House Spreadsheet checks proposed purchase against home affordability guidelines.

Share This Article

Delicious
Bookmark this on Delicious
See the "Share" option in the Menu Bar at the top of the page to share via Facebook, Twitter, etc.

Copyright © 2009. Last modified: 12/14/2013

Appendix

Following is the detailed input to the Excel spreadsheet used to produce the graph above. (Click to expand)

Retirement planning assumptions for Excel spreadsheet/calculator

Retirement planning assumptions for Excel spreadsheet/calculator
Note: Click on the above screenshots to expand them. The link to download the Excel spreadsheet is above.

4 comments:

  1. I looked at your spreadsheet....does it account for mandatory tax deferred investment portfolio withdrawals starting a 70.5 years and through the actuarial life less the taxes on the withdrawal? If not, that may have significant impact on terminal values.

    ReplyDelete
  2. Thanks for the heads-up. I included a sentence in the Notes-Assumptions section/tab of the spreadsheet that says "The model assumes you are never forced to take Required Minimum Distributions from your tax deferred accounts -- i.e., that your needed income is always at least as large as the required minimum." Unfortunately, you apparently only see that sentence when you're working in Google Documents/Spreadsheets. When you download to Excel, apparently all you see is the last word -- "minimum." Until I can figure out how to fix it, in Excel you only see the whole sentence if you edit that line (i.e., F2).

    More importantly, I think, the retirement planning calculator/spreadsheet does take taxes on the withdrawals into account; it uses the retirement income tax rate that you supply (withdrawals are taxed as ordinary income).

    My thinking was that most people will need to take more than the required distributions. If that's not the case, as you suggest, the model will overstate the "terminal values" (how much will be left at the end of your plan); as a result, your heirs may be disappointed. Luckily, among all of the potential problems in retirement planning, for most people that is one of the least worrisome....

    Thanks for stopping by my site.
    Al

    ReplyDelete
  3. I really like this spreadsheet. Unlike most models, you can actually see what's going on under the hood.
    I took a run at enhancing for the RMD. Added a "RMD Factor" column - the number you divide the IRA by to get the RMD, pulled from a website. Calculated the RMD. Then calculated the AFTER-TAX value of the RMD, multiplying by the retirement tax rate.
    Then subtracted the RMD from the following year's IRA value and ADDED the after-tax value of the RMD to the following year's After-tax value.
    Anybody have a comment on validity of that approach?

    ReplyDelete
    Replies
    1. TW,
      Glad you like the spreadsheet. I think being able to see what is "going on under the hood" helps readers to understand how things really work, and gives them a better understanding of what some of the issues are.

      Sorry, but I'm not absolutely clear on where you're adding the after-tax value, so it's difficult for me to comment on the validity of your approach. Remember, the RMD is the MINIMUM distribution. Did you include a check to determine if you need to withdraw MORE than the minimum?

      Thanks for stopping by.

      Delete

No spam, please! Comment spam will not be published. See comment guidelines here.
Sorry, but I can no longer accept anonymous comments. They're 99% spam.