How to generate random numbers for Monte Carlo simulation in Excel

Risk practitioners often use Monte Carlo simulation to simulate the potential future outcomes of a decision. A key requirement for Monte Carlo simulation is the ability to generate random numbers from a desired probability distribution.

Analysis Toolpak

Excel comes with an add-in called Analysis Toolpak, which includes a graphical user interface for generating random numbers from the uniform distribution, normal distribution, and several discrete probability distributions.

However, the graphical user interface can be inconvenient when you want to change the parameters or regenerate the random numbers.

RANDARRAY function

Excel also comes with the RAND and RANDARRAY functions, which return uniformly distributed random numbers. The functions can be used with inverse sampling to generate random numbers from any probability distribution whose inverse cumulative distribution function can be evaluated in Excel.

For example, to generate a column of 100,000 random numbers from the standard normal distribution, enter the formula =NORM.S.INV(RANDARRAY(100000,1)) in a cell.

However, this method can be inefficient when you want to generate a lot of random numbers and the inverse cumulative distribution function is expensive to evaluate.

QRS.RAND functions

QRS Toolbox for Excel includes functions for generating random numbers from some probability distributions using rejection sampling. The names of the functions all begin with QRS.RAND.

QRS.RAND.NORMAL and QRS.RAND.EXP return random numbers from the normal and exponential distributions respectively using the ziggurat algorithm.

QRS.RAND.GAMMA returns random numbers from the gamma distribution. It can be used to generate random numbers from gamma-related distributions, such as the chi-squared distribution and Beta distribution.

QRS.RAND.PEARSON and QRS.RAND.JOHNSON return random numbers from the Pearson and Johnson distributions respectively. They can be used to simulate rare but extreme events, because the Pearson and Johnson distributions uniquely fit all possible combinations of skewness and kurtosis.

Pearson Type IV example

There are in fact 5 types of Pearson distributions, namely Types I, III, IV, V, and VI. To the best of our knowledge, QRS.RAND.PEARSON is the only publicly available Excel function for generating random numbers from all 5 types of Pearson distributions.

The Pearson Type IV distribution deserves special mention. It is a natural alternative to the normal distribution in risk applications, because it is not only unimodal and unbounded, but also skewed (squared skewness < 32) and fat-tailed (kurtosis > 3).

To try QRS.RAND.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.RAND.PEARSON. Then, open a blank workbook.

To generate a column of 100,000 random numbers from the Pearson Type IV distribution with mean 0, variance 1, skewness 2, and kurtosis 16, enter the formula =QRS.RAND.PEARSON(100000, 1, 0, 1, 2, 16) in cell A1. The random numbers appear in cells A1:A100000.

To generate 5 columns of random numbers instead, change the second argument to 5 so that the formula becomes =QRS.RAND.PEARSON(100000, 5, 0, 1, 2, 16). The random numbers now appear in cells A1:E100000.

To regenerate the random numbers, simply recalculate cell A1. Unlike the RAND and RANDARRAY functions, the QRS.RAND functions are designed to be non-volatile.

Final remarks

The other QRS.RAND functions can be used in a similar manner. If you find the QRS.RAND functions useful, please share this page with other potential users.