The file has the formulas you might expect for this situation in the range C20:G23. You can check how the RISKCORRMAT function has been used in these formulas. Just so that there is an

@RISK output cell, 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 it is included 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 they should be. To check this, go to

@RISK’s Report Settings and check the Input Data option before you run the simulation. This gives you all of the simulated returns on a new sheet. Then calculate correlations for all pairs of columns in the resulting Inputs Data Report sheet.

(StatTools can be used to create a matrix of all correlations for the simulated data.) Comment on whether the correlations are different from what they should be.

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 P10_37.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 they should be.