Monte Carlo Simulation in VBA consistently underestimates the true value

Question

I have a weird problem with a Monte Carlo Simulation that I built. It is a nested loop to calculate the expected value of investments (actually Poker Tournaments). To demonstrate, assume that we are talking about heads-up Poker Tournaments, which is equal to a coin flip. Assume that we have a 25% ROI per coin-flip and the buy-in is one, so the EV after 100 (500, 1000) coin-flips is 25 (125, 250) units. The simulation, however, returns 24,6, 123,6 and 246, respectively. The critical line in the code is here:

Randomize
randomnumber = Rnd()
If randomnumber > adjustedITM Then
MC_array(m, n) = -tournamentvariables(k, 6)
Else:
Randomize
MC_array(m, n) = CDec(tournamentstructures(Int(Rnd() * (tournamentvariables(k, 7)) + 1), k) * tournamentvariables(k, 6) * (1 - tournamentvariables(k, 5)) * tournamentvariables(k, 2) - tournamentvariables(k, 6))
End If

The 2nd MC_array(m, n) is the critical line of code. It gives the net profit if the player wins. In case of a coin flip this is one unit. If I change the second line to

 Randomize
    If Rnd() > adjustedITM Then
    MC_array(m, n) = -tournamentvariables(k, 6)
    Else:
    Randomize
    MC_array(m, n) = 1
    End If

The results are correct. The code after the 2nd MCarray simpliefies for the coin-flip to:

CDec(tournamentstructures(Int(Rnd() * (tournamentvariables(k, 7)) + 1), k) * tournamentvariables(k, 6) * (1 - tournamentvariables(k, 5)) * tournamentvariables(k, 2) - tournamentvariables(k, 6))

=
CDec(tournamentstructures(1,1) * 1 * (1 - 0%) * 2 - 1)

So it is exactly the same as one. The array tournamentstructures() has the size (1,1), so it can't read anything in. I verified that all results are integers (as for a coin flip you can only win or lose a unit), I strongly suspect that the random number generator is somehow biased.

I declared pretty much everything in the code as variant, and excluded the second Randomize without it changing the bias. So guys, what is going here?

John Coleman · Accepted Answer · 2016-12-09T12:58:09.693

It looks like you are repeatedly calling Randomize, presumably as part of a tight loop. Every time this is called, it reseeds the random number generator from the system clock. Doing so with each pass through the loop introduces autocorrelations (though exactly how isn't quite clear).

Consider the following experiment:

Sub Test()
    Dim i As Long, A As Variant
    Dim count1 As Long, count2 As Long
    ReDim A(1 To 10000)

    For i = 1 To 10000
        Randomize
        A(i) = IIf(Rnd() < 0.5, 0, 1)
    Next i

    'count how often A(i) = A(i+1)
    For i = 1 To 9999
        If A(i) = A(i + 1) Then count1 = count1 + 1
    Next i

    For i = 1 To 10000
        A(i) = IIf(Rnd() < 0.5, 0, 1)
    Next i

    'count how often A(i) = A(i+1)
    For i = 1 To 9999
        If A(i) = A(i + 1) Then count2 = count2 + 1
    Next i

   Debug.Print "First Loop: " & count1
   Debug.Print "Second Loop: " & count2 & vbCrLf

End Sub

Typical output:

First Loop: 5452
Second Loop: 4996

I have run it several times. The first loop almost always produces a number which differs from 5000 by a large amount whereas the second loop almost always produces a number fairly close to 5000 (the expected value is 4999.5 if successive calls to Rnd correspond to independent random variables -- hence there is a distinct lack of independence when repeatedly reseeding).

Moral of the story: call Randomize just once in the simulation, say as the first line of the main sub. Alternatively, use Application.WorksheetFunction.RandBetween(0,1) and let Excel worry about the seeding.

If this autocorrelation doesn't explain the error, the problem lies in the code that you haven't shown, so you would need to include that code.

Monte Carlo Simulation in VBA consistently underestimates the true value

1 Answers1

Linked