Financial Simulation Modeling in Excel

NORMAL PSEUDORANDOM NUMBERS

Creating uniform pseudorandom numbers is an excellent starting point, but often doesn't take us far enough in finance, where normal distributions are often assumed. Common errors can occur when people new to financial simulation try either using uniform pseudorandom numbers where normal pseudorandom numbers should be used or incorrectly transform uniform pseudorandom numbers. Fortunately there are prebuilt functions in Excel and established methods such as Box-Muller that allow us to quickly transform uniform pseudorandom numbers to normal pseudorandom numbers.

Although we can quickly transform uniform pseudorandom numbers to normal pseudorandom numbers, we should take a moment to understand what this actually means. With a uniform distribution there is no propensity for the numbers toward a mean or a variance around the mean to give the distribution “shape.” A normal distribution will exhibit a mean and a symmetrical distribution of numbers around that mean depending on the variance.

A good example of the difference between a uniform distribution and a normal distribution is shown by creating a coin toss simulation. In such a simulation we would draw a uniform pseudorandom variable from our computer, perhaps using RAND on the sheet or Rnd in VBA, and then check to see if that number is greater than or equal to .5 (indicating a head) or less than .5 (indicating a tail). We could then run one simulation for hundreds of iterations and then count to see ...