Part1. Data Processing
This data processing is needed to answer the questions in Part 2.
FM_ASGN_Data contains monthly prices of 10 stocks from January 2016 to December 2020
(5 years), the S&P 500, and the risk-free rate (1-month treasury rates).

• First, calculate monthly returns (excluding the risk-free rate) for each item, including
  the S&P 500 as the following
  Return(t) = [Price(t) – Price(t-1)] / Price(t-1)
• The risk-free rate (1-month treasury) is in annual percentage. Convert them into
  monthly rates using
  Monthly rate = (1+Annual rate/100)
  1/12 – 1
  Note: The risk-free rates during this period are very low. You will need to adjust the
  excel so it shows at least 4 decimals.
  Assumptions/guidelines are as follows:

1. The S&P 500 is representative of the entire stock market.
2. Except for Q5, all answers should be in monthly returns or standard deviations.
3. These Excel functions will be helpful.
   Average: ‘=average(x1:x2)’
   Standard deviation: ‘=stdev.s(x1:x2)’
   Variance: ‘=var.s(x1:x2)’
   Covariance: ‘=covariance.s(y1:y2,x1:x2)’
   Correlation: ‘=correl(y1:y2,x1:x2)’
   Beta: ‘=covariance.s(y1:y2,x1:x2)/var.s(y1:y2)’
   where y is the market index
   and x is the individual stock
   Part2. Questions
   Q1. From January 2016 to December 2020, what are the average returns on the risk-free
   treasury bill, the S&P 500, and each security? What is each asset’s volatility of returns?
   Q2. Calculate the monthly returns of a portfolio that invests equal amounts in each of the 10
   individual securities. Is this a diversified portfolio? – i.e. how does the risk of the portfolio
   compare to those of individual securities?
   Q3. Plot the volatility-return relation. Is there a clear relation between risk and return?
   Q4. Which asset (including the S&P 500 and the equal-weighted portfolio in Q2) has the
   highest Sharpe ratio?

• Sharpe Ratio = Excess Return/Volatility
  = (average return – risk-free rate)/standard deviation
  Q5. Convert the monthly returns and volatilities from Q1 into annual numbers.
• Annual return = (1+Monthly return)12 – 1 – Rough approximation of volatility annualization: SDyear = SDmonth*(12)1/2
  Q6. Calculate the betas for each security, using the CAPM. Which stock has the highest beta?
  Which has the lowest beta? How does the beta relate to the industry the stock belongs to?
  Q7. Plot the beta and expected return relation. Does the relation fit the SML? What do you
  conclude about the validity of the CAPM?