The annual return on each of four stocks for each of the next five years is assumed

Question:

The annual return on each of four 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 listed in the file S15_37.xlsx. You believe that the stock returns for these 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 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 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.
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. 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 S15_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.

Stocks
Stocks or shares are generally equity instruments that provide the largest source of raising funds in any public or private listed company's. The instruments are issued on a stock exchange from where a large number of general public who are willing...