How it works

How we run Monte Carlo simulations in spreadsheets

In this article we’ll show you a few spreadsheets with Monte Carlo simulations. In each example, you can run the simulation by pressing the “Play” button on the right-hand side of the spreadsheet.

The basic idea of running Monte Carlo simulations is that we recalculate the spreadsheet lots of times. To make this obvious, here’s a spreadsheet that just counts the number of times we run it:

When you run a simulation in this spreadsheet, you’ll see the counter goes up to 500 as the simulation runs. (The simulations on this page are intentionally slowed down to show you how they work).

Each time we recalculate the spreadsheet is called a trial or iteration. In this example, we’re running 500 trials.

In the second example, we add a function that samples from a random distribution. When you run a simulation, you’ll see that in each trial the value of the random cell changes:

The idea here is that the spreadsheet represents a calculation based on some random information. And in each trial, we use a different value for that random data.

So that’s how we generate sample data — we use random values in the spreadsheet, and then recalculate the spreadsheet again and again.

The last step is to capture the values so we can analyze them. This example adds a histogram that shows the distribution of the sample values. Watch how it changes when you run a simulation:

That’s how it works! Even the most complicated Monte Carlo model is built on the same three steps:

• add random data to the spreadsheet
• recalculate it hundreds or thousands of times; and
• analyze the captured values

Here’s a list of the RiskAMP spreadsheet functions we used in the above examples, linked to the help documentation:

SimulationTrials
This function shows the number of trials run in the simulation.

NormalValue
This function samples values from a Normal (Gaussian) distribution.

RiskAMP.HistogramTable
This function creates a histogram showing the distribution of values.