Mersenne Twister Algorithm Background

The Mersenne Twister algorithm is a pseudorandom number generator developed by Makoto Matsumoto and Takuji Nishimura in 1997. The Mersenne Twister algorithm ensures fast generation of high-quality pseudorandom integers that pass numerous statistical randomness tests. The Mersenne Twister algorithm is utilized by many other well known software languages including but not limited to Python, PHP, Ruby, Lisp, Pascal, MATLAB, and C++. The Monte Carlo Excel Addin makes the Mersenne Twister pseudorandom number generator available to your Excel VBA Macros and workbooks.  In addition to generating random numbers representative of a uniform distribution, the Monte Carlo Excel Addin  generates random numbers whose distribution follow other statistical functions (Normal, Exponential, Weibull, etc)

Mersenne Twister Algorithm Advantage

The benefit to the Mersenne Twister method is that it has an extremely long period as compared other random number generators. The native Excel VBA random number generator function, RND(), has a period of 16,777,216 numbers, or 2²⁴. After 16,777,216 pseudorandom numbers are generated, the identical series of numbers repeats. The Mersenne Twister method has a much longer period of 2¹⁹⁹³⁷ − 1, which is nearly infinite by today’s computing standards. The Mersenne Twister method also generates random numbers with much finer granularity than other random number generators. The Excel VBA RND() function generates 2²⁴ unique floating point values between 0 and 1 while the Mersenne Twister method generates 2³² – 1 unique integer values.

Demonstrating Excel’s VBA RND() Function’s Limited Period

The following VBA code records the first 100 values generated by the native Excel random number generator function, RND(), alongside the first 100 values generated as the series repeats on trials 16,777,217 through 16,777,316. Through inspection of the output written to Sheet1 it is apparent that the RND() series repeats after 16,777,216 pseudorandom numbers are generated.

Sub Demonstrate_Limited_Period_of_RND_Function()

'Disable screen updating and calculation, for speed
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'define the end of the period
endval = 16777216

For i = 1 To endval * 2

'generate random number
NumValue = Rnd()

'Record first 100 random numbers in worksheet
If i < 101 Then Sheet1.Cells(i, 1).Value = NumValue 

'Record the first 100 random numbers of the next period as the series repeats 
If i > endval And i < endval + 101 Then Sheet1.Cells(i - endval, 2).Value = NumValue

Next

'Enable screen updating and calculation
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub