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 224. After 16,777,216 pseudorandom numbers are generated, the identical series of numbers repeats. The Mersenne Twister method has a much longer period of 219937 − 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 224 unique floating point values between 0 and 1 while the Mersenne Twister method generates 232 – 1 unique integer values.
|Random Number Generator||Period||Possible Values|
|Mersenne Twister||219937 – 1||232 – 1|
|Excel VBA RND() Function||224||224|
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