The spreadsheet below shows the age at which you can retire and pay your retirement expenses from your portfolio income. You can also get the Google Sheet version of this spreadsheet. This spreadsheet uses some pretty complex formulas to figure out when you can retire. But it’s easy for you to use. Just input a few different amounts / rates in the yellow cells. Excel magic does the rest. See the green cell toward the bottom to answer: when can I retire?

When Can I Retire Inputs

This spreadsheet uses 7 inputs. I tried to minimize the amount of inputs because the number one complaint users have with any spreadsheet is that it’s too hard to use. Here’s a quick rundown on the inputs:

Your Current Age – No explanation needed here (I hope).

Expenses in Retirement – Input the annual amount you expect to pay out in expenses in retirement. Use today’s dollars. This calculator adjusts for inflation.

Do you have a mortgage that will be paid off by the time you retire? Make sure exclude that but you would still include property taxes and insurance. Some other common items are food, gas, tax, medical, utilities, entertainment, etc.

Some adjustments you can make here:

  • If you want to account for Social Security you’ll receive in retirement, you can lower your expense amount by your annual Social Security benefit. See below for more information.
  • If you think you’ll pay a bunch of taxes because you’re withdrawing from a deferred-tax account like a 401(k), you can increase the expense amount. See below for more information.

Rate of Inflation – Keep this at 3% unless you want to become a forecaster. Maybe you foresee a 10% inflation rate. Then by all means adjust this number to whatever you want. This number is used to increase your annual expense amount in retirement.

Current Portfolio Value – Combine all your asset accounts in this field. It doesn’t matter if you’re adding a tax-deferred account like a 401(k) or a taxable brokerage account. We figure in taxes paid in the “Expenses in Retirement” field and that’s why the taxable status of the account doesn’t matter.

Investment Return – Change this number to whatever rate you think your above portfolio will generate. This could range from the risk-free rate on up depending on your portfolio allocation.

Annual Principal Additions – Input the amount you’re likely to save each year up until your retirement date. This amount would equal anything you’re contributing to your 401(k), TSP, 403(b) or other retirement account plus your after tax savings. Make sure to include your employer’s contributions if that’s applicable. This amount stops upon your retirement age, at which time you’ll be in a different financial life cycle.

Years in Retirement – This is simply the number of years you forecast you’ll spend in retirement. To say it another way, this is the number of years you want to withdraw the “Expenses in Retirement” amount from your portfolio. I should also point out here that this spreadsheet will factor in principal and interest in the withdrawal phase (not just the income your portfolio generates).

Want to Factor in Social Security?

Me too. I have an easy work around for this. Instead of inputting a bunch of super-complex Social Security formulas, just reduce the amount of your annual expenses in retirement by the annual expected Social Security amount. For example, if your retirement expenses are $50,000 annually and you expect to receive $20,000 in Social Security annually, your NEW “Expenses in Retirement” amount would be $30,000.

This isn’t the perfect way to do this but it’s an estimate after all. All of your assumptions are just that. A lot of people want exact numbers but it doesn’t work like that. Even the Social Security Administration can’t tell you the exact amount you’ll receive until you file. They’ll give you an estimate and there are many calculators online that give you estimates, but not to the penny amounts.

Want to Factor in Tax-Deferred Accounts?

If you have a $500,000 401(k) balance and expect to withdraw about $30,000 per year from this account, you would add $4,500 to the “Expenses in Retirement” field (assuming a 15% tax rate). It can be extremely complicated to figure out the net amount you’ll receive from all your income sources. Every income stream is treated a bit differently based on the tax-status of the account or what type of income it is. The work around for this calculator is to simply add the taxes you expect to pay as an increase to the “Expenses in Retirement” field.

This work around is actually quite accurate. After all, your net income is simply your gross income plus a negative tax expense amount. I think tax-deferred accounts are great and most people should contribute to them if they’re in or above the 25% tax bracket. But you will eventually have to pay big government. This could could come back to bite you if your tax rate is higher in retirement. Having a higher tax rate in retirement is unlikely, however.

How Does The When Can I Retire Spreadsheet Work?

This spreadsheet works by first figuring out the lump sum you will need to retire based on your annual expenses in retirement, inflation, investment return, and number of years in retirement. This lump sum amount is calculated using the present value of an annuity formula. Say what? It basically figures out the numbers based on the time value of money using inflation and factors in your investment return as well. This calculation is used by retirement professionals and is more robust than simple tricks like the 4% rule. The limitations of the 4% rule are obvious:

  • It’s linear – Life doesn’t follow a linear expense pattern. You’re likely to have more expenses early in retirement due to lifestyle and more expenses later in retirement. Then at the end of your retirement, you may face substantially increased healthcare expenses.
  • Sequence-of-returns risk – What if you retire and the market crashes? Would you still want to withdraw four percent or would you keep the money invested so that it can bounce back? Most advisors worth their salt will have a good plan if the market tanks. That means that you may need to get income from other sources while the market comes back.
  • Low-interest rate risk – You’re living in a low-interest rate world. The 4% rule was developed in 1994 when interest rates were much higher. You could earn four percent on a savings account back then. I remember having my passbook account rain interest on me back then. Now, it’s like a desert. Putting your money in stocks and bonds is the more risky alternative.

The spreadsheet then takes into account everything that will get you to that lump sum amount. This would include your current portfolio amount, annual additions to principal, and the investment return you expect. Your investment return is added each year to your current portfolio amount and additional contributions. Changing the investment return can make a pretty significant change in the output. Make sure you’re using a realistic number. I like 8% and I think most people can afford this amount of risk, even in retirement. You may be able to achieve an even greater return with the right investment mix.

Limitations of the When Can I Retire Spreadsheet

The main limitation of the When Can I Retire spreadsheet is the fixed nature of the inputs. For example, it doesn’t allow for different annual principal additions by year. It also doesn’t allow you to change the investment return. I could adjust for this but then it would be utterly complex to use. I had to sacrifice some functionality for more simplicity. I hope you don’t mind.

This spreadsheet uses assumptions which may or may not be correct. The accuracy depends on the numbers you input (GIGO). Inflation is currently running at 2.7% according to the BLS’ CPI-U all items. The inflation number is increasing and could go much higher in the future. It has a chance of going down too (deflation). No one really knows what’s going to happen in the future. That’s why I got out of finance and into accounting. Finance is full of forecasts that may or may not come true.

How Do You Compare?

Americans statistically are really poor savers. The median amount saved for a 50-something is $117,000, much less than what’s needed during your 50’s if you want to be on track. I recommend having 25 x your expected annual retirement expense amount saved by the time you’re 59 1/2. That’s $1.25 million saved if you anticipate having $50,000 in annual expenses in retirement. I use 59 1/2 because that’s the magic number that the government says you can start withdrawing from your IRA or 401(k) penalty free. I know it sounds like a lot.

If you haven’t saved “enough” whatever that number is for you, you’re in good company. A look at the mean retirement account savings of families by age group shows what a meager amount has been saved. The mean can be skewed a bit but it still illustrates a good point: American families need to save more.

 

Average Saved by Age Group

Source: EPI

There are 4 things I recommend if you haven’t saved enough for retirement. These 4 things can have a profound impact on your financial picture in retirement, even doubling the income you receive during your retirement. In the end the question, when can I retire, is one big math problem. I think using this spreadsheet or another online calculator is a good first step in the right direction. Let me know what you think, as always. Have questions? Put your email in below and reply to my introductory email to ask a question.