If 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.