Problem

The annual return on each of four stocks for each of the next the 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 P10_37.xlsx. You believe that the stock returns for these stocks in a given year are correlated, according to the correlation matrix given, but you 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 in the range C20:G23. You can cheek how the RISKCORRMAT function has been used in these formulas.

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, run the simulation, then click the Simulation Data button in the Results group of the @RISK ribbon, and capture these simulated data on a new sheet. Then calculate correlations for all pairs of columns in this new sheet. (StatTools provides one way to create a matrix of all correlations for the simulated data, but you can do it without StatTools if you like.) 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 formula in cell C20 should be changed to =RISKNORMAL($B5,$C5,RISKCORRMAT($B$12:$E$15,$B20,C$19)). Make this change to the formulas for the returns, 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.

Inputs on stocks
Means, standard deviations, and current prices
	Mean	Stdev
Stock 1	8.0%	6.0%
Stock 2	9.5%	8.0%
Stock 3	11.5%	14.0%
Stock 4	14.5%	19.5%
Correlations
	Stock 1	Stock 2	Stock 3	Stock 4
Stock 1	1	0.55	0.65	0.45
Stock 2	0.55	1	0.50	0.70
Stock 3	0.65	0.50	1	0.55
Stock 4	0.45	0.70	0.55	1
Returns
		Year
		1	2	3	4	5
Stock	1	0.08	0.08	0.08	0.08	0.08
	2	0.095	0.095	0.095	0.095	0.095
	3	0.115	0.115	0.115	0.115	0.115
	4	0.145	0.145	0.145	0.145	0.145