Only need to answer 6,7 and 8
Part I: Beta and Returns Estimation The estimation of betas for firms is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and not until later courses in finance that they realize that the choice of different returns, indices and intervals examined can deliver different beta estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data from the internet which can then be manipulated in Excel. Finally, you will tie the concept of beta and the CAPM to stock valuation. The Yahoo Finance Website has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find a box titled "Get Quotes" which you need to type in the Stock Symbol (if you are unsure of the stock symbol go to "Symbol Lookup"). To download financial data from the internet follow the detailed instructions in the text box below: Instructions for Downloading Stock Data In the Get Quotes Box type in the Stock Symbol (Verizon is VZ and the S&P500 Index is AGSPC) and click the GO Box. Click on Historical Data from the Quotes Section. Click on the Time Period, enter the Date Range as instructed below and click Done Button. Keep Historical Prices for the Show Setting, Set Frequency to Monthly, and click Apply. The first page of data should now be visible on the screen. Click the Download Data Link. Save the data to a file, it will be saved as a CSV file. Open Excel and then open the file you saved. At this point you should have SEVEN Columns including the following: Date, Open, High, Low and Closing Share Prices for that month, plus Volume and Adj. Close data for each month. You can only need the Date, Open and Close columns for each stock or index. To download dividend payments change the Show Setting to Dividends Only, click Apply and export and save this info to a spreadsheet. You will need to do this for any of your stocks that have paid dividends over the time period below. Alternatively, you can just en- ter the dividends for each stock into your spreadsheet from the Yahoo Finance price table itself. The S&P 500 Index already includes dividends. BE CAREFUL! The Dividends Pay- ments occur irregularly. You have to MATCH the Dividend Payment with the Respective Monthly Prices. Answer the Following Questions. 1. First download into Excel the AT&T (T), Nvidia (NVDA), and S&P 500 Stock Index (AGSPC) 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 in- dex 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 In- dex 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 your 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 (annu- 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 (annu- alized 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. 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 rec- ommend 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 (D) $2.00 $0.60 Part II: Stock Valuation 7. Let's assume AT&T is a Variable Growth Stock with an Expected Annual Growth Rate in Dividends of 1% in Years 2 through 5 and a Constant Annual Growth Rate of 2% after Year 5. Use the Dividend D1 estimate from Part I Q6. What is your Valuation of AT&T's Stock TODAY using the Non-Constant Growth Dividend Discount Model? Would you recommend buying AT&T's stock today and why? Use the Current Price in Part 1 Q6 to help aid your recommendation. 8. Now, assume that AT&T's Current Price is in Equilibrium and is a Constant Growth Stock. What is AT&T's Expected Constant Growth Rate using the CAPM Required Return you have estimated? Use Price and Dividend data from Part I Q6. Part I: Beta and Returns Estimation The estimation of betas for firms is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and not until later courses in finance that they realize that the choice of different returns, indices and intervals examined can deliver different beta estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data from the internet which can then be manipulated in Excel. Finally, you will tie the concept of beta and the CAPM to stock valuation. The Yahoo Finance Website has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find a box titled "Get Quotes" which you need to type in the Stock Symbol (if you are unsure of the stock symbol go to "Symbol Lookup"). To download financial data from the internet follow the detailed instructions in the text box below: Instructions for Downloading Stock Data In the Get Quotes Box type in the Stock Symbol (Verizon is VZ and the S&P500 Index is AGSPC) and click the GO Box. Click on Historical Data from the Quotes Section. Click on the Time Period, enter the Date Range as instructed below and click Done Button. Keep Historical Prices for the Show Setting, Set Frequency to Monthly, and click Apply. The first page of data should now be visible on the screen. Click the Download Data Link. Save the data to a file, it will be saved as a CSV file. Open Excel and then open the file you saved. At this point you should have SEVEN Columns including the following: Date, Open, High, Low and Closing Share Prices for that month, plus Volume and Adj. Close data for each month. You can only need the Date, Open and Close columns for each stock or index. To download dividend payments change the Show Setting to Dividends Only, click Apply and export and save this info to a spreadsheet. You will need to do this for any of your stocks that have paid dividends over the time period below. Alternatively, you can just en- ter the dividends for each stock into your spreadsheet from the Yahoo Finance price table itself. The S&P 500 Index already includes dividends. BE CAREFUL! The Dividends Pay- ments occur irregularly. You have to MATCH the Dividend Payment with the Respective Monthly Prices. Answer the Following Questions. 1. First download into Excel the AT&T (T), Nvidia (NVDA), and S&P 500 Stock Index (AGSPC) 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 in- dex 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 In- dex 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 your 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 (annu- 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 (annu- alized 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. 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 rec- ommend 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 (D) $2.00 $0.60 Part II: Stock Valuation 7. Let's assume AT&T is a Variable Growth Stock with an Expected Annual Growth Rate in Dividends of 1% in Years 2 through 5 and a Constant Annual Growth Rate of 2% after Year 5. Use the Dividend D1 estimate from Part I Q6. What is your Valuation of AT&T's Stock TODAY using the Non-Constant Growth Dividend Discount Model? Would you recommend buying AT&T's stock today and why? Use the Current Price in Part 1 Q6 to help aid your recommendation. 8. Now, assume that AT&T's Current Price is in Equilibrium and is a Constant Growth Stock. What is AT&T's Expected Constant Growth Rate using the CAPM Required Return you have estimated? Use Price and Dividend data from Part I Q6
