Question

The purpose of this analysis is to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. You will need to (1) estimate Beta in order to calculate the required return for MSFT; (2) estimate dividend growth rate; and (3) estimate future dividends.   

Submit your Excel spreadsheet with all data and formulas so that your answers can be replicated. You may answer the questions on the spreadsheet. HOWEVER, WRAP YOUR TEXT!!! I do NOTwant to see text running across 40 columns.Remember, Excel is not a word processor. Do a simple draft print to see if your output is in readable form. Follow instructions as written. NEATNESS AND ORGANIZATION MATTERS!

1. 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 December 1, 2008 to December 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.

2. 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 DEC 2008 TO DEC 2018! SO BE CAREFUL WITH YOUR RETURN FORMULA!There are 121 months to calculate 120 monthly returns. You may post monthly returns as (1) decimals to 6 placesor as              (2) percentages to 4 places. For example, average return for MSFT shouldbe written as .009999 or .9999%.

(10 points)

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

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

(5 points)

4. CALCULATEand INTERPRETthe 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)

5. We can estimate the Beta for MSFT over the n-monthly periods by running a Regression of SPY returnson the x-axis (independent variable) and MSFT returnson the y-axis (dependent variable). The Beta is the SLOPEof the regression. To find Beta use the =SLOPE(MSFT, SPY) functionin Excel. Be carefuluse RETURNS NOT prices!  NOTE: You may round your Beta estimates to 2-decimalsfor comparison to Yahoo& Value Line.

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)

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

		Microsoft	S&P 500 Index
		(MSFT)	(SPY)
Obs.	Date	Adj Close	Adj Close
1	12/1/08	15.171647	72.986816
2	1/1/09	13.345434	67.537422
3	2/1/09	12.604017	60.280582
4	3/1/09	14.434885	64.838524
5	4/1/09	15.920021	71.790146
6	5/1/09	16.415056	75.986549
7	6/1/09	18.796745	75.510246
8	7/1/09	18.599051	81.602089
9	8/1/09	19.492626	84.616447
10	9/1/09	20.453114	87.201355
11	10/1/09	22.051516	85.932243
12	11/1/09	23.387495	91.226288
13	12/1/09	24.345514	92.470947
14	1/1/10	22.508421	89.590057
15	2/1/10	22.899803	92.384804
16	3/1/10	23.504421	97.607178
17	4/1/10	24.507513	99.525345
18	5/1/10	20.703796	91.617607
19	6/1/10	18.54821	86.465836
20	7/1/10	20.805275	92.81102
21	8/1/10	18.919022	88.636307
22	9/1/10	19.846544	96.059845
23	10/1/10	21.613195	100.263435
24	11/1/10	20.470543	100.263435
25	12/1/10	22.757057	106.40667
26	1/1/11	22.610296	109.458611
27	2/1/11	21.672617	113.260925
28	3/1/11	20.824682	112.784561
29	4/1/11	21.259386	116.555138
30	5/1/11	20.513008	115.248009
31	6/1/11	21.464779	112.744888
32	7/1/11	22.620571	111.037201
33	8/1/11	21.960123	104.932869
34	9/1/11	20.678095	97.145752
35	10/1/11	22.123648	108.306389
36	11/1/11	21.251326	107.86628
37	12/1/11	21.729425	108.306389
38	1/1/12	24.717642	114.047813
39	2/1/12	26.567492	118.998093
40	3/1/12	27.180515	122.289597
41	4/1/12	26.978304	122.005577
42	5/1/12	24.593901	114.678444
43	6/1/12	25.942581	118.717079
44	7/1/12	24.992739	120.743851
45	8/1/12	26.137638	123.76886
46	9/1/12	25.405874	126.232666
47	10/1/12	24.364372	124.597
48	11/1/12	22.72529	125.302147
49	12/1/12	22.989487	125.531403
50	1/1/13	23.626413	132.893219
51	2/1/13	23.927656	134.588837
52	3/1/13	24.828709	139.080719
53	4/1/13	28.725281	142.38324
54	5/1/13	30.287378	145.744873
55	6/1/13	30.185152	143.043091
56	7/1/13	27.825571	151.23111
57	8/1/13	29.188885	146.695374
58	9/1/13	29.288961	150.603622
59	10/1/13	31.163521	158.345642
60	11/1/13	33.557331	163.038651
61	12/1/13	33.173367	166.362473
62	1/1/14	33.554665	161.370056
63	2/1/14	33.971455	168.71492
64	3/1/14	36.620502	169.36702
65	4/1/14	36.093391	171.296707
66	5/1/14	36.575829	175.271881
67	6/1/14	37.517635	178.037277
68	7/1/14	38.831207	176.486221
69	8/1/14	40.87352	183.451004
70	9/1/14	41.97076	180.078323
71	10/1/14	42.504902	185.181076
72	11/1/14	43.283485	190.268356
73	12/1/14	42.317471	188.743988
74	1/1/15	36.805725	184.162384
75	2/1/15	39.948784	194.513199
76	3/1/15	37.306213	190.607422
77	4/1/15	44.627991	193.340836
78	5/1/15	42.994816	195.826462
79	6/1/15	40.771599	190.95723
80	7/1/15	43.12648	196.136307
81	8/1/15	40.189812	185.614197
82	9/1/15	41.142715	178.559052
83	10/1/15	48.932507	195.695099
84	11/1/15	50.522064	195.507767
85	12/1/15	51.920101	190.992233
86	1/1/16	51.555122	182.563065
87	2/1/16	47.615261	182.412262
88	3/1/16	52.05724	193.683441
89	4/1/16	47.00515	195.44931
90	5/1/16	49.955341	198.774216
91	6/1/16	48.567814	198.433167
92	7/1/16	53.797611	206.740295
93	8/1/16	54.537945	206.987869
94	9/1/16	55.011574	205.959488
95	10/1/16	57.227314	203.411209
96	11/1/16	57.552036	210.904541
97	12/1/16	59.74847	213.919083
98	1/1/17	62.16188	219.030441
99	2/1/17	61.517666	227.63649
100	3/1/17	63.709213	226.933792
101	4/1/17	66.224304	230.183411
102	5/1/17	67.559242	233.431961
103	6/1/17	67.061157	233.780029
104	7/1/17	70.728943	239.748688
105	8/1/17	72.742813	240.448196
106	9/1/17	72.856537	244.081772
107	10/1/17	81.35598	251.073166
108	11/1/17	82.324265	258.747467
109	12/1/17	84.084389	260.553741
110	1/1/18	93.393242	276.645203
111	2/1/18	92.174339	266.586273
112	3/1/18	90.138329	258.24469
113	4/1/18	92.360428	260.618988
114	5/1/18	97.614464	266.954376
115	6/1/18	97.809433	267.289398
116	7/1/18	105.218788	278.436218
117	8/1/18	111.418053	287.323883
118	9/1/18	113.878044	287.729675
119	10/1/18	106.350555	269.058807
120	11/1/18	110.41301	274.049652
121	12/1/18	101.57	248.469025

Answer 1

Formulas:

Beta measures the volatility (or systematic risk) of a stock relative to the volatility (unsystematic risk) of the whole market. Beta helps the investor to understand the direction in which the stock is likely to move compared to the market and how risky it is with respect to the market.