0

I am writing a MonteCarlo simulation in VBA. This is the code:

 Sub Simular()

 Dim i As Integer
 Dim j As Integer
 Dim simulacion As Integer
 Dim iterar As Single
 Dim mu As Double
 Dim varianza As Double
 Dim aleat As Double
 Dim cantidad As Double
 Dim fecha As Date
 simulacion = Sheet8.Cells(2, 3)

 mu = Sheet8.Cells(7, 3)
 varianza = Sheet8.Cells(8, 3)
 cantidad = Sheet8.Cells(10, 3)
 fecha = WorksheetFunction.WorkDay(Sheet8.Cells(4, 2), cantidad)
 Sheet8.Cells(15, 4) = fecha
 Sheet8.Cells(1, 2) = cantidad
 Sheet8.Cells(3, 3) = Sheet8.Cells(1, 3)

 For j = 1 To 10000 'number of simulation
 iterar = Sheet8.Cells(15, 3)
 For i = 1 To 231 'workdays
 aleat = Rnd()
 iterar = iterar * Exp((mu - 1 / 2 * varianza) +      WorksheetFunction.Power(varianza, 0.5) * WorksheetFunction.Power(1, 0.5) *     WorksheetFunction.Norm_Inv(aleat, 0, 1))
 'Sheet8.Cells(10030 + j, 12 + i) = aleat
 'Sheet8.Cells(17 + j, 12 + i) = iterar
 Next i
 Sheet8.Cells(17 + j, 4) = iterar
 Next j
 End Sub

The problem is that is always repeating 3 different simulation. For example, when I put mu= 1/252 (interest rate), the first number of the first simulation is 5,570, when I run the simulation again, the first number of those simulation is 9,610 and the third time is 72,23. The fourth time is 5,570, so its repeating. I don't know why, because is rnd() numbers, all must be different.

Besides, in the third simulation, appear a message saying unable to get the "normsinv property of the worksheetfunction class"

I have tried a lot of things, but I didn't find the mistake!

Thank for your help! :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mschapiro
  • 25
  • 7
  • 1
    Duplicate. Check [this](http://stackoverflow.com/questions/2884972/repeating-random-variables-in-vba) out. – David Pullar Feb 13 '15 at 19:26
  • Its just the opposite. I dont want the same random numers! i want always new random numbers, but i dont find my mistake in the code! Thanks anyway! – mschapiro Feb 13 '15 at 19:34

1 Answers1

0

Have you tried:

Application.Worksheetfunction.NormInv(aleat,0,1)

Or, if you are always looking for the inverse normal distribution with a mean of 0 and std dev of 1 you could use:

Application.Worksheetfunction.NormsInv(aleat)

As for your issue with rnd(), have you tried using rnd() directly in your normsinv function? Or, in the line before you set aleat = rnd(), try:

Randomize

This will reset the seed number of the function rnd().

Kyle
  • 2,543
  • 2
  • 16
  • 31