If you’re not sure what Monte Carlo is, please checkout our explanation in part one. Otherwise, please proceed after downloading the simple Excel template this article explains.

This model is intended more as a vehicle for understanding the ins and outs of how it works and less as a ready made retirement calculator. Please download our most advanced retirement simulator if you’re after an immediate solution.

The idea for this model is to get a better feel for the uncertainty involved in a simple retirement scenario. How much money will we have in X years?

We start with the following:

• initial investment
• a number of years to invest
• a range of monthly investment for those years
• a range of potential investment returns

Do not to use this model for any time horizon under 10 years as there is so much potential volatility in the stock market that it will be wildly inaccurate.

A quick note on Google Sheets. I love Sheets for simple spreadsheets and it obviously shines where you need to collaborate or share. You may then wonder why I don’t provide this template or the others in a Sheets format and the simple answer is that Sheets simply isn’t built to handle the complexity of even modest Monte Carlo models. The experience of even trying is very frustrating and I don’t recommend it.

Excel has a function FV (future value) that allows us to calculate the compounding of a certain amount of money with or without additional contributions over a number of periods like years.

If we had $1000 invested at 5% annual return for 25 years we could use this function to get a quick answer.

=FV(rate of return, periods (years), ongoing investment, - initial investment)

=FV(0.05, 25, 0, -1000)

is $3,386.35

We could equally easily get an answer for $1000 invested at 5% annual return for 25 years where we add $1000 in investment each year.

=FV(0.05, 25, -1000, -1000)

is $51,113.45

The model uses this function as a shortcut to calculate the return on an investment portfolio extensively.

The first sheet you see in the template, “Model assumptions,” shows a place for you to select an initial investment, a number of years to retirement, a monthly investment range, and a rate of return range. Below those is a discrete (median case) calculation of what might happen with those assumptions. This helps you see the math that the model uses in the Monte Carlo simulation, gives you a chance to check that your assumptions are reasonable, and a place to see how to use the FV function.

The actual Monte Carlo simulation is built on the second sheet, “Model results,” and the top shows the results, the middle section shows a copy of your assumptions from the first sheet, and the bottom section is the series of 10,000 scenarios with the resulting calculations.

I’ll explain the bottom section first. Most columns here use an Excel function called NORM.INV to calculate a normally distributed value from a random number and the range that we specified in the first sheet. The random number is used so we inject randomness and uncertainty into the model. NORM.INV is a way to pick a number from a normal distribution. The specifics of how it works are beyond the scope of discussion here, but for a complete treatment I’d recommend Douglas Hubbard’s How To Measure Anything.

We have a column for each value we need to put into our equation for portfolio value. Only the initial investment column is a static number. With a selected number from our potential ranges in the assumptions we can use FV under Portfolio Value to calculate a specific number for the portfolio given the randomized outcome for each of the values we’re uncertain about.

Doing that 10,000 times gives us a clear idea of how likely a given scenario might be. The results section here is oriented around the chance that we meet our “target value” for the portfolio which is defaulted to $2.5MM. The results section breaks down the chance we meet that value or exceed it. It also shows the collective chance that we meet or exceed 90% of the target and on down in 10% increments.

We can develop a clear view of these probabilities because of the modeling we have done and the raw number of scenarios we have randomly computed. These sample equations, formulas, and methods can be used to model nearly any scenario or decision along a huge spectrum of complexity.

We’re going to take a next step in improving how this model looks at our goal in terms of monthly income, which is more digestible than a total dollar value target. We’ll also add a sheet to two to help us analyze what the new variables mean and do in terms of the future projections.

Our next step takes us very near to the simulation that Personal Capital will run for you on their website, but it’s a dead simple Excel sheet you can tweak to your heart’s delight. I find the Personal Capital simulation limited in a few troubling ways that limit how useful it is.