Price data

Go to finance.yahoo.com and download monthly price data for General Electric (GE) and Nike, Inc. (NKE)
from November 2005 through September 2020. Copy and paste the data into the ‘GE’ and ‘NKE’
worksheets and sort it by date from Oldest to Newest. Compute (net) returns for GE and Nike. Be sure
the returns appear in cells G3:G180 of each of the two worksheets as they do on the AMZN’ sheet. If you’ve done everything correctly the returns should transfer over onto the ‘data’ sheet. Complete the variance-covariance matrix between the three stocks (AMZN, GE, and NKE) in cells H3:J5. If you’ve done everything correctly, the values should transfer over onto theport’ sheet.
What are the means and standard deviations of monthly returns on each of our three stocks (AMZN, GE,
and NKE)? Copy and paste your answers into cells C5:E6 of the ‘ANSWERS’ sheet. What is the relation
between mean return and standard deviation? Are your answers consistent with this relationship
between mean and standard deviation of asset returns? Briefly explain.
Problem 2.
Open the ‘port’ sheet and use the solver as we did in class to find two portfolios on the efficient frontier
for our three assets AMZN, GE, and NKE. I’ve done the first one for you – the weights for an efficient
portfolio Q with average return 1.1% per month are in cells B14:D14. The statistics for portfolio Q are in
B18:F18.
Now find a portfolio P– by finding the optimum weights in cells B15:D:15 — with an average return of
1.5% per month using the Solver. Copy and paste the standard deviation of rP into cell D8 of the
‘ANSWERS’ sheet.
Calculate monthly returns on portfolio Q and P in cells O2:P166.
Problem 3.
On the ‘effront’ worksheet, produce a plot of the efficient frontier for AMZN, GE, and NKE similar to
what we did in class. What is the minimum possible standard deviation for a portfolio composed of
AMZN, GE, and NKE during this sample period? Copy and paste your answer into cell D10 of the
‘ANSWERS’ sheet.
Problem 4.
Go back to ‘port’ sheet. Suppose that the risk free rate is 2% per year (or equivalently, 0.1667% per
month, as entered for you in cell F14 of the ‘port’ sheet). First find two efficient portfolios on the
frontier with 2% (Portfolio A ) and 4% return (Portfolio B) per month by finding the optimum weights in
cells B28:D29. Calculate monthly returns on portfolio A and B in cells Q2:R179.
Using Portfolio A and B, find the average return and standard deviation of the tangency portfolio, T. To
do this, you will have to find the optimum weights of Portfolio A and B in cells B39:C39 in the tangency
portfolio. Copy and paste your answer into cells E12:E13 of the ‘ANSWERS’ sheet.
What are the weights of the original three assets AMZN, GE, and NKE in the tangency portfolio? Copy
and paste your answer into cells C16:E16 of the ‘ANSWERS’ sheet.