Please complete this problem set #3 More on Covariance, Correlation, Stdev, variance, and firm-specific risk, and upload your Excel file answer sheet provided- THE THIRD FILE
See the attached doc file for the full assignment
Problem Set #3: Fall 2023
Q1: Index Models:
Download 61 months (November 2017 to December 2022) of monthly data for the S&P 500 index (symbol = ^GSPC, or SPY). Download 61 months (November 2017 to December 2022) of price data for one of your 4 stocks (stock #1) and 61 months (November 2017 to December 2022) of price data for a second stock (stock #2) in your 4 stocks. Download 60 months (December 2017 to December 2022) of the 13-week T-bill rate (symbol = ^IRX).
Be sure to use end-of-month data! Construct the following on a spreadsheet:
- Calculate 60 months of returns for the S&P 500 index, stock #1 of your 4 stocks, and stock #2. (Please compute simple monthly returns not continuously compounded returns.) Use November 2017 to December 2022. Note this means you need price data for November 2017. On the answer sheet reports the average monthly returns for the S&P 500 index, stock 1, and stock 2, as well as the average monthly risk-free rate.
- Calculate excess returns for the S&P 500 index, stock 1, and stock 2. Note you must divide the annualized risk-free rate (^IRX) by 1200 to approximate the monthly rate in decimal form. On the answer sheet reports the average monthly excess returns for the S&P 500 index, stock 1, and stock 2.
Requirements: 2-3 pages
