Let me extract the main points: [and I’m editing my article - hey, it’s mine]
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.
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.
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.
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.
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.
