Financial Simulation Modeling in Excel

OTHER METHODS OF GENERATING NORMAL PSEUDORANDOM NUMBERS

Earlier in this chapter we discussed the merits of using different methods and algorithms to generate uniform pseudorandom numbers and found the RAND function to be suitable in many financial situations. Further transforming the uniform pseudorandom numbers can be done quite easily using the NORMSINV function, but as we get more advanced and possibly switch entirely to a code-based solution, we may want to explore more computationally efficient methods of transformation.

The Box-Muller transformation is a widely accepted method of taking uniform pseudorandom numbers and creating normal pseudorandom numbers. The Box-Muller transformation uses the trigonometric functions SIN and COS. If a simulation were being done in VBA, using this transformation would be more efficient than using NORMSINV, since NORMSINV would have to be called from the WorksheetFunction library, while SIN and COS are contained in the VBA library. Code that is written with references to the WorksheetFunction library is generally slower than code using the VBA library.

Implementing the Box-Muller transformation is relatively simple and can be done on the sheet as shown in the following Model Builder.

MODEL BUILDER 2.5: Creating Normal Pseudorandom Numbers Using the Box-Muller Transformation

1. Insert a new sheet in the Model Builder 2 workbook that you have been working in. Name this sheet “Box-Muller”.
2. Label the following cells:

   C5: “Uniform 1”

   D5: “Uniform ...