Question

Stock Valuation Assignment (Fall 2017 Data)

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 NOT want 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!

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 Sept 1, 2007 to Sept 1, 2017). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate 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 SEPT 2007 TO SEPT 2017! 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.2310% and SPY = .6861%.

(5 points)

Calculate and Interpret the Correlation Coefficient (r_1,2) between Microsoft (MSFT) and S&P 500 Index (SPY). (use CORREL() function). (5 points)

We can estimate the Beta for MSFT over the 120-month period 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!

How does your estimate compare to the FinanceYahoo.com beta and the Value Line beta? What does Beta represent? (10 points)

Now, let’s check the stability of Beta. You may again use the SLOPE function in Excel, where SPY is independent & MSFT is dependent variable.

Estimate Beta over the first 60 monthly returns (5 years): October 1, 2007 to September 1, 2012.

Estimate Beta over the second 60 monthly returns (5 years):         October 1, 2012 to September 1, 2017.

What is the beta for each period? Is there a substantial difference between the two Betas?

(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.25% (based on 25 years of historical data); the risk free rate is R_f = 3.0% (based on L-T inflation rate of 2.0% & real return of 1.0%); USE MSFT beta estimate: b = 0.97

(10 points)

Based on past trends and ValueLine estimate, let’s assume MSFT will pay a dividend of $1.64 in 2018. Therefore, let’s assume that D₁ = $1.64, because it will not be fully paid until the end of Year 2018. Let’s 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 (7) above, estimate the current value of MSFT using the Constant Growth DDM.   Assume that D₁ = $1.64

(10 points)

Now, using the Value Line sheet, estimate the average growth rate of dividends for MSFT over the last 10 years, from 2007-2017? 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 let’s assume that for the next four years MSFT will grow its dividends at the growth rate you estimated in (9) above. Assuming D₁ = $1.64, what are the dividends for: D₂ ; D₃ ; D₄; and D₅ if they grow at the rate estimated in (9)? You may round each dividend estimate to the nearest penny.

(10 points)

Now, let’s assume that the dividend growth reverts back to a L-T sustainable growth rate = 6% after Year 5 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 (10) & (11) above; assume a L-T sustainable growth rate of g = 6% after Year 5; and the required rate of return found in (7). [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 market price of MSFT? Based on your analysis, would you recommend buying this stock at the current market price? Explain why or why not?

(10 points)

MSFT Stock Valuation - Fall 2017
September 1, 2007 to September 1, 2017
	Microsoft	S&P 500 Index
Date	MSFT	SPY
9/1/2007	23.005114	123.184921
10/1/2007	28.744678	125.448433
11/1/2007	26.238007	120.589432
12/1/2007	27.891703	118.602097
1/1/2008	25.541286	112.022667
2/1/2008	21.310514	109.127731
3/1/2008	22.321413	107.619057
4/1/2008	22.431522	113.306641
5/1/2008	22.274223	115.019432
6/1/2008	21.716801	104.881966
7/1/2008	20.303745	104.45945
8/1/2008	21.543118	106.073692
9/1/2008	21.153511	95.53141
10/1/2008	17.697933	80.212509
11/1/2008	16.025621	74.629189
12/1/2008	15.511801	74.753479
1/1/2009	13.644643	69.172134
2/1/2009	12.886605	61.739662
3/1/2009	14.758518	66.407921
4/1/2009	16.276951	73.527817
5/1/2009	16.783087	77.82576
6/1/2009	19.21818	77.337944
7/1/2009	19.01605	83.577255
8/1/2009	19.929657	86.664581
9/1/2009	20.911688	89.312035
10/1/2009	22.545919	88.012207
11/1/2009	23.911846	93.434395
12/1/2009	24.89135	94.709175
1/1/2010	23.013071	91.75856
2/1/2010	23.413229	94.620926
3/1/2010	24.031397	99.969772
4/1/2010	25.056971	101.934349
5/1/2010	21.167976	93.835182
6/1/2010	18.964071	88.558739
7/1/2010	21.271736	95.057457
8/1/2010	19.343184	90.781731
9/1/2010	20.291504	98.384964
10/1/2010	22.097773	102.690292
11/1/2010	20.929499	102.690292
12/1/2010	23.267279	108.982224
1/1/2011	23.117222	112.108025
2/1/2011	22.158522	116.00238
3/1/2011	21.291574	115.514496
4/1/2011	21.736027	119.37632
5/1/2011	20.972919	118.037544
6/1/2011	21.946026	115.473801
7/1/2011	23.127737	113.724846
8/1/2011	22.452477	107.472748
9/1/2011	21.141701	99.497154
10/1/2011	22.619667	110.927917
11/1/2011	21.727795	110.477135
12/1/2011	22.21661	110.927917
1/1/2012	25.271822	116.808311
2/1/2012	27.163143	121.878418
3/1/2012	27.789909	125.249596
4/1/2012	27.583172	124.958702
5/1/2012	25.1453	117.454208
6/1/2012	26.524225	121.590622
7/1/2012	25.553089	123.666451
8/1/2012	26.723654	126.764626
9/1/2012	25.975487	129.288025
10/1/2012	24.910635	127.612816
11/1/2012	23.234793	128.335098
12/1/2012	23.504921	128.569839
1/1/2013	24.156122	136.109879
2/1/2013	24.464123	137.846497
3/1/2013	25.385376	142.447144
4/1/2013	29.369312	145.82959
5/1/2013	30.966436	149.272614
6/1/2013	30.861908	146.505402
7/1/2013	28.44943	154.891617
8/1/2013	29.843309	150.246063
9/1/2013	29.94562	154.248947
10/1/2013	31.862221	162.17836
11/1/2013	34.309704	166.984924
12/1/2013	33.917126	170.389221
1/1/2014	34.30698	165.276016
2/1/2014	34.733093	172.79866
3/1/2014	37.441544	173.466476
4/1/2014	36.902618	175.442932
5/1/2014	37.39587	179.514313
6/1/2014	38.358791	182.346588
7/1/2014	39.701805	180.758041
8/1/2014	41.789921	187.891373
9/1/2014	42.911755	184.437057
10/1/2014	43.457882	189.66333
11/1/2014	44.253918	194.873749
12/1/2014	43.266247	193.312485
1/1/2015	37.63092	188.620041
2/1/2015	40.844452	199.221344
3/1/2015	38.142635	195.221054
4/1/2015	45.628563	198.020798
5/1/2015	43.958771	200.566574
6/1/2015	41.685722	195.579468
7/1/2015	44.093388	200.883865
8/1/2015	41.090885	190.107056
9/1/2015	42.065155	182.881134
10/1/2015	50.029591	200.431534
11/1/2015	51.654785	200.23967
12/1/2015	53.084164	195.614822
1/1/2016	52.711006	186.981628
2/1/2016	48.682812	186.827194
3/1/2016	53.224377	198.371201
4/1/2016	48.059025	200.180191
5/1/2016	51.075367	203.585556
6/1/2016	49.656723	203.236313
7/1/2016	55.003773	211.744019
8/1/2016	55.760704	211.997604
9/1/2016	56.244946	210.944336
10/1/2016	58.510365	208.334351
11/1/2016	58.842373	216.009033
12/1/2016	61.088055	219.096573
1/1/2017	63.555569	224.331726
2/1/2017	62.896912	233.146057
3/1/2017	65.137604	232.426315
4/1/2017	67.709076	235.754608
5/1/2017	69.073936	239.081802
6/1/2017	68.564697	239.438278
7/1/2017	72.314713	245.551392
8/1/2017	74.373741	246.267838
9/1/2017	73.870003	249.113724

Answer 1