A. Choose a stock that interests you. Utilizing Bloomberg (or other financial websites) as a source of data, collect the following information: a. Current stock price b. The last dividend paid (D0) c. The stock's Beta d. The annual expected growth rate of earnings (g) e. The market risk premium (RmRf) f. The risk-free rate (Rf) B. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the intrinsic stock price (P0) C. Based on your above calculations, compare the calculated price with the current market price and indicate whether the stock price is overvalued, undervalued, or at equilibrium? Explain. D. Now, assume that your company has just released a new product and will be experiencing supernormal growth of 25% for the next three years. In Excel, use the information in "A" and the Discounted Dividend Model for Nonconstant Growth Stocks and solve for the intrinsic stock price (P0). The instructions below will help you do assignment 2 . A. Choose a stock that interests you. Utilizing Bloomberg, Yahoo Finance, Google Finance, etc. as a source of data, collect the following information: a. The stock's Beta b. The market risk premium (RmRf) c. The risk-free rate (Rt) d. The last dividend paid (D0) e. The annual expected growth rate of earnings - Go to https://finance.yahoo.com/ and type in a publicly traded company that you are familiar with. For example, Microsoft: Microsoft Corporation (MSFT) 149.760.21(0.14%) - Collect the current stock price and the Beta (all examples boxed in red above). Assume Yahoo uses three years of monthly data to calculate their Beta. - Next, interact with the MSFT chart so that it shows 10yrs of data: - Collect the current stock price and the Beta (all examples boxed in red above). Assume Yahoo uses three years of monthly data to calculate their Beta. - Next, interact with the MSFT chart so that it shows 10yrs of data: - For instance, if the dividend payouts throughout the 10 years ranges from $.08 in 2004 to $.28 in 2014. If we use the PV=FV/(1+r)t formula and solve for r to get the geometric average increase in dividends. So, .08=.28/(1+r)10, solving for r gets us 13.35%. This will be used for g. Next, go to Kenneth French's data library website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data library.html Note that the Return on the Market minus the risk-free rate (RmRf) is 23.52% for the last 12 months (this was noted back in 2014). Next, go to the U.S. Dept. of the Treasury: https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield Use the 1yr rate of .13% as your risk-free rate (again, this is from 2014) - Now, use the CAPM to calculate the Required Rate of Return for MSFT based on the information you have collected. Re=Rf+B(E(Rm)Rf) So, with MSFT Re=.13%+.9(23.52%)=21.30% You would use 21.30% for the Required Rate of Return for the Dividend Growth Model. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the intrinsic stock price (P0) With the Microsoft Example, dividends are paid quarterly. For sake of time and ease, just use the annual amount of dividends for D0, so .28 x 4=1.12. Using annual dividends will match up with our annual figures for R and g. Next, create D1=D0(1+g)=1.12(1.1335)=1.26 So, putting it all together: We have D1=1.26,R=.2130, and g=.1335 using Po=D1/(Rg), we would have Po=1.26/(.2130.1335)=$15.85. Based on your above calculations, compare the calculated price with the current market price and indicate whether is the stock price overvalued, undervalued, or at equilibrium? Explain. A. Choose a stock that interests you. Utilizing Bloomberg (or other financial websites) as a source of data, collect the following information: a. Current stock price b. The last dividend paid (D0) c. The stock's Beta d. The annual expected growth rate of earnings (g) e. The market risk premium (RmRf) f. The risk-free rate (Rf) B. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the intrinsic stock price (P0) C. Based on your above calculations, compare the calculated price with the current market price and indicate whether the stock price is overvalued, undervalued, or at equilibrium? Explain. D. Now, assume that your company has just released a new product and will be experiencing supernormal growth of 25% for the next three years. In Excel, use the information in "A" and the Discounted Dividend Model for Nonconstant Growth Stocks and solve for the intrinsic stock price (P0). The instructions below will help you do assignment 2 . A. Choose a stock that interests you. Utilizing Bloomberg, Yahoo Finance, Google Finance, etc. as a source of data, collect the following information: a. The stock's Beta b. The market risk premium (RmRf) c. The risk-free rate (Rt) d. The last dividend paid (D0) e. The annual expected growth rate of earnings - Go to https://finance.yahoo.com/ and type in a publicly traded company that you are familiar with. For example, Microsoft: Microsoft Corporation (MSFT) 149.760.21(0.14%) - Collect the current stock price and the Beta (all examples boxed in red above). Assume Yahoo uses three years of monthly data to calculate their Beta. - Next, interact with the MSFT chart so that it shows 10yrs of data: - Collect the current stock price and the Beta (all examples boxed in red above). Assume Yahoo uses three years of monthly data to calculate their Beta. - Next, interact with the MSFT chart so that it shows 10yrs of data: - For instance, if the dividend payouts throughout the 10 years ranges from $.08 in 2004 to $.28 in 2014. If we use the PV=FV/(1+r)t formula and solve for r to get the geometric average increase in dividends. So, .08=.28/(1+r)10, solving for r gets us 13.35%. This will be used for g. Next, go to Kenneth French's data library website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data library.html Note that the Return on the Market minus the risk-free rate (RmRf) is 23.52% for the last 12 months (this was noted back in 2014). Next, go to the U.S. Dept. of the Treasury: https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield Use the 1yr rate of .13% as your risk-free rate (again, this is from 2014) - Now, use the CAPM to calculate the Required Rate of Return for MSFT based on the information you have collected. Re=Rf+B(E(Rm)Rf) So, with MSFT Re=.13%+.9(23.52%)=21.30% You would use 21.30% for the Required Rate of Return for the Dividend Growth Model. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the intrinsic stock price (P0) With the Microsoft Example, dividends are paid quarterly. For sake of time and ease, just use the annual amount of dividends for D0, so .28 x 4=1.12. Using annual dividends will match up with our annual figures for R and g. Next, create D1=D0(1+g)=1.12(1.1335)=1.26 So, putting it all together: We have D1=1.26,R=.2130, and g=.1335 using Po=D1/(Rg), we would have Po=1.26/(.2130.1335)=$15.85. Based on your above calculations, compare the calculated price with the current market price and indicate whether is the stock price overvalued, undervalued, or at equilibrium? Explain