**Using Excel Formulas**

1 Stock Valuatica Points lost1 4 5 As a Wealth Manager at Crimson Wealth, you are responsible for making recommendations to clients regarding common stocks. Your research on Core Inc, shows that dividend has been growing at a rate of 10% per year to the current dividend D.) of $1.75 per share. The stock is now selling for $45 per share, and you believe that an appropriate discount rate of return for this stock is 15% 8 9 A. If you expect that the dividend will grow at 10% rate into the foreseeable future, what is the highest price at which you would recommend purchasing this stock for your clients? You are free to use whichever method you are most comfortable with! If you need additional "scratch paper" Ju may use the "Scratch.Sheet" 10 11 Dividend at time 0(D) 12 Market Capitalization Rate (RRR) 13 Growth Rate of Div, a 14 Stock Price 15 $1.75 15.00% 10.0% $45.00 homs neint 19 Using an IF statement to determine your answer will earn 20 Overvalued/Undervalued? Bonuspoint 23 B. Supposed now that you believe that Core's new computer chip will rival Pentel's, resulting in higher growth in the near future. Your new estimate is for a 5 year period of 30% annual growth followed by a return to the historical growth rate of 24 10%. Under these new assumptions, what is the value of the stock using the two stage dividend growth model? Calewote this price buth manually and using the FAME_TwStage Volwe function. 25 26 Use Vlookup when determining the Growth Rates for a bonus point: Bonus point Time Growth Rate Diy PV at time Growth rate of Dividend Beginning Period Growth Rate 100 6 10.00% Po Using Fame Adil-in 10 12 13 14 15 Using an IF statement to determine your answer will earn a bonus point: Over/Undervalued? Bonuspoint 1 Stock Valuatica Points lost1 4 5 As a Wealth Manager at Crimson Wealth, you are responsible for making recommendations to clients regarding common stocks. Your research on Core Inc, shows that dividend has been growing at a rate of 10% per year to the current dividend D.) of $1.75 per share. The stock is now selling for $45 per share, and you believe that an appropriate discount rate of return for this stock is 15% 8 9 A. If you expect that the dividend will grow at 10% rate into the foreseeable future, what is the highest price at which you would recommend purchasing this stock for your clients? You are free to use whichever method you are most comfortable with! If you need additional "scratch paper" Ju may use the "Scratch.Sheet" 10 11 Dividend at time 0(D) 12 Market Capitalization Rate (RRR) 13 Growth Rate of Div, a 14 Stock Price 15 $1.75 15.00% 10.0% $45.00 homs neint 19 Using an IF statement to determine your answer will earn 20 Overvalued/Undervalued? Bonuspoint 23 B. Supposed now that you believe that Core's new computer chip will rival Pentel's, resulting in higher growth in the near future. Your new estimate is for a 5 year period of 30% annual growth followed by a return to the historical growth rate of 24 10%. Under these new assumptions, what is the value of the stock using the two stage dividend growth model? Calewote this price buth manually and using the FAME_TwStage Volwe function. 25 26 Use Vlookup when determining the Growth Rates for a bonus point: Bonus point Time Growth Rate Diy PV at time Growth rate of Dividend Beginning Period Growth Rate 100 6 10.00% Po Using Fame Adil-in 10 12 13 14 15 Using an IF statement to determine your answer will earn a bonus point: Over/Undervalued? Bonuspoint