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;

Option Explicit

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

Application.ScreenUpdating=False

Set Rg= [G13:G53]

n=[B18].Value

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

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