Historical Analysis Project
prices for these two stocks for 11/01/2013-10/31/2014(one year) from Yahoo or Google, along with the
dividend amounts and dates. Do the same for the S&P 500index.
Calculations: Calculate the following for each asset (in Excel, using the statistical functions given in parentheses):
1. Calculate the average return (AVERAGE), standard deviation of returns (STDEV), and variance of returns
(VAR) for each stock and the S&P 500 index.
2. Calculate the covariance (COVAR) and correlation (CORREL) between the returns of stock 1 and stock
2. Remember to adjust the COVAR since the Excel functions divides by n rather than n-1.
3. Calculate the return and the standard deviation of a portfolio that held these two stocks in the following
weights: (-30)%-130%, …0%-100%, 10%-90%, 20%-80%, 30%-70%,…, 90%-10%, 100%-0%, …, 130%-
(-30)%.
4. Plot these portfolio returns – standard deviation combinations (Important: choose a “scatterplot” and not
a “line”!).
5. Calculate the weights for the minimum variance portfolio, and then solve for the return and standard
deviation. Mark this on your plot.
6. Calculate the optimal risky portfolio’s weights in the two stocks, its average return, and its standard
deviation. Mark this on your plot as well.
7. Calculate the Sharpe ratio for each combination.
8. Assuming the weekly risk-free rate is 0.1%, on your plot, plot or draw the optimal CAL and mark the
optimal risky portfolio.
9. Plot two additional investment opportunity sets – first assuming the correlation of your stocks was exactly
1, and second, assuming the correlation was –1.
Written Report: Address the following questions in a one-page, single-spaced written report. Note: this should be
a short essay that describes your results. Assume that you are preparing a primer on portfolio composition and
analysis for the new members of your investment committee. Do not just answer the questions! This is an essay
for investment committee.
1. Looking at the statistics for your two stocks, does the risk-return trade-off hold? Which combinations
should you avoid? Why?
2. Look at the amount you invested in stock 1 and stock 2 at the beginning of the semester. Assume, for this
assignment, that these stocks are your entire portfolio.
a. Which combination would deliver the least amount of risk?
b. Which would deliver the maximum return per unit of risk?
3. Looking at the CAL you have drawn and your calculations for the minimum risk and optimal portfolios,
what conclusions can you draw about the efficiency of the combination you chose?