Financial Simulation Modeling in Excel

QUICKLY GENERATING NORMAL PSEUDORANDOM NUMBERS USING PREBUILT EXCEL FUNCTIONS

The fastest method to create a normal pseudorandom number in Excel is to leverage Excel's prebuilt functions. Learning the RAND function, which creates a uniform pseudorandom number, is the first step, while understanding the NORMS-INV function is the second. The NORMSINV function returns the inverse of the normal distribution, when provided with a probability. The question, “What is the inverse of the normal distribution?” often follows the introduction of the NORMSINV function. To help explain the function we will look at a more intuitive distribution function and use a tangible example to explain how both functions work.

FIGURE 2.5 The NORMSDIST function provides the value under the curve given a point on the distribution. Here the value under the curve for 1 is visually shown.

The easier normal distribution function to learn first is the NORMSDIST function. If one were to enter “=NORMSDIST(1)” in a cell and press ENTER, the value .84135 would be returned. The NORMSDIST function returns the cumulative area under a normal distribution curve, up to the standard deviation value selected, assuming a distribution with a mean of 0 and a standard deviation of 1. Thus putting a 1 in the parameter for the function returns .84135, meaning that .84135 of the area under the curve is captured up to the first standard ...