Question

1. Go to Yahoo Finance, and search for MSFT (MSFT). Click on the historical Data tab, and pull the last 5 years of dividend (not stock price) data into excel. Calculate the yearly dividend payout using the trailing 12-months (does not have to correspond to calendar year).
2. If you use the most recent annual growth rate (not the average calculated in part a), what is the required rate that yields a price closest to the current trading price?
3. If MSFT decides to not increase their dividend anymore, and keep it at $5 a year indefinitely, what is the price of the stock if investors require an 18% return?

Answer 1

Date	Dividend	Growth rate
19-Nov-19	0.51
13-Aug-19	0.46
14-May-19	0.46
19-Feb-19	0.46
22/11/18- 21/11/19 (year 5)	1.89	(1.89/1.72)-1 = 9.88%
13-Nov-18	0.46
14-Aug-18	0.42
15-May-18	0.42
13-Feb-18	0.42
22/11/17- 21/11/18 (year 4)	1.72	(1.72/1.59)-1 = 8.18%
14-Nov-17	0.42
14-Aug-17	0.39
15-May-17	0.39
13-Feb-17	0.39
22/11/16- 21/11/17 (year 3)	1.59	(1.59/1.47)-1 = 8.16%
14-Nov-16	0.39
15-Aug-16	0.36
16-May-16	0.36
15-Feb-16	0.36
22/11/15- 21/11/16 (year 2)	1.47	(1.47/1.29)-1 = 13.95%
16-Nov-15	0.36
17-Aug-15	0.31
18-May-15	0.31
16-Feb-15	0.31
22/11/14- 21/11/15 (year 1)	1.29
DDM Formula
Market price per share = [Dividend*(1+growth rate)] / (Required rate of return - Growth rate)
In our case we need to calculate required rate of return, so we rearrange above formula
Required rate of return = {[(Dividend*(1+growth rate)] / Market price per share)}+ growth rate
{[1.89*(1.0988)]/149.48}+9.88% = 11.27%
Required rate of return is 11.27%
If MSFT decides to not increase their dividend anymore, and keep it at $5 a year indefinitely
Present value of perpetuity = Dividend / Discount rate
	5/18% = 27.78 USD