Using Excel, compare the efficient frontier of two portfolios, one composed of two highly correlated stocks and the other composed of two stocks showing low or negative correlation. One of the stocks must be common to both portfolios.

Part 1.

• Select three firms as described above and an index that represents the overall market (total 4 sets of prices), download the monthly prices for the most recent 3 years, compute the monthly returns and verify the correlation coefficients (_ab) for all pairs.
• Calculate the covariance for all pairs (_xy), and the variance for the returns of all assets (²_a , ²_b , ²_c and ²_M).
• Use CAPM to calculate the expected return E(R) for each stock
  • Use 1% as the risk-free rate
  • Use 8% as the expected market return
  • Calculate the betas for your stocks (use formula from the slides! Powerpoint 5, slide 7)
• Build a series of portfolios using the first pair of stocks by varying the weight of stock A from 1% to 99% (and stock B from 99% to 1%)
• Calculate the return and risk for each portfolio
• Create the graph in Excel
• Repeat for the second pair of stocks
• Both plots must be on the same graph.

Part 2.

• On a separate graph, use the low-correlation pair of stocks and R_f to create a complete portfolio, draw the Capital Allocation Line
  • Highlight (mark on the graph) the risky corner portfolio with the following allocation: XX% of A, (1-XX%) of B, where XX is the last 2 digits of your student id.
  • Draw the CAL using the risky portfolio above and R_f (dont forget that you can short R_f).
  • Highlight (mark on the graph) the portfolios (2 of them) with the following capital allocations:
    • 50% R_f and 50% Risky
    • -50% R_f and 150% Risky