Analysis of risk and return, portfolio diversification

Here you will apply what you have learned about portfolio theory. Use the monthly-adjusted

closing prices for IBM, MSFT, And the S&P500 during the five-year period from January 2013 –

December 2017 in the file “Stock Project Stock Prices” posted on Canvas. Calculate returns for

each month for each of these three assets (Stock 1; Stock 2; S&P 500).

Exercise 1:

Calculate the following for each asset (in Excel, using the statistical functions given in

parentheses): average return (AVERAGE), standard deviation of returns (STDEV.S), and variance

of returns (VAR.S). What is the covariance (COVAR.S) and correlation (CORREL) between the

returns of stock 1 and stock 2?

Exercise 2:

Calculate the return and standard deviation of a portfolio that holds these two stocks in the

following weights: 0%-100%; 10%-90%; 20%-80%; 30%-70%; 40%-60%; 50%-50%; 60%-40%;

70%-30%; 80%-20%; 90%-10%, 100%-0%. Plot these portfolio return / standard deviation

combinations. Make sure return is on the vertical axis and standard deviation is on the horizontal

axis. (Important: use a scatterplot) (You may use excel for this part)

Exercise 3

1. Which specific combination would deliver the least amount of risk? Use the formula for the

minimum variance portfolio (show your work) to get the exact weights, calculate its return,

standard deviation and Sharpe ratio (show your work by hand), and mark it by hand on your plot

printout.

2.Draw in the CAL (by hand) that gives you the best risk-return combinations, given that the

monthly risk free rate is 0.15%. Mark the optimal risky portfolio. Calculate the optimal risky

portfolio’s weights (show your work by hand) in the two stocks (using the book’s formula 6.10).

For this optimal portfolio, calculate the average return, standard deviation, and Sharpe ratio (show

your work). Mark the ORP on the plot printout by hand.

3. Mark the spot on your return / standard deviation plot where the market index (i.e. S&P 500)

falls.

4. For a moment, assume the correlation between the two stocks equals exactly 1. Graph the

investment opportunity set. (Hint: This does not require any additional excel work or calculations)

5. Now assume the correlation between the two stocks equals exactly –1. Again, graph the

investment opportunity set. If you would like, you can perform steps 4 and 5 on the same graph.

What to hand in: A

hard copy

of your plot, results, and

detailed

analysis — this means you should

clearly show all of your work! Write out all equations that you use, with the exception of those

that are functions in excel such as AVG, COVAR, VAR, etc. For example, you need to write out

the equations for portfolio return, portfolio standard deviation, and show all your calculations in

determining the MVP and ORP.