US Stock index return analysis




“US_stock_returns.xlsx”. This file contains the daily time-series of the returns of 2,500 different US stocks. The first 3 columns report the date, the daily risk-free rate (i.e. the yield of short-term US Treasury bonds), and the return of the S&P 500 index, respectively. The next 2,500 columns contain the daily returns of 2,500 individual stocks. The sample period spans 5 years, from January 2008 to December 2012.
*Please use the stock 1951 – stock 2000 (containing 50 stocks) to do calculation.

I need to submit

  • Excel workbook containing all calculations. The workbook needs to be submitted in .xlsx format. The calculations for each individual objective (see below) are presented in separate worksheets of the workbook.
  • a short report (max 2,000 words) explaining and discussing your findings


  • The report should briefly discuss the findings that you present in the workbook. It should include tables and graphs where relevant, and should clearly indicate what you have done and what your results mean. You should focus on providing an intuitive discussion rather than reproducing chunks of theory or merely presenting statistics. The report should not exceed 2,000 words (plus tables and graphs).
  • Any tables and graphs should be clearly labeled and referenced in the main text. The report should be divided into sections and sub-sections, including a short Introduction and a Conclusion.

OBJECTIVES 1-TO-1 (Please explain one by one)

[1]  Use the returns of the 50 individual US stocks from the beginning of the sample period
(2 January 2008) until the penultimate sample day (28 December 2012) to test the      validity of the CAPM. You should implement a 2-pass regression approach (Fama –            MacBeth), with the S&P 500 acting as a proxy for the market return.

[2]  Suppose that the date is currently 31/12/2012 and that the CAPM holds. Considering the       market return observed today and the previously estimated betas, compute the expected returns for each of the 50 stocks. Briefly discuss how your forecasted returns differ to            those actually observed on that date.

[3]  For the remaining of the coursework, assume that your 50 stocks constitute the only
risky assets in the economy (ignore the S&P 500 index). Use the entire sample
(2 January 2008 to 31 December 2012) to compute the risk-return profile for each of
the 50 stocks. Then, compute and plot the Efficient Frontier.

[4]  Suppose that you are facing certain constraints when choosing how to invest in
risky assets. More specifically, assume that the individual weights of short positions   cannot exceed -10% (in absolute terms) and that the individual weights of long positions     cannot exceed 20%. Compute the efficient portfolio (for a constant of your choosing)     under these constraints. Then, compute the corresponding efficient portfolio under no       constraints, and briefly discuss how the weights would be different in each case.

[5]  Suppose that the risk-fee rate is equal to that observed on the last sample date.
Compute the Optimal Risky Portfolio and plot the Capital Market Line.

[6]  Suppose that you have invested in the optimal mix of risky assets described in [5].      Discuss how you could partially hedge your position using options. Motivate your choice    for the hedging strategy and draw a graph of the payoff at maturity for the hedged      position (i.e. stock portfolio plus options).