vba - How good is the RAND() function in Excel for Monte Carlo simulation?

Question

I'm implementing a Monte Carlo simulation in 3 variables in Excel. I've used the RAND() function to sample from Weibull distributions (with long tails). The functions applied to the samples are non-linear but smooth (exp, ln, cos, etc). The result for each sample is a pass/fail, and the overall result is a probability of failure.

I have also implemented this by both numerical integration and Monte Carlo in MathCad, getting the same result both times. MathCad uses (I think) a Mersenne Twister random number generator.

My excel spreadsheet is getting consistently different results (ie always larger). I have checked the equations are the same.

What random number generator does Excel use, and how good is it? Is it possible that this is the source of my problem? I have assumed the Excel implementations of exp, cos etc are ok.

Finally, is there a way to implement Monte Carlo to mitigate against the (known) poor properties of a particular random number generator? (I've heard of Markov chains, random walks etc, but don't really know much about them)

Many thanks.

Answer 1

Since this is the top result in Google for "how good is Excel's RAND() function" it is worth updating the answers for later versions of Excel

This paper by Guy Melard "On the accuracy of statistical procedures in Microsoft Excel 2010" tested the RAND() function in Excel 2010 and found it to be substantially improved over 2007 or 2003. Microsoft switched from an incorrect Wichmann and Hill generator (2007/2003) to the Mersenne Twister algorithm which has a much, much greater cycle length.

The authors of that paper ran it through "Small Crush", "Crush" and "Big Crush" tests for randomness and it passed nearly all of the tests.

So while it certainly isn't the same as True random numbers, the RAND() function in Excel 2010, and presumably newer versions, can no longer be considered terrible.

It should be noted however, that Excel 2010 still uses two completely different algorithms for the VBA random number generator, and the RNG that is in the data analysis tool-kit. According to Melard, both of those are still terrible, and in fact the VBA uses the same seed number each each time so produces the same numbers.

My biggest complaints with the random numbers in Excel are

• You can't set the seed, so the numbers are not reproducible
• The random numbers update every time you press enter/delete, and even if you set calculation options to Manual, they still update when you save the Excel file