Once the simulation is complete, the average value can be calculated from this set of stored values. The Monte Carlo simulation runs hundreds or thousands of times, and at each iteration the RiskAMP Add-in stores and remembers the value of cell F11. This results in a different value in cell F11. When you run a Monte Carlo simulation, at each iteration new random values are placed in column D and the spreadsheet is recalculated. In this example, cell H11 calculates the average value of cell F11 over all the trials, or iterations, of the Monte Carlo simulation. In Figure C, we’ve added average simulation results in column H using the function seen in the function bar. Once you run a simulation, this error will go away. This is because the simulation hasn't collected data for the cell yet. Note: the first time you enter these functions in a spreadsheet, you'll see an #N/A error. To start, we’ll look at the average results of the simulation using the SimulationMean function. The RiskAMP Add-in includes a number of functions to analyze the results of a Monte Carlo simulation.
(In Excel, use the “Run Simulation” button on the Monte Carlo toolbar).
To run a Monte Carlo simulation, click the “Play” button next to the spreadsheet. Step 2: Running a Monte Carlo SimulationĪ Monte Carlo simulation calculates the same model many many times, and tries to generate useful information from the results. The key to using Monte Carlo simulation is to take many random values, recalculating the model each time, and then analyze the results. Randomly-distributed returns seem like a better approximation of the real world, but taking a single random return isn’t useful. The total return ( F11) can also differ significantly from the original value (30.08%). If you recalculate the model at this step, you will see each return change.
The returns in each period are randomly generated. In figure B, the return in each period has been changed from a fixed 5.4% to a randomly distributed return, using the function seen in the function bar.