will like, thank you!!!

B F 2016 4.36 2017 5.68 D 2018 6.84 E 2019 8.22 G 2020 2.06 Geometric avg. dividend growth rate 2 Year 3 Dividend 4 Dividend gross growth rate 5 Terminal value of the stock 6 PV of dividends 7 PV of terminal value of the stock 8 Stock value at the beginning of 2017 10 Annual risk-free rate 11 Beta 12 Expected market risk premium 13 Equity cost of capital 14 15 163.4200 9 0.0089 1.53 0.057 0.09611 4. Please compute the geometric average of the dividend growth rate over the four year period, which is the per year dividend growth rate that arrives the cumulative dividend growth rate over the past 4 years. You can simply do this by setting the formula in G4 as = {{C4*D4*4*F4)^.25)-1 and press enter. 5. To compute the expected share price in 2020, we need the equity cost of capital. It can be estimated from the capital asset pricing model, which is introduced in Chapter 12. re=r; +B (E(rm) - r), where r, is the risk free interest rate, E(rn) - ry is the market risk premium, and B measures the sensitivity of a stock's return relative to the market return. I have provided the annual risk free rate and equity risk premium in 2017. The equity risk premium is obtained from Statista Research Department. I have set the formula for equity cost of capital in the Excel template. What you have to do is to look up the value of the stock's beta from Yahoo Finance. We don't have the historical data of a stock's beta in 2016, so just use the information from the most recent year. Once you enter the value of the beta in B10, Excel will return you the equity cost of capital 6. Now you will compute the expected share price (i.e. the terminal value of the stock) in 2020 Div 2920X(1+9) using the constant growth dividend model. It is P2020 Set a formula by (9) referencing to the equity cost of capital and the geometric average dividend growth rate (i.e. the constant dividend growth rate.) You should be able to arrive the terminal value of the stock in 2020. 7. Compute the present values of expected dividends from 2017 to 2020 and the expected share price in 2020. Div 2017 Div2018 DiV2019 Div2020 P2020 P2016 = (1 +re) + (1 +re)2 + (1+re)3+ (1+r)* *(1 + re)* This is the intrinsic value of this stock at the end of 2016 computed from the 2-stage dividend discount model. In Chapter 7 you learned that the constant growth dividend model assumes that re > g. Hopefully, the geometric average of the dividend growth rate you computed is less than its equity cost of capital. Otherwise, you will arrive a negative stock price and there is no way to estimate the expected share price in 2020. Pick a stock with a history of smooth dividend payments or a high beta stock (e.g., beta greater than 2). Your stock pick must be different from my example! The 2-stage dividend discount model just provides a ballpark estimate of a stock price. The model is very sensitive to the changes of the dividend growth rate and the estimated equity cost of capital