The Journal The Journal of of Financial Financial Modeling Modeling and Educational and Educational Technology Technology
Volume 1, Number 1 Number 1
Generating Correlated Normally Distributed Random Numbers in Excel Timothy R. Mayes, Metropolitan State College of Denver of Denver ABSTRACT
Simulating asset prices prices and/or returns and/or returns is often useful for demonstrating for demonstrating important concepts important concepts in class. Because the returns on financial on financial assets assets are typically correlated, typically correlated, it is it is useful to useful to be able to generate new returns new returns that follow a particular covariance or correlation matrix. However, Excel has no built ‐in tools to generate correlated returns. In this paper we discuss several methods of generating random numbers in Excel, and show two show two methods of generating of generating correlated random correlated random numbers.
Introduction The ability to generate random numbers is very useful in financial education. For example, an instructor may wish to demonstrate the meaning of correlation coefficients, how the efficient frontier changes in response to various correlations, or a variety of other concepts. These illustrations often require the creation of a of a set of correlated of correlated random numbers that follow a normal probability distribution. While the normal distribution is not a perfect description of the real‐world distributions of asset returns, it is nonetheless useful in teaching many concepts. This paper discusses several methods of generating of generating random numbers in Excel, and shows two methods of generating correlated random number series. Producing uncorrelated random numbers in Excel is easy using built‐in tools. However, because various asset classes tend to be correlated, these uncorrelated random numbers are not of much direct use. Thus techniques for transforming uncorrelated random number series into correlated random number series are presented.
Uniformly Distributed Random Numbers Most pseudorandom number generation algorithms start by generating a series of uniformly of uniformly distributed random numbers. Those numbers are then transformed by an algorithm into the required distribution with appropriate parameters. There are at least three built‐in methods in Excel for creating uniform random numbers: 1. The Rand and RandBetween worksheet functions. 2. The Rnd VBA function 3. The Random Number Generation tool in the Analysis ToolPak add‐in Both the Rand worksheet function and VBA’s Rnd function generate random numbers between 0 and 1 from a uniform distribution. The RandBetween worksheet function creates uniformly distributed random integers that fall into a user‐supplied range.1 The worksheet functions are the easiest to use, but are only useful if the transformation algorithm can be easily implemented within a spreadsheet (e.g., the Box‐Muller transform).
www.afmet ‐online.org
59
Generating Correlated Normally Distributed Random Numbers in Excel For more complicated algorithms (we will use Cholesky Decomposition) some VBA programming is necessary. Fortunately, it is very likely that the VBA code can be found on the Internet so one probably will not need to write much code.
Using the Random Number Generation Tool The Random Number Generation tool is similar to using the worksheet functions, but it can generate any number of observations for any number of variables all at once. This tool has an advantage over the built‐in worksheet functions in that one can specify a random seed.2 Furthermore, this tool can generate random numbers from any of seven distributions (e.g. uniform, normal, binomial, etc), including a user‐ defined discrete probability distribution. Since the Excel 2007 online help provides only a very short description of this tool, we will provide an example of its usage. To launch the tool click the Data tab on the Ribbon and then the Data Analysis button. A list of the available tools appears from which one should select Random Number Generation. This will lead to the dialog box shown in Figure 1. Figure 1 The Random Number Generation Tool
The dialog box is set up to generate 20 observations of 5 random variables from a normal distribution with a mean of 0 and a standard deviation of 1. The upper left corner of the output is cell A2 of the active worksheet. A random seed of 10 was used so that the reader can reproduce the exact results shown below. It must be stressed that the results shown in Figure 2 are uncorrelated. Additionally, the mean and standard deviation for each variable cannot be specified independently, so all of the variables are generated from identical distributions. Even if correlations are unimportant for a particular project, this limits the usefulness of the tool. Because financial asset returns tend to be correlated with one another, these techniques are useful only as a starting point. They are not a complete solution to the problem at hand; namely, generating correlated normally distributed random variables that are useful for demonstrating financial concepts in the classroom or for research purposes.
60
Winter 2010
The Journal of Financial Modeling and Educational Technology
Volume 1, Number 1
Figure 2 Output from the Random Number Generation Tool
The Box-Muller Transform As previously noted, many pseudorandom number generators take sets of uniformly distributed random numbers as a starting point. These numbers are then transformed to have the appropriate distribution and parameters. One such transformation is the Box‐Muller transform (Box & Muller, 1958), which produces uncorrelated bivariate normally distributed random numbers (Weisstein). The algorithm used is simple to implement using only worksheet functions and requires no VBA programming. First generate two uniformly distributed random variables (U1 and U2) using the Rand function, and create as many observations as desired.3 Next, create two normally distributed random numbers (N1 and N2) by applying the Box‐Muller transform to the numbers generated in the previous step. The transformation equations are:
2ln cos2
(1)
2ln sin2
(2)
N1 and N2 are uncorrelated, so we will generate a third variable (N3) that has the specified correlation with N1 (van den Berg):
1
(3)
where ρ is the desired correlation coefficient. Once the N3 series has been created, we no longer need the N2 series. At this point we have two normally distributed random variables (N1 and N3) with a specified correlation coefficient. The next step is to transform each of these number series so that they have the desired mean and standard deviation. Because the numbers are currently distributed as approximately N(0,1), we can scale them by reversing the standardization formula. Recall that to standardize a number we use:
www.afmet ‐online.org
61
Generating Correlated Normally Distributed Random Numbers in Excel
(4)
Solving for x :
(5)
Using the desired mean and standard deviation for each series, apply that transformation to each observation of the series N1, and then to N3. This produces two normally distributed random series with the specified means, standard deviations, and correlation coefficient.
Transformation Using the NormSInv or NormInv Worksheet Functions While the Box‐Muller transformation is commonly mentioned in literature, it is more complicated than necessary when using Excel. There are two built‐in worksheet functions that convert a probability into a draw from a normal distribution: 1. NormSInv – Returns the inverse of the standard normal cumulative distribution function. 2. NormInv – Returns the inverse of the normal cumulative distribution function with a specified mean and standard deviation. Because the probability is between 0 and 1, we can generate it randomly using the Rand function. Therefore, to generate a pseudorandom standard normal variable, we can use: =NORMSINV(RAND()) or, to specify a mean and standard deviation of, say, 10% and 15% we can use: =NORMINV(RAND(), 0.10,0.15) This method is much easier to remember than the formulas for the Box‐Muller transformation. Using the NormSInv function results in a z‐score, which must then be converted to have the appropriate mean and standard deviation just as above. The result of the NormInv function does not need to be converted, so it is a more direct method. Using either function, the algorithm is essentially the same as with the Box‐Muller transform. We first generate two uncorrelated normally distributed variables (N1 and N2) and then create a third (N3) that has the specified correlation with N1.
Generating Returns Using a Specified Covariance Matrix In the previous section to generate a pair of correlated random number series only a single correlation coefficient is needed. However, to generate multiple random number series that conform to a specified covariance (or correlation) matrix, a covariance matrix must be created. If it is created manually, great care must be taken to ensure that it is consistent. So it is often easier to calculate a covariance matrix from historical data. Once a covariance matrix is available, the following algorithm can be used to generate a set of normally distributed random numbers with the correct (on average) means and standard deviations:
62
Winter 2010
The Journal of Financial Modeling and Educational Technology
Volume 1, Number 1
1. Calculate the Cholesky decomposition (C) of the covariance matrix. This will be discussed in the next section. 2. Generate a set of random numbers from a standard normal distribution (i.e., N(0,1)). This can be done with the Random Number Generation tool as described above, or by using the worksheet function: =NORMSINV(RAND()). Call this matrix X. 3. Multiply the matrix of standard normal variables by the transpose of the Cholesky matrix: N = XCT. Matrix N is the simulated data with appropriate (on average) means, standard deviations, and covariance matrix.
Cholesky Decomposition of the Covariance Matrix The Cholesky decomposition of a symmetric positive definite matrix is simply the square root of the matrix. Thus, the Cholesky decomposition of the covariance matrix (Σ) is the matrix (L) that meets the following criteria:
Σ
(6)
The algorithm to find L is well‐known, and source code is freely available on the Internet in many computer languages. In the sample spreadsheet, we have slightly modified VBA code from VBNumericalMethods.com (Jacquier, 2004), which is an excellent resource for open‐source VBA code.
Cholesky Decomposition of the Correlation Matrix If one prefers to specify the correlation matrix, instead of the covariance matrix, the process is very similar to that shown above, but it requires an adjustment. Without an adjustment the above procedure leads to a correct correlation matrix, but the means and standard deviations will be incorrect. Step 2 of the algorithm created several series of standard normal random numbers using the formula: =NORMSINV(RAND()). When using the Cholesky decomposition of the correlation matrix, the numbers must be scaled to have the proper means and standard deviations. This can be done using the de‐ standardization formula given in equation 5, and the worksheet formula: =J$8+NORMSINV(RAND())*J$9 where J$8 contains the desired mean and J$9 contains the standard deviation. Alternatively, we can use the NormInv function as follows: =NORMINV(RAND(),J$8,J$9) Aside from that minor change, the algorithm is implemented in exactly the same manner.
Simulating a Portfolio Using the Covariance Matrix Imagine that an instructor wishes to simulate the performance of a portfolio comprised of five exchange‐traded funds (ETFs) over time. Assuming that past returns, standard deviations, and covariances are representative of the future, historical data can be used to determine these parameters.
www.afmet ‐online.org
63
Generating Correlated Normally Distributed Random Numbers in Excel The “Cholesky Using Covar Matrix” worksheet in the sample workbook uses monthly return data from January 2004 to December 2008 for the following ETFs:
AGG – iShares Barclays Aggregate Bond (U.S. Bonds) SPY – S&P Depositary Receipts (U.S. Large‐cap Stocks) EWJ – iShares MSCI Japan Index EWG – iShares MSCI Germany Index EWU – iShares MCSI UK Index
In calculating the portfolio statistics, it is assumed that the portfolio is rebalanced each period so as to maintain constant weightings. Figure 3 The Original Variance/Covariance Matrix
Begin by creating a variance/covariance matrix as shown in Figure 3 using the historical monthly returns for the five exchange‐traded funds. This matrix could be created from scratch with the desired characteristics, or, as in the sample workbook, using an add‐in for Excel 2007 to create a live variance/covariance matrix that changes if the underlying data is changed (Mayes, 2009). The variance/covariance matrix can also be created using the Covariance tool from the Analysis ToolPak add‐ in, though this method does not link to underlying data through formulas. The sample worksheet makes use of the algorithm above and VBA code to calculate the Cholesky decomposition of the covariance matrix. Because the result of the Cholesky function is a matrix, the entire output region (a 5x5 range) J13:N17 must be selected prior to entering the formula. Next, click in the formula bar and type: =Cholesky(J3:N7). Instead of pressing the Enter key, the formula must be array‐entered using Shift+Ctrl+Enter. This fills the selected range with the output of the Cholesky function, which is the square root of the covariance matrix and should match that shown in Figure 4. Figure 4 Cholesky Decomposition of Variance/Covariance Matrix
64
Winter 2010
The Journal of Financial Modeling and Educational Technology
Volume 1, Number 1
The next step is to create many observations (say, 100) of five standard normal random variables. As shown above, we can enter the formula: =NORMSINV(RAND()) in a cell and then copy this across and down. Finally, the sample worksheet creates 100 observations of the five correlated normal random numbers with the appropriate means and standard deviations by using the formula: =MMULT(O2:S101,TRANSPOSE(I13:M17)) where O2:S101 contains the standard normal variables, and I13:M17 is the Cholesky decomposition of the covariance matrix. Note that the above formula must be array‐entered (Shift+Ctrl+Enter) after first selecting the entire output range (U2:Y101 in the sample workbook). A set of portfolio returns can now be calculated by a weighted average of the randomly generated returns over 100 periods. From this data, we can calculate the portfolio expected return and standard deviation. The results of a single iteration of the simulation are shown in Figure 5. Figure 5 Portfolio Statistics from an Iteration of the Simulation
In this case equal weights were chosen for each security, but the weights can be changed as desired. A line chart of the result can be created as shown in Figure 6. In Figure 6 the darkest line is the portfolio, and the lighter lines are the returns of the individual securities. The lighter lines are created by setting the transparency to 80%, so that they can be seen but don’t interfere with the portfolio line. It is obvious that combining the securities into a portfolio has resulted in some reduction in volatility. Figure 6 A Chart of the Simulated Portfolio Over 100 Periods
www.afmet ‐online.org
65
Generating Correlated Normally Distributed Random Numbers in Excel
Summary This paper demonstrates simple techniques for generating correlated normally distributed random numbers in Excel. For many in‐class simulations it is useful to create two correlated variables. These can be handled by either a Box‐Muller transformation or, more easily, by using the NormInv function to generate two uncorrelated normally distributed random series and then a third via a simple transformation. The first and third numbers will have the specified correlation on average. When it is necessary to generate several random variables that conform to a specified covariance or correlation matrix, we can use the Cholesky decomposition. The VBA code to provide this decomposition is provided in Module1 of the VBA project in the sample workbook. It can be copied into other open workbooks by dragging it to the VBA project of the other workbook within the VBA Editor.
References Box, G. E., & Muller, M. E. (1958). A note on the generation of random normal deviates. The Annals of Mathematical Statistics , 29, 610‐611. Haugh, M. (2004). The Monte Carlo Framework, Examples from Finance and Generating Correlated Random Variables. Retrieved August 31, 2009, from Columbia University: http://www.columbia.edu/~mh2078/MCS04.html Jacquier, A. (2004, November 10). Math. Retrieved September 11, 2009, from VB Numerical Methods: http://www.vbnumericalmethods.com/math/ Mayes, T. R. (2009, January 7). Variance/Covariance Matrix Add ‐in for Excel 2007. Retrieved September 21, 2009, from TVMCalcs.com: http://www.tvmcalcs.com/blog/comments/VarianceCovariance_Matrix_Add‐in_for_Excel_2007/ van den Berg, M. (n.d.). Generating Correlated Random Numbers. Retrieved September 9, 2009, from Sitmo.com: http://www.sitmo.com/doc/Generating_Correlated_Random_Numbers Weisstein, E. W. (n.d.). Box ‐Muller Transformation. Retrieved September 9, 2009, from MathWorld‐‐A Wolfram Web Resource: http://mathworld.wolfram.com/Box‐MullerTransformation.html
Endnotes 1
The RandBetween function was formerly a part of the Analysis ToolPak add‐in that ships with Excel, but isn’t enabled by default. In Excel 2007 this function was promoted from the add‐in to being built directly into Excel. Users of older versions will need to enable the Analysis ToolPak add‐in to use this function. 2
Specifying a random seed is useful for generating identical sets of random numbers. This is particularly useful in a classroom setting as each student can get precisely the same results as the instructor. Not using this feature of the Random Number Generation tool means that students will all get unique results, which can cause confusion if it isn’t explained clearly. 3
The Rand function is volatile, meaning that it produces a different random number each time the worksheet is recalculated. To eliminate this problem, select the cells containing the Rand function and copy them. Now, use Paste Special → Values to lock in the original numbers. An alternative method, which will preserve the function, is to set the calculation mode to manual. This will prevent recalculation until the user presses the F9 function key.
66
Winter 2010
