The annual return on each of four stocks for each of the next five years is assumed to follow a normal distribution, with the mean and standard deviation for each stock, as well as the correlations...

The annual return on each of four stocks for each of the next five years is assumed to follow a normal distribution, with the mean and standard deviation for each stock, as well as the correlations between stocks, listed in the file P11_38.xlsx. We believe that the stock returns for these stocks in a given year are correlated, according to the correlation matrix given, but we believe the returns in different years are uncorrelated.


For example, the returns for stocks 1 and 2 in year 1 have correlation 0.55, but the correlation between the return of stock 1 in year 1 and the return of stock 1 in year 2 is 0, and the correlation between the return of stock 1 in year 1 and the return of stock 2 in year 2 is also 0. The file has the formulas you might expect for this situation entered in the range C20:G23. You can check how the RISKCORRMAT function has been used in these formulas. Just so that we have an @RISK output cell, we calculate the average of all returns in cell B25 and designate it as an @RISK output. (This cell is not really important for the problem, but we include it because @RISK requires at least one output cell.)


a.  Using the model exactly as it stands, run @RISK with 1000 iterations. The question is whether the correlations in the simulated data are close to what we expect. To find out, go to @RISK’s Report Settings and check the Input Data option before you run the simulation. This will show you all of the simulated returns on a new sheet. Then use Excel’s CORREL function to calculate correlations for all pairs of columns in the resulting Inputs Data Report sheet. (This is tedious. We recommend instead that you use the StatTools add-in included with this book or the Analysis ToolPak that ships with Excel to create a matrix of all correlations.) Comment on whether the correlations are different from what you expect.


b.  Recognizing that this is a common situation (correlation within years, no correlation across years), @RISK allows you to model it by adding a third argument to the RISKCORRMAT function: the year index in row 19 of the P11_38.xlsx file. For example, the RISKCORRMAT part of the formula in cell C20 becomes =RISKNORMAL($B5,$C5, RISKCORRMAT($B$12:$E$15,$B20,C$19)). Make this change to the formulas in the range C20:G23, rerun the simulation, and redo the correlation analysis in part a. Verify that the correlations between inputs are now more in line with what you expect.