# An advanced retirement Monte Carlo model Excel template

BY Robert GrahamIf you’re looking for an explanation of Monte Carlo please take a look at our breakdown of the method before this piece. If you want a very simple Excel model, start with our bare bones version to improve your understanding.

If you’re here for a strong Monte Carlo simulation tool for retirement spending then you’re in the right place. Download the template and follow along with the information in this article. First I’ll explain how to use the Excel template, then how it works relative to the simpler model, and finally a few things you might want to change or consider.

Before we get going, I will mention again that you shouldn’t look at anything less than a 10 year time horizon with these simulations. It will be wildly inaccurate. For an explanation, see our method breakdown which will explain why this is the case.

For the basis of this model take a look at the prior post on the simple model. The only thing we add to the advanced model’s “Model assumptions” sheet is a “Desired annual spend” range. You pick an annual number and the model will use that number to project how often you should expect to be successful given the set of assumptions you provide.

This Excel template adds a new sheet “Detailed spending in retirement” that ideally will help you understand the Monte Carlo model we’re using and make the whole package more useful. This sheet shows what you expect to have saved at the time (X years from now) when you expect to retire. We take the median rate of return from the assumptions sheet and use our friend the FV function to project how much money we will have remaining from our nest egg given our planned annual spending and a median rate of return.

If your “Available capital” number is increasing, you would expect to be in great shape. You’re withdrawing less each year than you would expect to accumulate in interest. If your rate of return assumptions are modest and your annual spending number is reasonably accurate this table will really help you get some idea of what happens to the money in different scenarios and why some assumptions play out very badly in the Monte Carlo scenario. Sometimes getting a probability for your answer can be unsatisfying and this table can help contextualize that result. This gives you a feel for what a 34% or an 84% chance of success might look like.

On the “Model results” sheet we discard our past top section about meeting a certain target amount of cash. The replacement is a singular percentage chance that we meet our target annual spending goal. 84% chance of success means that 84% of our 10,000 scenarios leave us with the capital required to spend at the rate we would like.

This statement itself is a little tricky, so I’ll break down what we mean by it specifically. We use the same randomized NORM.INV function to select values for each variable with randomness but a selected normally distributed shape. We use the FV function to find the final value of such a portfolio given the number of months we invest. If our final portfolio value accrues more in one additional year of compounding than we plan to withdrawal, we count this scenario as a success.

There are two big things to keep in mind with this criteria for success. One, we are keeping the singular rate of return assumption from the scenario and only calculating a single year’s compounding to make our success/failure determination. This calculation could actually be its own complete Monte Carlo simulation if we wanted to increase our fidelity and complexity. I chose to keep it simple. The randomized rate of return for a scenario is also what we use to determine success in a single tacked on year. This could be a bad assumption if you plan to stay retired an extremely long time or want to consider transitioning your funds toward lower risk, lower reward investments at the end of your time horizon.

The other key concept here is that we are only successful if the model and scenario’s specific final randomized assumptions are able to support your desired spending in perpetuity with growing underlying capital. If you somehow knew you only needed this money to bridge you to help in the form of a large pension for a few years and then your spending would change or your retirement will be a short affair, you could relax this assumption significantly.

Chris says…

28 June 2018 at 11:52

Hello. Interesting model. Does the model allow for inflation (specifically YOY inflation adjusted retirement spend). My apologies if I missed it. Thank you. Chris.

robert says…

4 July 2018 at 1:52

The model doesn’t explicitly handle inflation for your target spend, but you can use the same function(s) to calculate the right value for your annual spend based on your assumptions about inflation. Here’s an example for 2.5% inflation with 30 years until retirement and a desired annual spend of $65,000 in retirement.

`=FV(rate of inflation, years to retirement, ongoing change (zero), annual spend in retirement)`

=FV(0.025, 30, 0, -65000)

This results in a target spend of $136,341.89 for $65,000 annually with 2.5% inflation over a thirty year term. Use the equivalent figure that fits your retirement scenario in this model for the ‘target annual spend’ and the model will accurately account for inflation.

Hope that helps!

youronlinechoices.com says…

19 January 2019 at 1:03

We’re a group of volunteers and starting a new scheme in our community.

Your site offered us with valuable info to work on. You’ve done an impressive job and our whole community will be thankful

to you.

Brian says…

16 January 2020 at 7:50

I’m also interested in community based financial literacy and would be interested in what your group is doing.

Jack says…

2 December 2019 at 2:15

Hi, what is the logic behind using Standard Deviation = (upper band – lower band) / 3.29 ?

Thanks

robert says…

2 December 2019 at 7:59

That means that the distribution we’re sampling from represents a 90% confidence interval.

In order to use the sheets most effectively, you should choose a lower bound and an upper bound within which you believe is the right answer with 90% confidence. There are times that you might want to specify a specific Standard Deviation to represent a specific distribution and it’s not too difficult to do, but this is often an easier interface that represents our uncertainty about the future more clearly.

nonof says…

10 January 2020 at 9:31

Is your success criteria assuming that the fund needs to grow in retirement to reach 100%? Seems a bit conservative. Doesn’t the fund just need to stay above yearly withdrawal rate for the years in retirement, say 30, or 40 to be safe?

Robert says…

4 February 2020 at 7:25

It is more of a legacy model or a “4% safe withdrawal” model with the assumption you stated.

The retirement term depends on the individual,, but it’s not too difficult to change the sheet to support assumptions like you’re talking about.

If you’re interested I’m happy to share an example sheet.