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
- Insert a new sheet in the Model Builder 2 workbook that you have been working in. Name this sheet “Box-Muller”.
- Label the following cells:
C5: “Uniform 1”
D5: “Uniform 2”
E5: “Normal 1”
F5: “Normal 2”
- Insert an increasing row of numbers, starting with 1 and ending with 100, in B6 to B105.
- In C6 enter =RAND() and copy and paste this across the range C6:D105.
- In E6 enter the following formula:
=SQRT(-2*LN(C6))*COS(2*PI()*D6)
The Box-Muller transformation is using the Cartesian system, which in the current implementation is two dimensional and will therefore be able to generate two normal pseudorandom numbers. In the first number transformation it uses the cosine function. Copy and paste this formula over the range E6:E105.
- Now we can create a second normal pseudorandom number using the sine function. Enter the following formula in F6:
=SQRT(-2*LN(C6))*SIN(2*PI()*D6)
Copy and paste this formula over the range F6:F105.
FIGURE 2.9 The Box-Muller transformation takes the uniform pseudorandom numbers created by the RAND function and returns a set of two normal pseudorandom numbers. The resulting histograms for each set of normal pseudorandom numbers are shown here.
- We can check the results of our number generation by building two histograms as we have done in the previous Model Builder exercises in this chapter. Figure 2.9 shows the normal results of those histograms.