How much of your salary do you need to save to reach your retirement goal? That is not a simple question to answer, but that is what this spreadsheet attempts to help solve. It makes quite a few assumptions, and doesn't include nearly as much detail as you would find in professional financial planning software. But, for a quick estimation it may be useful.
Retirement Calculatorfor Excel
Template DetailsLicense: Personal Use
(not for distribution or resale)
"No installation, no macros - just a simple spreadsheet" - by Jon Wittwer
If you would like to use this Retirement Calculator in your business to assist your clients, you may purchase the commercial-use version.
The header includes a place for the client's name and your company info.
The Vertex42 branding is outside the print area so it doesn't show up when you make a printout or PDF for a client.
Return Policy: 60 Days
License: Commercial Use
Note (3/13/15): I put this spreadsheet together after receiving numerous requests for a spreadsheet that combined my 401(k) savings calculator with my retirement withdrawal calculator. I still consider this spreadsheet a work in progress, but I think it's finally at a point where people can try it out.
Update (5/5/16): Fixed a bug where the Retirement Income in the table (and chart) referenced inflation rather than the Annual Increase. Update (7/20/16): Added the ability to limit Other Retirement Income to a specific number of years (thanks to Ben Martinek).
Update (7/23/18): Fixed a bug in the formula for Current Annual Contributions. The old formula assumed the employer match was always maxed out. However, the corrected formula calculates the employer match as the Match % multiplied by the Employee contribution or the Max contribution (whichever is less).
Update (11/15/2018): Improved labels and instructions for the Current Savings Contributions section and added a note above the Shortfall section to warn when the shortfall is negative.
Update (3/22/2019): Fixed an error in the "Value of Other Income at Retirement" so that it more accurately predicts the amount of additional salary to save to reach zero at the end of the payout period.
Using the Retirement Calculator Spreadsheet
The most important thing about this spreadsheet to understand is that future rates of return, inflation, salary, disasters, how long you'll live, and other life events can't be known for sure. So, if you are going to use this, realize that it is a mathematical exercise and the inputs that you choose and how you interpret the results are your own responsibility.
Below are some additional explanations that may help you use this calculator.
Rates of Return and Inflation
These are values that just have to be estimated. You can't know future rates with certainty. However, one of the unique things about this spreadsheet is that you can enter different rates of return for the accumulation period and the retirement period. This may be useful because during retirement period, your portfolio will likely be weighted towards lower risk, lower return investments.
Defining Your Retirement Goal
1: Your Salary. Enter a salary you want to live on during retirement in today's dollars. This should represent a lifestyle rather than an actual income or withdrawal amount. The payouts during retirement will be adjusted for inflation (the calculator uses the estimate of the inflation rate to calculate the "inflation-adjusted salary"). If you expect to receive income during retirement, the amount withdrawn from your nest egg is reduced to account for that.
2: Years to Pay Out. The number of years you want your retirement nest egg to last is the second aspect of your retirement goal. Are you going to live until you are 100, 90? Are your kids going to support you when your nest egg runs out? Is the government going to support you?
Current Savings Contributions
The amount that you will save is based on a percentage of your salary. That way, as your salary increases, the amount that you will save will also increase.
Your annual savings contributions in this spreadsheet are not limited to just 401k contributions. However, if you specify an Employer Match, the spreadsheet assumes you are first contributing to a 401k, and then any savings beyond the 401k limits are placed in other accounts.
Updated 11/15/2018: The labels and comments associated with the Employer Match should be more clear now. For this spreadsheet, if you enter 50% as the Employer Match and 6% as the Max Employee % of Contribution, this should be interpreted as "50% match up to an employee contribution of 6%." This means that the company stops matching the rest of your contribution if you contribute more than 6% of your salary.
This spreadsheet doesn't include social security calculations. Instead, you can use the Other Retirement Income section to includes estimates of other income like social security. The social security administration provides a Retirement Estimator that you can use to get a rough idea of what your social security benefits might be.
This calculator does not take into account taxes at all. That doesn't mean taxes are not important to consider. In fact, taxes can have a HUGE impact on your savings. When using this calculator, assume that you are making all the correct tax-related decisions that apply to your specific situation.
More Retirement Calculators
- Annuity Calculator - This spreadsheet lets you enter the total nest egg and the years you want the nest egg to last and it calculates the withdrawal amount.
- Withdrawal Calculator - Instead of calculating the withdrawal amount, this spreadsheet lets you enter the desired withdrawal and it calculates how long the nest egg would last.
- 401(k) Savings Calculator - This one lets you play with some random rates to estimate what your final savings might come to.