Monte Carlo simulation in VBA consistently underestimates true value

Question

I have a strange problem with the Monte Carlo simulation that I built. This is a nested loop to calculate the expected value of the investment (actually Poker Tournaments). To demonstrate, suppose we are talking about heads-up poker tournaments, which is equal to a coin. Suppose we have 25% ROI for a coin flip, and the buy-in is one, so the EV after 100 (500, 1000) coin transfers is 25 (125, 250) units. The simulation, however, returns 24.6, 123.6, and 246, respectively. The critical line in the code:

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 second MC_array (m, n) is the critical line of code. This gives a net profit if the player wins. In the event of a coin flip, this is one unit. If I changed 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. Code after the 2nd MCarray simplicity for transferring coins:

 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) 

Thus, he is exactly the same as one. An array of tournament objects () has a size of (1,1), so it cannot read anything. I checked that all results are integers (for example, for a coin flag you can win or lose one), I strongly suspect that the random number generator is somehow biased.

I proclaimed almost everything in the code as an option and excluded the second Randomize, without changing the offset. So guys, what's going on here?

Answer 1

It looks like you repeatedly call Randomize , presumably as part of a tight loop. Each time this is called, it resubmit the random number generator from the system clock. Performing this with each passage through the loop introduces autocorrelation (although for sure, as it is not entirely 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 ran it several times. The first cycle almost always produces a number that differs from 5000 by a large number, while the second cycle almost always produces a number quite close to 5000 (the expected value is 4999.5 if successive calls to Rnd correspond to independent random variables - hence there is a clear lack of independence when repeated).

The moral of the story: call Randomize only once in the simulation, say, like the first line of the main unit. Alternatively, use Application.WorksheetFunction.RandBetween(0,1) and let Excel worry about sowing.

If this autocorrelation does not explain the error, the problem is the code that you did not show, so you will need to include this code.