Why doesn't NORMSINV(RAND()) in Excel work as a standard normal random number generator?

Question

I am looking for an easy way to generate random numbers from a standard normal distribution in Excel. I realize the best way is probably the Box–Muller method, SQRT(-2*LOG(RAND()))*COS(2*PI()*RAND()), and indeed, 10,000 of these looks like a good standard normal distribution. However, I can't figure out why a simpler function, NORMSINV(RAND()), would not work as well. The distribution looks much flatter. Can somebody explain this to me?

Answer 1

The problem not with NORMSINV, it is with your implementation of the Box-Muller transform. Excel's LOG function returns the base-$10$ logarithm, whereas what you need is the natural logarithm, LN.

Below is a histogram showing the Box-Muller transform using $\ln$ in blue, and the incorrect transform using $\log_{10}$ in red, with the expected curve from the standard normal distribution overlaid in dark blue.