I am considering investing in a portfolio of stocks. I have examined historical performance and gathered...

Transcribed Image Text:

I am considering investing in a portfolio of stocks. I have examined historical performance and gathered the following information for a set of six stocks I am considering (S1-stock1, S2-Stock 2....) Covariance matrix (joint risk) S1 S2 .005 S2 S3 S4 S5 S3 S4 S5 S6 .03 -.031 -.027 .01 .085 -.07 -.05 .02 -0.11 -0.02 .042 .05 -0.06 -0.02 Annual return S1 S2 S3 S4 Annual return 0.2 0.42 1.0 .5 S5 .46 S6 .3 Volatility 0.18 0.32 0.58 0.35 0.25 0.28 a) I am very risk averse, so I would like to determine how to construct a portfolio with very little risk. What percentage of my portfolio should be invested in each stock if I would like to keep the risk (variance of the portfolio) below .02? Create a spreadsheet model and solve. Write a sentence explaining how I should invest my money and what return I should expect to achieve. The sentence can be included on the Excel sheet that contains the solution. b) Now I would like to consider how much risk I would have to accept if I would like a return that is 10% better than the solution found in part a. Create a spreadsheet model and solve. Write a sentence explaining how I should invest my money and what risk level would be required to achieve that return. The sentence can be included on the Excel sheet that contains the solution. Please note I would like two separate spreadsheets (one for part a and one for part b), but you should be able to copy and paste things from one sheet to the other to avoid have to retype a lot of things. I am considering investing in a portfolio of stocks. I have examined historical performance and gathered the following information for a set of six stocks I am considering (S1-stock1, S2-Stock 2....) Covariance matrix (joint risk) S1 S2 .005 S2 S3 S4 S5 S3 S4 S5 S6 .03 -.031 -.027 .01 .085 -.07 -.05 .02 -0.11 -0.02 .042 .05 -0.06 -0.02 Annual return S1 S2 S3 S4 Annual return 0.2 0.42 1.0 .5 S5 .46 S6 .3 Volatility 0.18 0.32 0.58 0.35 0.25 0.28 a) I am very risk averse, so I would like to determine how to construct a portfolio with very little risk. What percentage of my portfolio should be invested in each stock if I would like to keep the risk (variance of the portfolio) below .02? Create a spreadsheet model and solve. Write a sentence explaining how I should invest my money and what return I should expect to achieve. The sentence can be included on the Excel sheet that contains the solution. b) Now I would like to consider how much risk I would have to accept if I would like a return that is 10% better than the solution found in part a. Create a spreadsheet model and solve. Write a sentence explaining how I should invest my money and what risk level would be required to achieve that return. The sentence can be included on the Excel sheet that contains the solution. Please note I would like two separate spreadsheets (one for part a and one for part b), but you should be able to copy and paste things from one sheet to the other to avoid have to retype a lot of things.