Retirement Date Forecasting Tool

May 2nd, 2018 | Posted in Retirement, Tools

Get the retirement date forecasting tool as an excel file (recommended) or as a Google Sheets file. For Google Sheets, you need to log in to your google account, and then click File –> Make a Copy

Retirement? Yes, Retirement…

For most of us, golden pensions are a thing of the past. Without a guaranteed monthly check to rely on, funding your retirement requires strong planning, and even stronger execution.

In this post, I’ll walk you through the Retirement Date Forecasting Tool that I’ve built. We’ll see how it can help you understand your current trajectory towards retirement, and set new targets to get where you want to go.

Our retirement planning should begin by getting a grasp on a few key facts:

• How much money do I need to retire?
• How close am I to retirement now? How long will it take to get there based on my current spending and saving habits?
• How much will I need to save, and what returns will I need on my investments in order to retire when I’d like to?

With this knowledge in hand, we can then set long-term targets that will help us reach our retirement goals.

To get started, please download a copy of the Retirement Date Forecasting tool. I highly recommend using the Excel version if you have access to Excel, given that it includes a scenario analysis feature which I’ve yet to be able to replicate in Google Sheets, and because it lets you switch between charts to view at the press a button. If you don’t have excel, the Google Sheets version has 95% of the same functionality and is more than serviceable.

See the Excel version in action below. Some good fun to be had in pressing the buttons and watching the charts fly by…

How to Use the Retirement Date Forecasting Tool

First things first, navigate to the “Retirement Forecasting Tool” tab, and enter in your own assumptions. To get a handle on how the tool works, let’s take the example of Mike – a 24 year old who has been working for a couple of years, and wants to figure out how he can set himself up in a solid position for his eventual retirement.

Mike has already managed to save up $5,000, and will be able to save an additional $12,000 per year, with investment returns of 5% per year (real returns after inflation).

In retirement, Mike expects to have expenses of $25,000 per year, and would like to retire at age 60 using a withdrawal rate of 3.5%.

After you’ve inputted your assumptions, everything else is automatically calculated for you.

What Assumptions Should I Use?

Investment returns

The US stock market has delivered a real return of 6.9% per year from 1871 to 2017. Using a return assumption of 4% to 7% would be reasonable.

Expenses in retirement

Input your best guess for your annual expenses in retirement. Starting to track your expenses today would be a good step if you aren’t sure of what your current spending habits are.

If you expect to have a government benefit payment when you retire (e.g., social security, old age security), you can subtract this amount from your expenses. Please note that the earlier you retire, the lower your benefit payment would be. As such, I would stay on the safe side and not include this benefit unless you are sure you will receive it.

Withdrawal rate

The withdrawal rate represents the maximum percentage of your portfolio that can be spent in a year, without running out of money in retirement. Values of 3% to 4% are typically considered ‘safe’, and have worked in the vast majority of historical time periods.

The higher the withdrawal rate you choose, the faster you will be able to retire. However, a higher withdrawal rate also increases the risk that you run out of money in retirement. The withdrawal rate that is ‘safe’ for your situation is dependent on the investment returns you will be able to achieve, and the length of your retirement.

An illustrative example of the relationship between the “withdrawal rate” assumption and the resulting amount required for you to retire:

• If you plan to spend $30,000 per year in retirement, and assume a 4% withdrawal rate, you will need a portfolio valued at $750,000 in order to retire ($750,000 x 4% = $30,000)
• However if you assume a 3% withdrawal rate, you’d need a portfolio of $1,000,000 in order to fund the same $30,000 of annual spending in retirement ($1,000,000 x 3% = $30,000)
• Put another way, a lower withdrawal rate means you need more money before you can retire, and therefore the age at which you can retire is higher

The short answer is that you should use a withdrawal rate between 3% and 4% and test out how the answers change if you vary that number. If you want to really dig into the details of withdrawal rates, please see this fantastic series of posts by Early Retirement Now.

Results of Your Retirement Scenario

Mike is on track! Based on the assumptions he has entered, he’ll be able to retire in 28 years (at age 52), a healthy 8 years before his target age of 60.

Mike will need a portfolio of ~$714,000 to retire.

Note that all dollar figures in this post are measured in today’s dollars – i.e., they are adjusted for inflation.

If Mike chooses to continue working and saving until his target age of 60, he would have a portfolio of ~$1.2 million, which would provide a good buffer over the amount actually needed to fund his retirement.

After reading through these results, move to the “Outputs and Charts” section.  In the excel version, you can click on the menu of buttons to toggle between viewing the various charts, as shown in the animation above. In the Google Sheets version, you will need to scroll through the charts one by one.

Now, we’ll walk through each of the charts included in the tool, and explain how these answers are derived.

Please note that this tool is meant to calculate how much money you need to retire / when you’d get there. This tool does not model out the “withdrawal phase” after you are retired and stop earning income.

Chart 1: # of Years to Retirement

As seen in the Retirement Results section, Mike needs a portfolio of ~$714,000 to retire. A portfolio of ~$714,000 will provide $25,000 of income at a 3.5% withdrawal rate, thereby fully funding Mike’s expenses in retirement.

Based on Mike’s savings during his working years, and the investment returns being earned on the portfolio, Mike will reach the amount required for retirement when he is 52 years old, when his portfolio grows to be ~$738,000.

If Mike does not retire at 52, and chooses to continue working and saving until his target retirement age of 60, he will have a portfolio of ~$1.2 million.

Chart 2: Passive Income and % Progress to Retirement

As the value of Mike’s portfolio increases over time, the amount of ‘passive income’ generated by his portfolio increases as well.

Passive income is calculated as the total portfolio value multiplied by the withdrawal rate. This represents the amount that could be withdrawn from the portfolio each year without running out of money in retirement.

Note that a higher withdrawal rate would allow Mike to generate higher income, but would increase the risk that Mike runs out of money in retirement. As mentioned above, a withdrawal rate of 3% to 4% has been safe in the majority of time periods.

When Mike is 52, his portfolio will generate ~$26,000 of passive income per year, which is enough to cover his expenses in retirement of $25,000. As such, Mike could retire at this point, since his expenses would be funded by the income generated by his portfolio.

Again, if Mike chooses to work and continue to save until 60, he will build up an even greater cushion, with his passive income of ~$42,000 per year being 169% of the amount needed to retire.

Chart 3: # of Years of Retirement Spending Saved

As of today, Mike’s portfolio value of $5,000 only represents 0.2 years of annual spending in retirement.

However, by age 38 Mike will have saved up 10 years of expenses. By age 52, Mike will have saved up 29.5 years of retirement expenses, which is enough to retire on. Note that Mike’s portfolio will actually last longer than 29.5 years in retirement, given that his portfolio will still be generating investment returns above inflation, meaning that the portfolio would likely stay flat or even increase in value after Mike retires.

Mike needs to reach a target of 28.6 years of expenses saved in order to retire, which is calculated as the inverse of the withdrawal rate (1 divided by 3.5% equals 28.6).

While the total dollar value of your portfolio can sometimes be a bit of an abstract concept, thinking about it in terms of ‘years of expenses saved’ is a much more tangible way of looking at it.

Chart 4: Breakeven Analysis

We’ve seen in a few different ways that Mike would be able to retire by the age of 52. However, life is not a spreadsheet (a positive or a negative depending on how you look at it…). This analysis is based on assumptions spanning multiple decades into the future, so we need to take them with a grain of salt.

What if Mike’s assumptions for savings, investment returns, or retirement expenses don’t turn out like he thought they would? What would need to happen for Mike to retire at 60 (his target age)? The breakeven analysis chart has those answers.

If Mike’s annual savings are only $7,151 per year (instead of $12,000), he will be retirement-ready at age 60 (instead of 52). This assumes that all of the other assumptions stay the same as before (5% investment returns, $25,000 annual retirement expenses, and 3.5% withdrawal rate).

Another way for Mike to be retirement-ready at 60 (instead of 52) would be if his investments only return 2.6% per year (instead of 5.0%). Likewise for his retirement expense assumption ($41,265 instead of $25,000) and his withdrawal rate assumption (2.12% instead of 3.5%).

This chart shows you what assumptions you need to ‘beat’ to retire before your target retirement age. If you don’t believe that you can beat these assumptions, you will need to re-assess your plan to see how you can increase your savings, and/or delay your retirement date.

Chart 5: Scenario Analysis

Note: unfortunately I haven’t been able to figure out how to add this into the Google Sheets version yet, so this analysis is only found in the excel version for now.

In the scenario analysis chart, we continue to stress-test our assumptions. Here, we can see how the final answers change based on slight tweaks to the assumptions.

Mike assumed he would save $12,000 per year; what if that number is slightly higher or lower? In this case, Mike would be able to retire at 49 if he saves $14,400 per year (an increase of 20%), or he could retire at 55 if he saves $9,600 per year (a decrease of 20%). This is relative to the baseline result of retirement at 52, if Mike saves $12,000 per year.

The sensitivity +/- range for this analysis can be adjusted on the “Scenario Analysis Assumptions” tab (only in excel for now).

Chart 6: Breakdown of Investment Portfolio – Contributions vs Returns

This chart shows how the value of Mike’s total portfolio is split between money that he saved directly, and money made from investment returns. Mike’s portfolio at age 52 would be worth ~$738,000, with 46% of the total coming from his contributions, and the remaining 54% coming from investment returns. Cheers to compound interest!

Chart 7: Coast Retirement Analysis

The ‘coast retirement’ concept involves choosing to shift into a lower paying job which just meets your expenses, and letting your portfolio grow until you reach your retirement amount without contributing any additional savings.

On one hand, your retirement date would be delayed since you earn less money and stop contributing to your portfolio. On the other hand, this can enable you to pursue a career that you are passionate about (writing, freelance work, a non-profit?) while still managing to retire in a reasonable time frame. Instead of racing to the finish line at your current job, you can slow it down, move into a career which is more fulfilling, and ‘coast’ to retirement.

Let’s say that Mike doesn’t love his job, and doesn’t want to continue working there all the way up until the age of 52, when he could retire. Instead of being cooped up in an office, Mike wants to turn his woodworking hobby into a small business.

The chart above shows that if Mike left his office job at age 38, he would still be able to retire at age 60, assuming that his woodworking business earns enough to meet his expenses (but not a dollar more), for a total of 22 years – from age 38 to 60. In doing this, Mike has traded 14 years of working at his old job (from age 38 to 52), in exchange for 22 years of working at his more fulfilling new job. In both cases, Mike manages to reach his retirement goal.

This math relies on the fact that Mike has already saved up ~$251,000 by the age of 38. If his portfolio compounds at a 5% real rate of return per year, he does not need to contribute any additional money for that amount to grow to ~$734,000 by age 60 (higher than his retirement-ready amount of ~$714,000).

You’ll also see from the chart above that if Mike decided to start ‘coasting’ at the age of 24, he would only be able to retire at the age of 126. Perhaps not the best choice.

While not for everyone, the coast concept is an interesting option to consider, especially for those who have already managed to save up a decent nest egg and feel that they may be burning out at their current job.

Choosing to coast can open up many attractive paths: moving to part-time work, taking a job which gives you more freedom over your time, or pursuing something you’ve always wanted to do but which doesn’t pay that well.

Final Thoughts

If you haven’t already done so, please download the tool (links to the excel and google sheets versions are at the top of this post) and give it a try with your own assumptions.

I encourage you to play around with different assumptions to see how your results change. Please keep in mind that these are only assumptions, and that any model which seeks to forecast decades into the future will always give a ballpark estimate at best. Using conservative assumptions and building in buffer into your scenarios would be wise.

I hope that this tool gives you a greater understanding of how on track you are with your own retirement, and helps you to build a plan for how you can get there. While the idea of retirement can seem like a hazy possibility far into the future, it can be achieved with a dose of planning and discipline. The best time to start was yesterday, but today works nearly as well!

If you have any questions, comments, or feedback, please let me know in the comments below or by email at themeasureofaplan@gmail.com.

—

Header image credit: Saturday Cartoons by Mark Stivers