Stock Valuation

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.

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 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.

2. Using the prices provided, calculate the monthly returns for each of the stocks, where r = (Pt/Pt-1) 1; which is the same as [(Pt-Pt-1)/ Pt-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)

3. 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)

4. CALCULATE and INTERPRET the Correlation Coefficient (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 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)

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

7. 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)

8. 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) RSPY = 9.0% (based on 25 years of historical data); the risk free rate is

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

(5 points)

9. Based on past trends and ValueLine estimate, lets assume MSFT will pay a dividend of $1.84 in 2019. Therefore, lets assume that D1 = $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 D1 = $1.84

(10 points)

10. 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.

[For example: What is the rate of return if you invest $1 (div0) today and in 10 years it is worth $4 (div10)?]

(5 points)

11. 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 D1 = $1.84, what are the dividends for: D2 ; D3 ; D4 ; and D5 if they grow at the rate estimated in (10) above? You may round each dividend estimate to the nearest penny. (10 points)

12. 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 D6 and P5.

(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)

What is the current price of MSFT (see finance.yahoo.com) (give date you find price)?

Based on your analysis, would you recommend buying Microsoft stock at todays current price?

You must provide a proper explanation of WHY or WHY NOT based on this analysis. (5 points)

Microsoft S&P 500 Index

MSFT SPY

Obs. Date Prices Prices

1 8/1/2008 21.16 104.64

2 9/1/2008 20.78 94.24

3 10/1/2008 17.38 79.13

4 11/1/2008 15.74 73.62

5 12/1/2008 15.24 73.75

6 1/1/2009 13.40 68.24

7 2/1/2009 12.66 60.91

8 3/1/2009 14.50 65.51

9 4/1/2009 15.99 72.54

10 5/1/2009 16.49 76.78

11 6/1/2009 18.88 76.29

12 7/1/2009 18.68 82.45

13 8/1/2009 19.58 85.50

14 9/1/2009 20.54 88.11

15 10/1/2009 22.15 86.83

16 11/1/2009 23.49 92.17

17 12/1/2009 24.45 93.43

18 1/1/2010 22.61 90.52

19 2/1/2010 23.00 93.34

20 3/1/2010 23.61 98.62

21 4/1/2010 24.61 100.56

22 5/1/2010 20.79 92.57

23 6/1/2010 18.63 87.36

24 7/1/2010 20.90 93.78

25 8/1/2010 19.00 89.56

26 9/1/2010 19.93 97.06

27 10/1/2010 21.71 101.31

28 11/1/2010 20.56 101.31

29 12/1/2010 22.86 107.51

30 1/1/2011 22.71 110.60

31 2/1/2011 21.77 114.44

32 3/1/2011 20.91 113.96

33 4/1/2011 21.35 117.77

34 5/1/2011 20.60 116.45

35 6/1/2011 21.56 113.92

36 7/1/2011 22.72 112.19

37 8/1/2011 22.05 106.02

38 9/1/2011 20.77 98.16

39 10/1/2011 22.22 109.43

40 11/1/2011 21.34 108.99

41 12/1/2011 21.82 109.43

42 1/1/2012 24.82 115.23

43 2/1/2012 26.68 120.23

44 3/1/2012 27.30 123.56

45 4/1/2012 27.09 123.27

46 5/1/2012 24.70 115.87

47 6/1/2012 26.05 119.95

48 7/1/2012 25.10 122.00

49 8/1/2012 26.25 125.06

50 9/1/2012 25.52 127.54

51 10/1/2012 24.47 125.89

52 11/1/2012 22.82 126.60

53 12/1/2012 23.09 126.84

54 1/1/2013 23.73 134.27

55 2/1/2013 24.03 135.99

56 3/1/2013 24.94 140.53

57 4/1/2013 28.85 143.86

58 5/1/2013 30.42 147.26

59 6/1/2013 30.32 144.53

60 7/1/2013 27.95 152.80

61 8/1/2013 29.31 148.22

62 9/1/2013 29.42 152.17

63 10/1/2013 31.30 159.99

64 11/1/2013 33.70 164.73

65 12/1/2013 33.32 168.09

66 1/1/2014 33.70 163.05

67 2/1/2014 34.12 170.47

68 3/1/2014 36.78 171.13

69 4/1/2014 36.25 173.08

70 5/1/2014 36.73 177.09

71 6/1/2014 37.68 179.89

72 7/1/2014 39.00 178.32

73 8/1/2014 41.05 185.36

74 9/1/2014 42.15 181.95

75 10/1/2014 42.69 187.11

76 11/1/2014 43.47 192.25

77 12/1/2014 42.50 190.71

78 1/1/2015 36.96 186.08

79 2/1/2015 40.12 196.53

80 3/1/2015 37.47 192.59

81 4/1/2015 44.82 195.35

82 5/1/2015 43.18 197.86

83 6/1/2015 40.95 192.94

84 7/1/2015 43.31 198.17

85 8/1/2015 40.36 187.54

86 9/1/2015 41.32 180.41

87 10/1/2015 49.14 197.73

88 11/1/2015 50.74 197.54

89 12/1/2015 52.14 192.98

90 1/1/2016 51.78 184.46

91 2/1/2016 47.82 184.31

92 3/1/2016 52.28 195.70

93 4/1/2016 47.21 197.48

94 5/1/2016 50.17 200.84

95 6/1/2016 48.78 200.50

96 7/1/2016 54.03 208.89

97 8/1/2016 54.77 209.14

98 9/1/2016 55.25 208.10

99 10/1/2016 57.47 205.53

100 11/1/2016 57.80 213.10

101 12/1/2016 60.01 216.14

102 1/1/2017 62.43 221.31

103 2/1/2017 61.78 230.00

104 3/1/2017 63.98 229.29

105 4/1/2017 66.51 232.58

106 5/1/2017 67.85 235.86

107 6/1/2017 67.35 236.21

108 7/1/2017 71.03 242.24

109 8/1/2017 73.06 242.95

110 9/1/2017 73.17 246.62

111 10/1/2017 81.71 253.68

112 11/1/2017 82.68 261.44

113 12/1/2017 84.45 263.26

114 1/1/2018 93.80 279.52

115 2/1/2018 92.57 269.36

116 3/1/2018 90.53 260.93

117 4/1/2018 92.76 263.33

118 5/1/2018 98.04 269.73

119 6/1/2018 98.23 270.07

120 7/1/2018 105.67 281.33

121 8/1/2018 111.90 290.31