Financial Simulation Modeling in Excel

DISTRIBUTIONS

Once pseudorandom numbers are generated, the most common error I see when I look at finance students who are starting to implement simulations into their analysis is for them to generate a series of pseudorandom numbers using Excel's prebuilt functions and assume that those numbers can be directly used to test the probability of events taking place. We will delve into working with probabilities in simulations later in this chapter, but for now what we have to realize is that a pseudorandom number can be generated from differing numerical distributions.

The best way to understand what it means to generate a pseudorandom number from different numerical distributions is to start with the outputs from the series of pseudorandom numbers and create a histogram with the numbers. In the Model Builder sections in this chapter, we will look at how to create these outputs, but for now we should understand that pseudorandom numbers generated assuming different distributions will have very different characteristics. Figure 2.1 shows three histograms generated from uniform, normal, and lognormal distributions, as labeled.

MODEL BUILDER 2.1: How to Implement Uniform Pseudorandom Number Generation in Excel

As was just shown in Figure 2.1, a pseudorandom number can take many forms depending on the distribution assumed. In this Model Builder exercise we will start with the most basic pseudorandom generation on the Excel sheet using the RAND function and show how to create a histogram ...