You are analyzing Microsoft to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. I have provided you with an Excel spreadsheet of monthly prices (121 months) from August 1, 2008 to August 1, 2018). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate 120 monthly returns.

Using the prices provided, calculate the monthly returns for each of the stocks, where r = (P_t/P_t-1) 1; which is the same as [(P_t-P_t-1)/ P_t-1] as I covered in the Lecture Video. PLEASE NOTE THAT THE DATA IS LISTED FROM AUG 2008 TO AUG 2018! SO BE CAREFUL WITH YOUR RETURN FORMULA! There are 121 months to calculate 120 monthly returns. You may post monthly returns as decimals to 6 places or percentages to 4 places. For example, average return for MSFT can be written as .009999 or .9999%.

(10 points)

At the bottom of the column for each stock calculate the Average Monthly Return (use AVERAGE() function) and the Standard Deviation [use STDEV.P()] population function NOT STDEV() sample function).

As a check, you should find your average returns to be: MSFT = 1.6146% and SPY = .9469%.

(5 points)

CALCULATE and INTERPRET the Correlation Coefficient (r_1,2) between the RETURNS (not Prices) of Microsoft (MSFT) and S&P 500 Market Portfolio Index (SPY) => (use the CORREL() function). (5 points)

We can estimate the Beta for MSFT over the n-monthly periods by running a Regression of SPY returns on the x-axis (independent variable) and MSFT returns on the y-axis (dependent variable). The Beta is the SLOPE of the regression. To find Beta use the SLOPE function in Excel. Be careful use RETURNS NOT prices!

Calculate the beta for each period:

(a) Estimate Beta over the full 120 monthly returns (10 years): Months 1 - 120

(b) Estimate Beta over the first 60 monthly returns (5 years): Months 1 - 60

(c) Estimate Beta over the second 60 monthly returns (5 years): Months 61 120

(d) Estimate Beta over the last 36 monthly returns (3 years): Months 85 120

(10 points)

Define Beta; give a good explanation of what Beta represents (see Module 2). (5 points)

Now, lets check the stability of Beta:

(a) Is there a substantial difference between the four Betas? COMMENT! (5 points)

(b) How do your estimates compare to the FinanceYahoo.com beta and the Value Line beta? (5 points)

Given the information below, use the CAPM to estimate the required rate of return for MSFT. Round to 2 decimals, e.g., x.xx%, 1.23%

Return on the market portfolio (SPY) R_SPY = 9.0% (based on 25 years of historical data); the risk free rate is

(based on L-T inflation rate of 2.0% & real return of 1.0%); USE MSFT beta estimate: b = 0.95

(5 points)

Based on past trends and ValueLine estimate, lets assume MSFT will pay a dividend of $1.84 in 2019. Therefore, lets assume that D₁ = $1.84. Lets also assume that MSFT will grow its future dividends at a L-T constant rate of g = 6%. Assuming a required rate of return found in (8) above, estimate the current value of MSFT using the Constant Growth DDM. Assume that D₁ = $1.84

(10 points)

Now, using the Value Line sheet, estimate the average growth rate of dividends for MSFT over the last 10 years, from 2008-2018? Round your growth estimate to 4 decimal places. [Hint: The Growth rate (g) can be calculated as CPT i on your calculator or in Excel as a TVM problem.

(5 points)

Two-stage Non-constant DDM: Now lets assume that for the next four years MSFT will grow its dividends at the growth rate you estimated in (10) above. Assuming D₁ = $1.84, what are the dividends for: D₂ ; D₃ ; D₄ ; and D₅ if they grow at the rate estimated in (10) above? You may round each dividend estimate to the nearest penny. (10 points)

Now, lets assume that the dividend growth reverts back to a L-T sustainable growth rate = 6% after Year 5, that is from Year 6 to infinity. Estimate is D₆ and P₅.

(5 points)

Use the Non-constant growth DDM from the Stock Video Lecture (at 17:20) to estimate the current value of MSFT using the dividend information you found in (11) & (12) above; assume a L-T sustainable growth rate of g = 6% after Year 5; and the required rate of return found in (8). [HINT: You already have all the data, not much work left here.find the sum of the PV of the cash flows.]

(10 points)

Which of the two models do you think is more reasonable (Constant DDM or Non-constant DDM)? WHY?

(5 points)

Data:

		Microsoft	S&P 500 Index
		MSFT	SPY
Obs.	Date	Prices	Prices
1	8/1/08	21.16	104.64
2	9/1/08	20.78	94.24
3	10/1/08	17.38	79.13
4	11/1/08	15.74	73.62
5	12/1/08	15.24	73.75
6	1/1/09	13.40	68.24
7	2/1/09	12.66	60.91
8	3/1/09	14.50	65.51
9	4/1/09	15.99	72.54
10	5/1/09	16.49	76.78
11	6/1/09	18.88	76.29
12	7/1/09	18.68	82.45
13	8/1/09	19.58	85.50
14	9/1/09	20.54	88.11
15	10/1/09	22.15	86.83
16	11/1/09	23.49	92.17
17	12/1/09	24.45	93.43
18	1/1/10	22.61	90.52
19	2/1/10	23.00	93.34
20	3/1/10	23.61	98.62
21	4/1/10	24.61	100.56
22	5/1/10	20.79	92.57
23	6/1/10	18.63	87.36
24	7/1/10	20.90	93.78
25	8/1/10	19.00	89.56
26	9/1/10	19.93	97.06
27	10/1/10	21.71	101.31
28	11/1/10	20.56	101.31
29	12/1/10	22.86	107.51
30	1/1/11	22.71	110.60
31	2/1/11	21.77	114.44
32	3/1/11	20.91	113.96
33	4/1/11	21.35	117.77
34	5/1/11	20.60	116.45
35	6/1/11	21.56	113.92
36	7/1/11	22.72	112.19
37	8/1/11	22.05	106.02
38	9/1/11	20.77	98.16
39	10/1/11	22.22	109.43
40	11/1/11	21.34	108.99
41	12/1/11	21.82	109.43
42	1/1/12	24.82	115.23
43	2/1/12	26.68	120.23
44	3/1/12	27.30	123.56
45	4/1/12	27.09	123.27
46	5/1/12	24.70	115.87
47	6/1/12	26.05	119.95
48	7/1/12	25.10	122.00
49	8/1/12	26.25	125.06
50	9/1/12	25.52	127.54
51	10/1/12	24.47	125.89
52	11/1/12	22.82	126.60
53	12/1/12	23.09	126.84
54	1/1/13	23.73	134.27
55	2/1/13	24.03	135.99
56	3/1/13	24.94	140.53
57	4/1/13	28.85	143.86
58	5/1/13	30.42	147.26
59	6/1/13	30.32	144.53
60	7/1/13	27.95	152.80
61	8/1/13	29.31	148.22
62	9/1/13	29.42	152.17
63	10/1/13	31.30	159.99
64	11/1/13	33.70	164.73
65	12/1/13	33.32	168.09
66	1/1/14	33.70	163.05
67	2/1/14	34.12	170.47
68	3/1/14	36.78	171.13
69	4/1/14	36.25	173.08
70	5/1/14	36.73	177.09
71	6/1/14	37.68	179.89
72	7/1/14	39.00	178.32
73	8/1/14	41.05	185.36
74	9/1/14	42.15	181.95
75	10/1/14	42.69	187.11
76	11/1/14	43.47	192.25
77	12/1/14	42.50	190.71
78	1/1/15	36.96	186.08
79	2/1/15	40.12	196.53
80	3/1/15	37.47	192.59
81	4/1/15	44.82	195.35
82	5/1/15	43.18	197.86
83	6/1/15	40.95	192.94
84	7/1/15	43.31	198.17
85	8/1/15	40.36	187.54
86	9/1/15	41.32	180.41
87	10/1/15	49.14	197.73
88	11/1/15	50.74	197.54
89	12/1/15	52.14	192.98
90	1/1/16	51.78	184.46
91	2/1/16	47.82	184.31
92	3/1/16	52.28	195.70
93	4/1/16	47.21	197.48
94	5/1/16	50.17	200.84
95	6/1/16	48.78	200.50
96	7/1/16	54.03	208.89
97	8/1/16	54.77	209.14
98	9/1/16	55.25	208.10
99	10/1/16	57.47	205.53
100	11/1/16	57.80	213.10
101	12/1/16	60.01	216.14
102	1/1/17	62.43	221.31
103	2/1/17	61.78	230.00
104	3/1/17	63.98	229.29
105	4/1/17	66.51	232.58
106	5/1/17	67.85	235.86
107	6/1/17	67.35	236.21
108	7/1/17	71.03	242.24
109	8/1/17	73.06	242.95
110	9/1/17	73.17	246.62
111	10/1/17	81.71	253.68
112	11/1/17	82.68	261.44
113	12/1/17	84.45	263.26
114	1/1/18	93.80	279.52
115	2/1/18	92.57	269.36
116	3/1/18	90.53	260.93
117	4/1/18	92.76	263.33
118	5/1/18	98.04	269.73
119	6/1/18	98.23	270.07
120	7/1/18	105.67	281.33
121	8/1/18	111.90	290.31