An investor puts $15,000 in each of four stocks, labeled A, B, C, and D. The table below contains means and standard deviations of the annual returns of these 4 stocks.
A: Mean = .15 Standard Deviation = .05
B: Mean = .18 Standard Deviation = .07
C: Mean = .14 Standard Deviation = .03
D: Mean = .17 Standard Deviation = .06
1) Assume that the returns of these 4 stocks are independent of each other. Use a spreadsheet to calculate the mean and standard deviation of the total amount that this investor earns in 1 year from these 4 investments as a function of the information in the table.
2) Let "v" denote a market volatility index. The standard deviation of a stock "n" is now v*std dev of n, where std dev is its base volatility level. The volatility index impacts all stocks in the same way.
If v=1 then the std dev of the 4 stocks A, B, C, D are as shown in the table. In general, v can be lower than 1 (low volatility) or higher. For example, if v=1.1, then stock A has volatility 1.1*.05, stock B has volatility 1.1*.07, and so on.
Modify the spreadsheet above to accommodate the possibility of v not equal to 1. Use excel data table (1 dimensional) to check the sensitivity of the portfolio's std dev as a function of v. Vary v from .05 to 1.5 in .05 increments.
The solution gives detailed steps on computing the mean and standard deviation of portfolio and checking the sensitivity of the portfolio's standard deviation using market volatility index.