Monte Carlo Simulation Excel Model
Originally published: 05/12/2017 15:46
Publication number: ELQ-86400-1
View all versions & Certificate

Monte Carlo Simulation Excel Model

Monte Carlo Model generated in excel with explanatory article included to guide you block-by-block through the code.

The Monte Carlo method is taking a pile of estimates for your inputs and then seeing what the results come out like.

Nearly all of the inputs into models are just estimates, however there is an amount of uncertainty around the estimates. If we draw values from every one of the distributions many times we can work out the probable outcomes.

The important bit is that you should run the model multiple times with new draws for the variables. 'Multiple' in this case means up to millions of times.

To make things the least complicated possible, the model demonstrating these techniques is really straightforward. From a normal distribution, 2 numbers are drawn and these are our inputs. The output is their product. The rand () function is used to return a random number as the probability and it is fed into the inverse normal probability function so that a value can be generated.

Pressing the F9 button to generate a recalculation for the workbook (giving new random numbers) never seems to give a feel for how the numbers are generating output and interacting. The other option is to write VBA code so that we can draw lots of values and then overview the whole set of outcomes.

*Originally shared on*

This Best Practice includes
1 Downloadable Monte Carlo Model, 1 Code Explanation in PDF

Frank Gary offers you this Best Practice for free!

download for free

Add to bookmarks


4.8 / 5 (28 votes)

please wait...