Excel Monte Carlo simulation

Monte Carlo simulation model parts

A Monte Carlo simulation model consists of the following parts:

• the distribution parameters of random inputs
• the calculation model that generates the outcomes of a single run
• the database, a table that contains al values of inputs/outputs of multiple runs (e.g. 10,000)
• an interface between calculation model and database - a single row of formulas that link to each individual input and output variable, and which has the same layout as the database table
• the statistical analysis of inputs/outputs - also to check the calculation model - with metrics such as average/median/min/max, correlations, probability distribution histograms, and regression scatter charts

It also contains short MS Excel VBA code to run the calculation model multiple times and store inputs/outputs of each run in the database (see MS Excel VBA code at the end of page).

Preview

The example has been kept very small on purpose. The focus is to show what the structure of such a model may look like and how little MS Excel VBA coding is required, not to show how to create complex models. This example uses uniform distributions. But you can also generate random variables of other distributions (such as a triangular or a normal distribution): see example 14 of the MS Excel function reference file.

Short MS Excel VBA code

Monte Carlo VBA codePrivate Sub cmdMonteCarlo_Click() Dim R1 As Range, R2 As Range, R3 As Range Dim iRun As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set R1 = Range("calculationmodel") Set R2 = Range("interface") Set R3 = Range("database") For iRun = 1 To 10000 R1.Calculate R2.Calculate R3.Offset(iRun, 0).Value = R2.Value Next iRun Calculate 'update statistical analysis Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox "Done", vbOKOnly + vbInformation, "Monte Carlo simulation" End Sub

Of course, you can make the run length (10,000) a user input variable. In that case, do not forget to add code that clears the database first before starting the simulation.