413 Assignment 2

I Portfolio construction and analysis

I.I Construct a portfolio

1. Complete the fundamentals table of assets in your template

2. Download the daily close prices for the dates and assets named in template from yahoo finance

(should have 252 close prices for each stock)

3. Import daily close values and betas into excel and compute on Portfolio TAB:

Log returns

Risk-Return Matrix

Covariance Matrix

Correlation Matrix

4. Create a portfolio composed of Stock 1 with each of the other stocks

Stock 1 weight is given, find stock 2 weight

find the return on the portfolios

find the portfolio variances using the correlation coefficient

find the portfolio standard deviations

5. Portfolio Analysis

State the portfolio with the lowest risk from 4. above

Look at the correlation coefficient for each pair of stock 1, stock 2 and find the lowest

Is the lowest risk portfolio (V13) also the portfolio with the lowest correlation coefficient V17:V21?

Would you short any of these portfolios? Why?

Which portfolio is objectively optimal on your template?

II Efficient Frontier and the Minimum Variance Portfolio

II.I Portfolio: [BND, SP500]
1. Efficient Frontier

Complete the risk-return table for this portfolio and CORREL (BND, SP500)

Find the portfolio return, variance and volatility for the varying stock weights (see excel template)

plot the Efficient Frontier (x axis is the σP, y axis is the returnP )

Highlight the lowest risk (σP) weight combination in green

2. Find the MVP: Using the equations on Lecture 4 slide 19

Find XA, XB, rp and σ2P

Compare the lowest risk combo in EF to the σP,rP in the MVP, which is lower risk?

III CAPM

III.I 1. Estimate Beta: using returns data calculated in Part I.I.3

Estimate Beta β_i=(Cov(i,M))/(σ_M^2 ) and compare with the Beta given from yahoo finance.

(To estimate beta, our market portfolio is the SP500)

2. CAPM

a. find the 3 month US T bill rate as of 2/13/2020, input the value into the rf cell

http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield

Note: the values on this page are in annual percentage already i.e if the value stated is 0.05 it is 0.05% or 0.0005

b. input the estimated betas you found in J3:J8 into the Beta row

c. Find the CAPM for each stock using the equation: 〖ER〗_i=rf+ [β_i×(〖ER〗_M-rf)]
3. SML

If we vary stock beta from -1 to 1 by increments of .5

a. Find the CAPM for each given beta

b. plot the SML (x axis is risk(beta), y axis is return (CAPM))

c. Compare the CAPM value of SGEN cell R8 to SGEN annual expected return

d. Is SGEN expected return above (undervalued) or below (overvalued) the CAPM required rate of return according to our beta calculations?

e. what conclusions do you find given our SML?

Extra – Arbitrage Pricing Theory

Research how APT differs from CAPM and answer the questionnaire matching column

Suppose that 2 factors have been identified for your stock with annual μ_stock given

F1 = the Unemployment rate and F2=Return of Oil

F1 RP = 1.59% with beta = 0.88 and F2 RP = 3.0% with beta 0.75

Find the APT model return for your asset

Compare to the CAPM for your asset

You found this in previous tab

which model appears to be a closer estimate of the average return for your asset?

DON’T FORGET TO SUBMIT ALL YOUR WORK IN RP2 QUESTIONNAIRE UNDER TESTS & QUIZZES