Wednesday, December 21, 2011

My SIMPLE Retirement Saving Calculator /Spreadsheet

This post introduces my "back-of-the-envelope" retirement model. I've reduced retirement planning calculations to the bare minimum.

It takes a complicated model to account for all of the variables in retirement planning. In this post, I've made some simplifying assumptions. I've tried to develop a "bare bones" model so that we can focus better on the big picture, and still get results that are in the right ballpark.

The Observations Back-of-the-Envelope Retirement Savings Calculator

Retirement planning: Excel spreadsheet to calculate what percent of salary to save
Notes: Click on the screenshot to expand it. The link to download the spreadsheet is at the end of the post. Don't have spreadsheet software? See Related Posts below.

How Much Money Will You Need to Retire?

The key simplifying assumption was to estimate the savings you will need at retirement using
a simple multiple of your expected expenses in your first year of retirement. This eliminates many of the calculations in my original model. This approach is consistent with the increasingly popular recommendation that retirees begin retirement with a 4-5% first year withdrawal rate.

What is a 4% - 5% Withdrawal Rate?

That simply means that in your first year of retirement you withdraw 4% to 5% of your retirement savings. The assumption is that in subsequent years you will increase your withdrawals to keep pace with inflation.

For example, at a 5% withdrawal rate, a $1,000,000 portfolio would support a first-year withdrawal of ($1,000,000 x 5%=) $50,000. It follows that you would need retirement savings of (100%/5%=) 20 times the initial withdrawal rate. And, in order to support a first-year withdrawal of $50,000, you would need retirement savings of ($50,000 x 20=) $1,000,000.

How the Model Works

In the graphic above (click to expand), the example is for a 25 year old, Pat, making $100,000/year who plans to retire on his 65th birthday. Pat estimates that, in today's dollars, he will spend $75,500/year in retirement (including income taxes). Using this site (or my graph), and given his current $100,000 salary, Pat estimates that he will receive about $25,500 in Social Security payments his first year of retirement. That leaves ($75,500 - $25,500=) $50,000/year that his investments need to cover.

Since Pat has Social Security, he chooses to target retiring with 20 times the yearly expenses that his investments need to cover (a 5% withdrawal rate). Therefore, Pat will need a $1,000,000 investment portfolio when he retires. Here, we assume Pat has no current retirement savings, so he'll need to accumulate all of it between now and retirement. (Note: if Pat already had $20,000 in savings, he'd only need about $900,000, not $980,000, from his new savings since the $20,000 would be expected to grow to $100,000 by the time he retires.)

What Percent of His Salary Does Pat Need To Save Each Year?

The next step is to calculate how much Pat needs to save each year, in today's dollars. To do so, we need an estimate of what his annual return on investment will be. It's easier if we remove inflation from the equation. Pat expects to earn 7%/year on his total portfolio, and expects inflation to average 3%/year. Thus, he entered (7%-3%=) 4%. (The actual spreadsheet gives you a little help in estimating your return.) It turns out that if Pat saves $10,500/year and his investments earn 4%/year after inflation, expenses and taxes, he will accumulate the needed $1,000,000.

The final step is just to calculate that Pat needs to save ($10,500/$100,000=) 10.5% of his salary each year.


This very simple sequence of calculations shows that if Pat invests $10,500/year at 4%/year, after inflation, expenses and taxes, by age 65 he will have accumulated about $1,000,000 in today's dollar. This amount has a good chance of supporting $50,000/year in withdrawals, which, combined with his $25,500/year from Social Security will yield the desired $75,500/year in pre-tax income in today's dollars.

This is a "back-of-the-envelope" approach. It's intended more to get young investors started. When you're under, say, 45 there are so many unknowns that the critical thing is just to get in the ballpark. If you're over 55 or so, consider supplementing it with some additional analysis.

Retirement savings are assumed to grow tax-free in retirement accounts (e.g., in an IRA or 401k).
All dollar amounts are in today's dollars.
The 4% rule assumes all of your income is coming from investments. The larger the percentage of your expenses that will be covered by guaranteed income, the better the chances that a higher initial withdrawal rate will be successful.
The 4% rule is designed to support about 30 years in retirement. If you retire at age 70 instead of 65, you can safely increase your withdrawal rate to about 4.5%.  Retire early, and you'll need to reduce your withdrawal rate.
The model assumes you begin collecting your pension and social security as soon as you retire.

Related Posts

Start Retirement with a 4% Withdrawal Rate: an intro to the 4% withdrawal concept, from Time magazine. For a more detailed discussion, see Wikipedia.

Easy-to-use graphs based on this spreadsheet:

Start Investing for Retirement When You're Young: Graphs showing the surprising impact of starting at age 25 vs 35 or 45.
What Will $100,000 be Worth Invested in the Stock Market for 20 Years?: a look at the variability of market returns (a key reason withdrawal rates must be kept low).
Build Your Own Pension Using Immediate Annuities: A way to mute the impact of market variability.
A Retirement Planning Calculator/Spreadsheet: my earlier, more complete retirement model.
Do You Need a Personal Strategic Plan? a process for setting goals and establish priorities.

Link to Spreadsheet

Here's the link to download the Excel spreadsheet: The Observations Back-of-the-Envelope Retirement Savings Calculator. If you have any problems accessing or using the model, see this post.

Some Other Retirement Planning Links

Social Security Income Estimator : the official site.  For an approximation, see this site, or my graph.
Many versions of Quicken include a basic retirement calculator (see Planning - Financial Calculators in the Quicken menus).
Or, see on-line calculators such as the one at

For lists of other popular posts and an index of stock market posts, by subject area, see the sidebar to the left or the blog header at the top of the page.
Copyright © 2011                 Last modified: 2/23/2013

Share This Article

Delicious Bookmark this on Delicious To share via Facebook, Twitter, etc., see below.


  1. Hi, I love the spreadsheet. I suggest adding an assumption that says "model assumes your retirement age is the age at which you being receiving social security benefits."

    I think I figured out how to manually override the COLA retirement income column to run the early "quit work" scenario I'm thinking of. It would be awesome though if you could program it to allow for retirement age and social security benefit withdrawls to start at different times.

    1. Glad to hear you're finding the spreadsheet helpful. I've added the assumption you suggested. I've also added your proposed enhancement to my list of things to do. Unfortunately, it's a very long list, so don't hold your breath.

      Thanks for reading.


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.