Pseudorandom Number Generation in Excel

I’m resurrecting some of the old AO threads, and updating.

This thread was also known as “Why RAND() Sucks”

In July 2010, I had written an article for CompAct (newsletter of the Tech Section at the SOA): https://www.soa.org/globalassets/assets/library/newsletters/compact/2010/july/com-2010-iss36-campbell.pdf

Let me extract the main points: [and I’m editing my article - hey, it’s mine]

  1. RAND() is a volatile function.

Volatile functions are ones that recalculate every time Excel recalculates. For mundane volatile functions such as TODAY(), this is not such a big deal, as they rarely change their results upon
recalculation (and generally, you’re only calling them a few times within a spreadsheet–not a million times).

But every time Excel recalculates, you get a different number resulting from RAND().

This can be a big pain if you have a lot of calls to RAND() in your spreadsheet. Every single one of the cells that contains that function will recalculate.

And, even worse, every other cell formula that is dependent on that result will recalculate.

To be sure, there are a few ways to get around this problem. First, you can set your calculation mode to Manual, in which case the spreadsheet is recalculating only when you request it to.

Second, you can copy your “random number cells” and paste them as values over those very same cells. Of course, if you need more “random” numbers, you’re going to need to put the formulas back in.

  1. RAND() is not seeded.

What do I mean by a seeded PRNG (pseudorandom number generator)? I mean that you give the PRNG a specific number, a “seed,” which initializes the algorithm underlying the routine and will give you the exact same sequence of pseudo-random numbers in a particular order each time you use that particular seed.

This makes it easy to reproduce a particular Monte Carlo run without needing to store all the pseudorandom variates that go into producing your Monte Carlo estimate.

Why you need this:

a. Auditability–as Monte Carlo calculations are being used for regulatory purposes, an auditor will need to be able to follow your work from author inputs to final numbers. This will be especially important as many of these processes will be new, requiring more detailed investigation on the part of auditors.
Of course, many Monte Carlo estimates are purely for exploration or quick estimate purposes, and do not need to be audited.

b. Sensitivity Analysis/Analysis of Change–many times, even for exploratory purposes, you would like to determine the impact of model assumption changes. Say that you use a different lapse assumption for a variable annuity with a GMDB. If you don’t use the same PRN (pseudo-random number) sequences, how do you know if variance of results are from the new assumption or a new pseudorandom sequence?

c. Testing goodness-of-PRNG–it’s difficult to test how “good” a PRNG is if you can’t even rely on what the starting sequence will be.

Now, that was from 2010, but even back then, you really shouldn’t be doing Monte Carlo anything in Excel.

I did find something worse than RAND():

=(NOW()*100000 - INT(NOW()*100000))

That had been on an official Microsoft support page. [since removed]

One could create a separate RNG workbook for this need. Format things to produce the needed numbers that can be “copy/paste-values” into the main workbook.

This reminds me, the other night we told our kids about this:

image

This.
Heck, you can create this sequence in the same workbook but on a different tab, using a macro.
I’ve done this to show risk of ruin in playing Craps.
You could probably show the results of Monty Hall problem using a Monte Carlo simulation 10000 times.

Okay, now for some serious resources on PRNG. I will be editing this post.

Background

https://hal.inria.fr/hal-01561551/document

Good PRNGs
By “good”, I mean ones appropriate for actuarial usages

NIST recommendations: [developed for crypto, which is even more strict than financial modeling]

Okay, I wouldn’t use this for serious work, but I did use it for RPS tournaments:

Testing PRNGs
Note: some of the tests are for making sure the PRNG is cryptographically secure, and one generally doesn’t need this for financial modeling.

The Dieharder suite of PRNG tests:
https://webhome.phy.duke.edu/~rgb/General/dieharder.php

Bad PRNGs

RANDU


http://physics.ucsc.edu/~peter/115/randu.pdf

Humans



Humans:

I love that 47 didn’t listen to the question. I wonder how many math students you’d have to ask before you got someone who said.pi.

1 Like

Or the overweight math student who chooses 8.5397.

I did this once. Used a Mersenne twister, output the results, used those results in Excel.

1 Like