Monte Carlo Simulation In Excel

Forward

A Monte Carlo simulation uses random sampling to estimate the probability of various outcomes.

I was inspired by Ryan O'Connel's video tutorial on how to build a Monte Carlo simulation for investment returns.

In the context of investment returns, our model has a single element of variability (standard deviation), that can lead to many different outcomes.

The goal here is to find out, for a given amount invested, the probabilities of ending up with various final amounts.

My Process

1: Inputs

The context here is an investment in the S&P 500. I went with historical data of a 9% mean rate of return, and 15% standard deviation. This is not meant to be the most accurate forecasting of future performance, as the focus here is just on building the model.

2: Method

Using the "NORM.DIST" function, together with the "RAND" function, we are essentially generating random values from a normal distribution.

The normal distribution in question is defined by our mean return (9%) and standard deviation (15%) stated above.

This entire table spans the 40-year investment time horizon, and represents 1 iteration of the simulation.

3: Extending Iterations

The "end value" cell in the picture here is the final value at the end of 40 years from step 2 earlier.

We then make a data table for 10,000 iterations, and use Excel's "What If Analysis" function to generate 10,000 possible outcomes.

The picture here shows just 6 rows of the 10,000 row-spanning data table.

4: Visualizing The Simulation

Main Graph

I used a histogram to represent the distribution of possible outcomes. To prevent the graph from being spread out too wide, I used an overflow bin for the high-end of return possibilities.

Pressing the "F9" key regenerates the whole simulation, and the graph changes accordingly to reflect a new distribution of outcomes.

Supporting Graphs

I used a box and whisker plot to summarize the interquartile range
I used a column chart to show a select group of percentiles (0.01%, 1%, 5%, 25%, 75%, 95%)

For instance, if the "1%" column has a value of $330,000, it means that there is conversely a 99% chance that the investment ends up with more than $330,000.

Page updated

Google Sites

Report abuse