# Question: The annual return on each of four stocks for each

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 S15_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 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.

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.

**View Solution:**## Answer to relevant Questions

It is surprising (but true) that if 23 people are in the same room, there is about a 50% chance that at least two people will have the same birthday. Suppose you want to estimate the probability that if 30 people are in the ...In Example 16.1, the possible profits vary from negative to positive for each of the 10 possible bids examined.a. For each of these, use @RISK’s RISKTARGET function to find the probability that Miller’s profit is ...In the cash balance model from Example 16.5, the timing is such that some receipts are delayed by one or two months, and the payments for materials and labor must be made a month in advance. Change the model so that all ...In the financial world, there are many types of complex instruments called derivatives that derive their value from the value of an underlying asset. Consider the following simple derivative. A stock’s current price is $80 ...A martingale betting strategy works as follows. You begin with a certain amount of money and repeatedly play a game in which you have a 40% chance of winning any bet. In the first game, you bet $1. From then on, every time ...Post your question