Multiple Correlation

FSMulticorr(Correlation Matrix, Optional Random Numbers, Optional Error check logical variable)

There is a second function in FinPak that allows multiple correlation among more than 2 variables. To use this function you need a full correlation matrix. An example of a three variable correlation matrix is given in Table 1. The function: Fsmulticorr(Correlation Matrix, Optional Randin, Optional True or False [Default]). This function is an array function and must be entered for the correct number of rows/variables. The process uses a Choleskey decomposition to compute the correlated digits. This generates correlated normally distributed values which are converted to uniformly distributed values. Discussion of the process is beyond what is needed at this point.

It is important that the correlation matrix be correctly defined. Table 1 show a correlation matrix for three variable, it is always a square matrix. Notice the principal diagonal are all 1.00. Also notice that element 1,2 is always equal to element 2,1 and element 1,3 is always equal to element 1,3 or in general element (i,j) is always equal to element(j,i). This the only required input.

To us FSMulticorr you must use the function as an array formula. This is a special form of the function/formula in Excel. You can study the Excel Help file to see a general discussion of array formula or see Array Formulas. In this case the random number input vector must be a row vector, the output may be either a row or column vector.

Panel 1 of Table two shows the same result, with different values for the random numbers, each time F9 is pushed or the spreadsheet is re-calculated three new correlated random numbers will appear. Panel two shows the optional value, notice the double commas to indicate that you leaving out the next input value and are supplying the third one. In this case the result are returned without checking the correlation matrix. Panel 3 of Table 2 shows the option for entering your own set of random numbers, note that you must enter as many random number cells as you have rows/columns in the matrix, in this case three. If you fail to do that you will either get an error message explaining this (if the last option was false), or a #Value error if it were true.

In place of Rand() you could of course use the FinPak FSRandseq() random numbers. While array function can be either row (as used here) or column vector, optional input random numbers must always be a row vector. For FSMulticorr the optional random number input vector must be a row vector, the output may be either a row or column vector (the example above shows a row output vector).