Assignment Paper

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?