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 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

                 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;

 

http://www.ozgrid.com/forum/showthread.php?t=168719

 

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