Monte Carlo Simulation in Excel

Monte Carlo simulations are used in a diverse range of applications, such as the assessment of traffic flow on highways, the development of models for the evolution of stars, and attempts to predict risk factors in the stock market. The scheme also finds applications in integrated circuit design, quantum mechanics and communications engineering.  The analysis is based on the use of random numbers and probability statistics to investigate the likelihood of particular outcomes. 

 

“Monte Carlo simulation is named after the city in Monaco, where the primary attractions are casinos that have games of chance. Gambling games, like roulette, dice, and slot machines, exhibit random behaviour.”  From;

 

 http://www.investopedia.com

 

I saw an interesting post from an Investment Banker and decided to follow the project. I used to work in London's Banking sector and have always taken a keen interest in the field.  I got involved and came up with the following simple Monte Carlo Simulation which should be easy to follow for anyone who is interested in the area.

Monte carlo Simulation Excel

 The above diagram is a snapshot of the output with the results appearing at the far right.  The input criteria is on the left and the VBA code which sits behind the model is as follows;

Option Explicit
Sub MontSim() 'Excel VBA procedure to Run Montecarlo Simulation.
Dim rg As Range
Dim r As Range
Dim iAs Long
Dim n As Long
Dim jAs Long
Dim ar As Variant
Dim BaseLn As New Collection
Set rg= [G13:G53]
Application.ScreenUpdating=False
rng.Sort [D10], xlDescending, [C10], , xlDescending, [B10], xlAscending

End Sub
n=[B19].Value
ReDim ar(1 To n)
For j=1 To [B19].Value
For Each r In rg.Cells
If r.Value < [f13].Value Then
BaseLn.Add r.Value
End If
Next r
i=BaseLn.Count
ar(j)=i
Set BaseLn=Nothing
Calculate
Next j
Range(Cells(13, 8), Cells(UBound(ar) + 12, 8))=Application.Transpose(ar)
Application.ScreenUpdating=True
End Sub

You can follow the thread to its completion here;

 

Ozgrid Forum Post

 

The project ended well with over 1 million simulations and a graphical display of what the distribution looked like.  I have updated the code slightly since the post to help it run more quickly.

 

Monte Carlo Simulation Excel