Do you have a concrete retirement goal to work toward? This amazing (free) mini-class will walk you through the steps of calculating just how much you'll need to save for retirement! Love the spreadsheet!

RE401: Calculating Retirement Needs

If you’re just stopping by for the first time, this is a class in a series of classes over the next few months which will culminate in the development of a complete financial plan.  Stop by HERE for a complete list of classes currently available and HERE for more information about the website.

Class Objectives: To calculate how much money will be needed to meet needs in retirement.
Prerequisites: SA302 Investing Basics: Time Value of Money
Handout: A Vision for Retirement
Assignment: Download the estimated retirement needs spreadsheet for Excel | Google Docs


There’s one fact that especially bothers me about American savings habits.  And it’s this… Over half of Americans have less than $10,000 saved for retirement.  While millennials are the least likely to be saving for retirement, even the baby boomer generation (now 55+) still surpasses a rate of 40% without retirement savings {source:}.

While I can and do recognize that I’ve had many opportunities and circumstances that so many others don’t have in our country, I believe that the core of the problem is not that people can’t afford to save for retirement but that they would rather spend money on other things.

One problem is that people often do not have a goal.  They don’t know what an adequate amount even is when it comes to saving for retirement, so they don’t save at all.  Sure, there are an unlimited number of unknowns about what will happen in over a 30+ year stretch of time.  However, setting a goal is better than the goal of “I’ll save as much for retirement as I can”, which rarely works.

The very first thing we covered in this series of classes was setting SMART financial goals.  The “M” stands for “measurable”, and that’s exactly what we’re going to do in this class. We are going to find the number that you need to save for retirement so that you can set that specific numerical goal and make a plan to achieve it.

By now, you know how much I love my spreadsheets.  So, of course, I have a spreadsheet for you to go through the process of calculating your retirement needs (previewed below).

Let’s take this process step-by-step.  It’s just 3 steps – as simple as 1-2-3!


Hopefully, you already have a current budget or cash flow statement that you can start from to help with determining your retirement budget.  If you’ve used the one from the previous class, you can directly copy and paste it to this retirement budget spreadsheet.  Otherwise, you should transfer your current budget numbers into the spreadsheet.

Then, adjust your numbers in today’s dollars, to determine amounts that you would spend during retirement years.  For example, right now we have a family of 5 (myself, my husband and 3 kids).  Some of our significant budget items include groceries, kid activities, and college savings.  These will significantly go down or be eliminated during retirement years.

Retirement Income Sources

Because the purpose of this exercise is to determine the amount you need to save for retirement, do not include investment income that will accrue on your current retirement accounts.  However, the following income items should be entered in the spreadsheet for retirement years:

  • Part-time employment – You should be very careful if you enter part-time employment as an income source during retirement because it’s very likely that at some point you’ll have to stop working out of medical necessity.  If you do plan on part-time employment, be sure to subtract the applicable withholding taxes.
  • Interest & dividend income  – If you have other taxable investments outside of retirement accounts (such as dividends from stocks or interest from municipal or commercial bonds), enter the projected income from them during the retirement period.
  • Business & rental income – Any other passive business or rental streams should be entered at their net value (after all expenses).
  • Pension income  – Although it’s rare for our generation, there are still a few lucky people out there (maybe even you?) with a pension.  If you have an estimate of how much your pension will be at retirement, go ahead and include it in other income.
  • Social Security income – Social security is a huge uncertainty for Millennials and Generation X.  Currently, the Social Security Administration states (directly on the social security statements) that “by 2034, the payroll taxes collected will be enough to pay only about 79 percent of scheduled benefits”.  To obtain your current projected Social Security projected monthly payment, go to the “my Social Security” website and create an account (or login).  You can get you statement immediately online.  My suggestion is that if you do want to include Social Security as an income source during retirement, you should only include 75% or less of your projected monthly benefit.

Retirement Expenses

It is beneficial to take a detailed look at the significant changes that will occur with regards to your expenses during retirement.

  • Savings Goals – While you’re still likely to be “saving” money from your retirement account withdrawals for things like car repairs or medical expenses during retirement, you won’t be saving in the traditional way as before retirement.  You should already have your retirement savings to draw from in the event that these things come up.  This number is likely 0 during retirement.
  • Giving  – Building a big enough nest egg will ensure that you can give back to your community even during the retirement years.  Do yourself a favor and don’t assume that you’ll not be giving to charity during retirement.
  • Housing – This will likely determine the biggest portion of your budget.  Will you still have a mortgage payment during retirement?  Even if you don’t have a mortgage, owning your home still requires cash outlays for property taxes, insurance, and home repairs.  Or, do you plan to rent instead?  Be sure to include utilities either way.
  • Transportation  – Self-driving cars are going to allow us to still get around by ourselves when we’re old, but it’s not going to be free.  It’s anyone’s guess how much transportation will cost in 30 years, but you’re definitely going to want to put in an estimate.  At a minimum, assume that your transportation costs will be similar to now if you didn’t have to commute back and forth from work.
  • Debt Payments  – I really, really hope that you aren’t paying off student loans and don’t have credit card balances by retirement (ouch), but if there is any other debt that you’ll likely have during retirement, enter the monthly payments here.  Or just do yourself a favor now and get rid of that debt by going through the Debt Overview class.
  • Food  – Your grocery bills will likely decrease during retirement, but you may really enjoy eating out instead.  Consider your current habits and how much you currently eat out.
  • Medical – It’s absolutely impossible to determine medical expenses in 30 years, but you can start with the current cost of Medicare premiums which are $134/month dollars for those that claim married filing joint status on their taxes with less than $170,000 for 2017.  A Medigap policy currently costs an additional approximately $150 per month.  Also, plan to save for medical expenses in an HSA account now if you have a high deductible plan that qualifies you to contribute to one.  You can use your HSA account to pay for the Medicare premiums with pre-tax funds.
  • Personal & Miscellaneous – Go through each other expense line item and estimate your monthly expenses based on the lifestyle you envision for retirement.  Some of these may go up during retirement (pet costs because all old people need a pet, right?) and others may go down or be eliminated (child care, professional dues, etc.).
  • Non-Recurring Expenses  – Do you have dreams of traveling in retirement?  Be sure to include the monthly average of travel expenses you are expecting on an annual basis.  Don’t forget to include Christmas and birthday gifts for your dear family that is taking such good care of you in your old age (I hope!).
  • Taxes – You will still be required to pay taxes on retirement income if you have income from a part-time job, rental properties, pension, traditional 401(k) or traditional IRA (anything besides a Roth).  These types of income are typically taxed at ordinary income rates.  To learn more about the tax formula, go to the beginning or intermediate tax class.  Just make sure you include some estimate of taxes.  If you’re not tax savvy at all, at least estimate 25-30% of the income you expect, which will need to include the distributions from retirement accounts other than Roth accounts.  Yes, this will mean going around in a circle to some extent, since your distributions depend on your expenses (one of which is these taxes!).

Now that you have a reasonable estimate of expenses (in today’s dollars), check the bottom line “excess (shortage) per month to be sure that it makes sense.  You don’t want to greatly underestimate your retirement budget by planning to live on poverty-line income when you’ve been living comfortably in the previous 20-30 years prior.  Be sure to be generous in your estimates.


The next thing that you need to do is determine the following inputs:

  • Annual Inflation Rate – Inflation is expected to average about 2.5%, but this is certainly an unknown.  If you want a conservative estimate, go ahead and go with a higher inflation number to make sure you have plenty of retirement savings.
  • Expected Market Rate of Return – Market returns are of course not guaranteed and no one can predict the future.  I generally go with a reasonably conservative non-inflation adjusted return of around 7%, but you can estimate a lower return if you’re a very conservative investor.
  • Current Age – This one should be easy for you!
  • Expected Retirement Age – if you’re planning on a traditional retirement, go ahead and put age 65-67 in there.  If you’re planning for an early retirement, go ahead and assume 40, 45, 50 (or whatever your magic number is!).  If you’re planning on never retiring, you’re in the wrong spot.
  • Years During Retirement – Feel like pondering your own mortality today?  Pick a death date and subtract that from your expected retirement age.  This is how long your money needs to last after the day you retire.  Be conservative and optimistic here.  You don’t want to run out of money in retirement.
  • Current Balance of Retirement Account – For this purpose, total all your current retirement account balances together (401k balances, traditional and Roth IRAs, etc.).  Don’t include taxable accounts and other funds that you would be able to use in retirement.  The income generated from those funds should be included in the budget spreadsheet.

Don’t get stuck on this step.  Use the averages, throw in some ages and move on.  The great thing about this spreadsheet is you can go back again and again and readjust your inputs and goals.  Just keeping going.  This number is simply to give you something to work toward.  You can (and will) adjust your plan as you go!


This spreadsheet should give you a fairly quick estimate for planning for retirement.  However, it should be mentioned first that if you’re close to retirement or have complex financial investments or retirement needs, this is not going to provide enough detail and analysis for you.  This is a ballpark figure, it doesn’t include extensive tax considerations.

First, you’ll see in the “Results” section that it will adjust the manually entered annual income and expenses as entered in step 1 (above) to inflation-adjusted amounts for the first year in retirement.  This will be the starting point for your retirement needs.Then, it will determine the lump sum amount you’ll need at retirement age based on this starting budgeted annual expenses and inflation-adjusted numbers for the years following.The last things you’ll see are the annual or monthly contributions you’ll need (these are based on end-of-the-period contributions) to reach that lump sum amount by retirement age.  If you already have enough saved for retirement that your current balance will grow to the lump sum needed by retirement age, the needed contributions will show as the number 0.

We’ll be discussing contributing to retirement plans through employer-sponsored plans and individual retirement accounts (IRA’s) in the next classes.  For now, adjust the various inputs such as retirement age and return rates and see the difference that it makes in the monthly or annual contribution amounts required.  You’ll be surprised at how much of a difference a few years makes in compounding interest!


Jim & Mary Smith plan to retire early but are not currently setting aside money for retirement due to their tight budget and debt repayment.  However, their goal related to retirement (as we discussed in the example in class PF101: Financial Goals) is to retire by age 55 without a mortgage and debt free.  They go through this exercise and estimate the following:

Example of a spreadsheet to calculate retirement needs, including determining a post-retirement budget. EXACTLY what I need to set that retirement financial goal!

Example of a spreadsheet to calculate retirement needs, including determining a post-retirement budget. EXACTLY what I need to set that retirement financial goal!

For this exercise, note that I’ve assumed $1,500 per month in Social Security income and $200 per month in taxes related to withdrawing money from their retirement accounts (they currently have traditional retirement plans instead of Roth accounts).  They love to travel but mostly plan to travel around the United States during retirement, which won’t be nearly as expensive.  In addition, they plan to pay off their mortgage before retirement and have minimal housing expenses.

The end result is that they will need $740,000 for retirement at age 55.  They would need to contribute $905/month to reach this early retirement goal, which is definitely not possible at this time due to their debt payments and other expenses.

If they were to retire at age 60 instead (resulting in 35 years in retirement), they would only need to contribute $596 per month to retirement accounts as shown below:

If they were to retire at age 65 instead (resulting in 30 years in retirement), they would only need to contribute $382 per month to retirement accounts as shown below:

They set a goal to start saving $100/month starting as soon as possible to retirement accounts and use any extra commissions beyond what they’ve budgeted for debt payments toward retirement.  They plan to increase the $100/month contribution by at least 5% each year.  In addition, they plan to reassess their overall budget and spending to try to allocate more money to retirement savings.


Your homework assignment is to estimate and plan for basic future retirement needs using the provided spreadsheet.

  • IMPROVING -Use an online calculator from any major brokerage company (such as Vanguard) to estimate a future retirement sum goal and make a plan to save for those costs.
  • INVESTED – Use the spreadsheet to analyze future retirement needs.  Start saving now and set a concrete goal for retirement savings.
  • UNSTOPPABLE – Use the spreadsheet to analyze future retirement needs and expected contributions to meet those goals.  Revise your financial goals to include a set number to set aside each month (or year) to save for retirement as well as a lump sum to reach within the specified time frame.


Have you calculated your retirement goal?  Were you surprised by the result?


7 Responses

  1. I use personal capital almost exclusively and I have to admit that I was pleasantly surprised when including some of the data. It looks like I am on target for retirement and shouldn’t have too much difficulty maintaining the lifestyle that we currently lead. Hopefully that stays true in the future 🙂

  2. We love pensions! I guess we’re one of the lucky few who will have that lifetime income, and assuming Paul can work for another 20 years in a state or local government job, the pension payments should sustain our current standard of living without us touching the retirement accounts.

    We’ll keep contributing to the 401k, though. After all, the world won’t travel itself!

    1. Sounds like an awesome plan, plus you guys will still be young in 20 years! We were covered partially right before the auto industry completely got rid of pensions. I believe we have about $5 per month coming to us in 30 years. Yes, we’re going to have to keep contributing to our 401k too.



I’m Kathryn Hanna-wife, mother of 3 and a Certified Public Accountant. I love to budget (really, I do!) , build spreadsheets and spend money on travel, sewing supplies and good chocolate.


12 Month Financial Plan Sidebar


Easy-to-customize spreadsheets to improve your entire financial life from budgeting to tax and retirement planning.