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;
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.
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 Code which sits behind the model is as follows;
Sub MontSim() 'Excel VBA monte carlo simulation
Dim Rg As Range
Dim r As Range
Dim BaseLn As New Collection
Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant
Set Rg= [G13:G53]
ReDim ar(1 To n)
For j=1 To [B18].Value 'The Value in B8 is how many Sims to Run
For Each r In Rg.Cells 'Loop through a rng and test condition
If r.Value < [f13].Value Then
Range(Cells(13, 8), Cells(UBound(ar) + 12, 8))=Application.Transpose(ar)
You can follow the thread to its completion here;
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.