CODEWITHSUNDEEP
vbasimulationmontecarlo
user2371290
user2371290

Reputation: 327

Monte Carlo Simulation Method with VBA

I have a code which generates a Probability distribution for the total profit of price using the Monte Carlo Simulation Method.

How does the data in column F and G , display the cumulative distribution of profit ?

I understand that it will compute the cumulative frequency using a range of values which is given

given by Int(Iteration / 20 * i).

but I don't see how it is related to the Probability that Profit >= X in column F.

ie.

if I choose 100 for my iterations ,

then

TP(Int(Iteration / 20 * i))

= TP(Int(100 / 20 * i))

= TP(Int(5 * i))

and so it would only display,

TP(5), TP(10) , TP(15) and TP(20)

if i = 5

TP(Int(Iteration / 20 * i))

= TP(Int(100 / 20 * i))

= TP(Int(5 * 5))

and i get TP(25) which is out of the range.

this is the part of the code I'm confused about :

For i = 1 To 20
Cells(i + 3, 6) = 1 - (0.05 * i)
Cells(i + 3, 7) = TP(Int(Iteration / 20 * i))

Cells(i + 3, 14) = Iteration / 20 * i


Next i

http://www.anthony-vba.kefra.com/vba/vba12.htm

Upvotes: 1

Views: 1664

Answers (1)

user2480047
user2480047

Reputation:

From the code and the data you are providing, there shouldn't be any out of range:

ReDim TP(Iteration) As Double 'Is defined as function of the number of iterations (100 here)

Cells(i + 3, 6) = 1 - (0.05 * i) 'Writes to the F column until row 8 
Cells(i + 3, 7) = TP(Int(Iteration / 20 * i)) 'Writes to the G column until row 8

'The maximum value for  TP(Int(Iteration / 20 * i)) is 25, below 100 

'Actually, this array should be dimensioned in a more direct way like ReDim TP(Int(Iteration / 20 * i)) As Double

If you get an out of bounds error is because the TP array is not dimensioned as it should be: either because you missed the line above (ReDim TP(Iteration) As Double), or because you didn't assign the right value to the variable Iteration (= 100) before doing the aforementioned redimensioning.

Upvotes: 0

Related Questions