First download into Excel the AT&T (T), Nvidia (NVDA), and S&P 500 Stock Index (GSPC) Monthly Price data for the 5-Year Period from March 1, 2013 to February 28, 2018 using the instructions outlined above. Using the Open and Close Price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month's Open Price is the beginning-of-the-month price and each month's Close Price is the end-of-the-month price. You can get rid of the other data provided for each month. In addition, you will need to include Dividend Payments for each Stock to calculate Monthly Returns for the months that they made a Dividend Payment (the Index doesn't have Dividend Payments). For example, September's Monthly Return is (Sept. Close - Sept. Open + ANY Sept. Dividend)/Sept. Open. You should have 60 Monthly Returns for each Stock and the Index from March 2013 to February 2018. Helpful Hint: I strongly urge creating a table of the appropriate Opening and Closing Prices and Dividends for each Stock along with Opening and Closing Prices for the S&P 500 Index on a separate worksheet and then do calculations on this worksheet. 2. What is the Average Monthly Return for each Stock and the S&P 500 Index over the entire time period? Convert each Stock's and the Index's monthly average into an APR (annualized rate) by multiplying your monthly average for each Stock or Index by 12. Please convert your answers to percentages. 3. Calculate the Sample Standard Deviation of Monthly Returns for each Stock and the Index using Excel. 4. Now calculate the beta for each stock using Excel's SLOPE or LINEST Function as a method to estimate the slope of the linear regression line, which is beta. The y-variable are the Returns of the Stock and the x-variable are the Returns of the Market. Use the S&P 500 Index as the Market Portfolio in finding beta. 5. Now use the CAPM Equation to estimate each stock's Required Return. Use 10% as the Required Market Return and the recent 1-Year Treasury Rate of 2% as the Risk-Free Rate. Would you recommend buying AT&T and/or Nvidia if your APR average for each stock from Q2 is your Expected Return? Explain your answer. 2/3 BUSFIN 1030: CAPM Analysis and Stock Valuation Assignment 6. Construct a graph of the Security Market Line given by the parameters in Q5. Point out on the SML the Required Returns you calculated in Q5. Also, find and plot AT&T's and Nvidia's Expected Returns from the information given below gathered from their recent Yahoo Finance Stock Quotes against each Stock's Beta on your SML graph. Would you recommend buying AT&T and/or Nvidia based on this information? Explain your answers. Note: you are evaluating each stock individually, not in comparison to one another. Stock AT&T Nvidia Current Price $44.00 $245.00 Estimate Price in 1 Year $48.65 $251.12 Annual Expected Dividend (D1) $2.00 $0.60