Q3 Q3 Q4 Average = 2.0232% Std Dev= 0.06003 Correlation = 62.1535% 1.1062% 0.04017 62.1535% Q5 Q6(a) Q6(b) 10-yr Beta Years 1-10 = Beta for Years 1-5 = Beta for Years 6-10 = Find the required return using CAPM equation Q11. Assuming that after Year 5 and beyond the growth in dividends = 6%, find D6 and P5. 31 32 33 34 35 36 37 38 39 -40 41 42 Q7. 43 44 Rm = AE 45 Rf= -46 Beta = 47 48 Return = 49 50 51 52 08. 53 54 55 56 57 Estimate price of MSFT using the Constant Growth Divdend Discount Model (DDM) Q12. Using the Non-constant DDM, estimate the price of MSFT. 58 Estimate the short-term growth rate of dividends from 2010 to 2020 Q13 Explain which of the two dividend discount models is more reasonable. 59 60 61 62 63 09. 64 65 66 67 68 169 70 71 72 Q10. 73 74 75 76 77 78 79 80 81 82 03 Estimate Dividends D1, D2, D3, D4, and D5 using the growth rate found in 09. Q14. Show the current price of MSFT. Based on your analysis in this assignment, explain WHY or WHY NOT you would recommend buying MSFT at its current Price of MSFT = Date of price quote = Ktock Valuntion Assinment (May 2020 Data) 7. Given the information below, use the CAPM to estimate the required rate of return for MSFT. Use this return for answering questions below. The purpose of this analysis is to find an intrinsic value for Microsoft (MSFT) using the both the Constant Divideod Discount Model (DDM) and the Non-constant DDM. You will need to (1) cstimate Bcta in order to calculate the required return for MSFT; (2) estimate dividend growth rate; and (3) estimate future dividends, Assume the Return on the market portfolio (SPY) Rspy = 9.0% (based on 25 years of historical data): the risk fres rate is Rr - 3.0% (hased on L-T inflation rate of 2.17% & real return of 1.0%); USE MSFT heta estimate: B=.90 (10 points) Submit your Excel spreadsboct with all data and formulas so that your answers can be replicated. You may answer the questions on the spreadshoct. 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! 8. Based on past trend, it is estimated that MSFT will pay a dividend of $2.04 in 2020 (Year 0). [NOTE: This does NOT malch the Valueline sheet estimate of $1.99 for 21/20! Therefore, ASSUME D - 52.114. Let's also assure that MSFT will grow its future dividends at a L-T constant rale afg-6%. Assuming a required rale ol'return found in (7) abrve, estimate the current value of MSFT using the Constantinwth DDM, where - 52.04. NOTE: You need to find D1. (10 points) 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 121 monthly prices from May 1, 2010 La May 1, 2020) for MSFT and S&P SCO Market Index (SPY). These prices have already been adjusted for dividends. Last dates and prices out on your spreadsheet in cirder to calculate monthly relums. 9. Now, specifically using the dividends as listed on the Value Line shoct (Dix.ds Decl per sku, find the average growth Tale of dividends for MSFT over the last 10 years, from 2014-20201. Round your growth estimate to 3 decimal places. [Hint: The Crowth rate (g) can be calculated as CPT on your calculator or in Excel as a TVM problem. For example: What is the rate of return if you invest Sl Loday and in 10 years it is worth $2.507 ANS: 9.595%] (5 points) 2. Using the prices provided, calculate the monthly retums for each of the stocks, wherer-(PHP1)-1; which is the same as [(P- PP-l as I covered in the Lecture Video PLEASE NOTE THAT THE DATA IS LISTED FROM May 2010 TO May 2020! SO BE CAREFUL WITH YOUR RETURN FORMULA! You may post monthly retums as decimals to 6 places or percentages to 4 places. For example, average retum for MSFT can be written as .013333 or 1.3333% (10 points) 10. Two-stage Non-existant DDM: Now let's assume that for the next five years MSFT will grow its dividends at the yn wth rate you estimated in (9) ahove. Asuming Do - $2.AM, estimale dividends for: DL12; 13; D4 and DS! You may run each dividend estimate to the nearest penny. (10 points) 3. At the bottom of the column for ench stock calculate the average Monthly Return (use -AVERAGE function) and the Standard Deviation usc STDEV.PO1 population function NOT STDEVO sample function) As a check, you should find your average retums to be: MSFT - 2.0232% and SPY = 1.1062%. (5 points) 11. Assuming that dividend growth reverts back to a L-T sustainable growth rate = 6% aftor Year 5 (i.c., Year 6 to infinity), estimate is Ds and Ps? (5 points) 4. Calculate and Interpret the correlation Cocfficient (pl.) between MSFT and SPY. (use =CORRELO function). (5 points) 12. 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% alter Year S: and the required rate of return found in (7)- [HINT: You already have all the data, nal much work left here....simply find the PV of the cash flows.] (10 points) 5. Estimate the Beta for MSFT over the 120 month period by running a Regression in Excel of MSFT returns on the y-axis (dependent variable) and SPY returns on the x-axis (independent variable). The Bera = the SLOPE of the regression_So just use the SLOPEO function in Excel to find the Beta. Be careful use Returns NOT Prices! How does your estimate compare to the Finance Yahoo.com beta and the Value Line beta? (10 points) 13. EXPLAIN which of the two models do you think is more reasonable (Constant DDM or Nod-constant DDM). (5 points) 6. Now, let's check the stability of Beto. Again, use the SLOPE function in Excel, where SPY is dependent & MSFT is independent variable (a) Calculate Bcta (=slope) on tctums over the first 60 months (Rcturns 1-60): June 1. 2010 to May 1, 2015 (b) Calculate Bcta (=slope) on returns over the second 60 months (Returns 61-120: June 1, 2015 to May 1, 2020. 14. Look up the current market price of MSFT (sce finance.yahoo.com). Based on your analysis in this assignment, would you recommend buying this stock at that price? Must explain why or why not! (10 points) Comment on any differences between the 10-year vs the two 5-year Betas? (5 points) Q3 Q3 Q4 Average = 2.0232% Std Dev= 0.06003 Correlation = 62.1535% 1.1062% 0.04017 62.1535% Q5 Q6(a) Q6(b) 10-yr Beta Years 1-10 = Beta for Years 1-5 = Beta for Years 6-10 = Find the required return using CAPM equation Q11. Assuming that after Year 5 and beyond the growth in dividends = 6%, find D6 and P5. 31 32 33 34 35 36 37 38 39 -40 41 42 Q7. 43 44 Rm = AE 45 Rf= -46 Beta = 47 48 Return = 49 50 51 52 08. 53 54 55 56 57 Estimate price of MSFT using the Constant Growth Divdend Discount Model (DDM) Q12. Using the Non-constant DDM, estimate the price of MSFT. 58 Estimate the short-term growth rate of dividends from 2010 to 2020 Q13 Explain which of the two dividend discount models is more reasonable. 59 60 61 62 63 09. 64 65 66 67 68 169 70 71 72 Q10. 73 74 75 76 77 78 79 80 81 82 03 Estimate Dividends D1, D2, D3, D4, and D5 using the growth rate found in 09. Q14. Show the current price of MSFT. Based on your analysis in this assignment, explain WHY or WHY NOT you would recommend buying MSFT at its current Price of MSFT = Date of price quote = Ktock Valuntion Assinment (May 2020 Data) 7. Given the information below, use the CAPM to estimate the required rate of return for MSFT. Use this return for answering questions below. The purpose of this analysis is to find an intrinsic value for Microsoft (MSFT) using the both the Constant Divideod Discount Model (DDM) and the Non-constant DDM. You will need to (1) cstimate Bcta in order to calculate the required return for MSFT; (2) estimate dividend growth rate; and (3) estimate future dividends, Assume the Return on the market portfolio (SPY) Rspy = 9.0% (based on 25 years of historical data): the risk fres rate is Rr - 3.0% (hased on L-T inflation rate of 2.17% & real return of 1.0%); USE MSFT heta estimate: B=.90 (10 points) Submit your Excel spreadsboct with all data and formulas so that your answers can be replicated. You may answer the questions on the spreadshoct. 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! 8. Based on past trend, it is estimated that MSFT will pay a dividend of $2.04 in 2020 (Year 0). [NOTE: This does NOT malch the Valueline sheet estimate of $1.99 for 21/20! Therefore, ASSUME D - 52.114. Let's also assure that MSFT will grow its future dividends at a L-T constant rale afg-6%. Assuming a required rale ol'return found in (7) abrve, estimate the current value of MSFT using the Constantinwth DDM, where - 52.04. NOTE: You need to find D1. (10 points) 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 121 monthly prices from May 1, 2010 La May 1, 2020) for MSFT and S&P SCO Market Index (SPY). These prices have already been adjusted for dividends. Last dates and prices out on your spreadsheet in cirder to calculate monthly relums. 9. Now, specifically using the dividends as listed on the Value Line shoct (Dix.ds Decl per sku, find the average growth Tale of dividends for MSFT over the last 10 years, from 2014-20201. Round your growth estimate to 3 decimal places. [Hint: The Crowth rate (g) can be calculated as CPT on your calculator or in Excel as a TVM problem. For example: What is the rate of return if you invest Sl Loday and in 10 years it is worth $2.507 ANS: 9.595%] (5 points) 2. Using the prices provided, calculate the monthly retums for each of the stocks, wherer-(PHP1)-1; which is the same as [(P- PP-l as I covered in the Lecture Video PLEASE NOTE THAT THE DATA IS LISTED FROM May 2010 TO May 2020! SO BE CAREFUL WITH YOUR RETURN FORMULA! You may post monthly retums as decimals to 6 places or percentages to 4 places. For example, average retum for MSFT can be written as .013333 or 1.3333% (10 points) 10. Two-stage Non-existant DDM: Now let's assume that for the next five years MSFT will grow its dividends at the yn wth rate you estimated in (9) ahove. Asuming Do - $2.AM, estimale dividends for: DL12; 13; D4 and DS! You may run each dividend estimate to the nearest penny. (10 points) 3. At the bottom of the column for ench stock calculate the average Monthly Return (use -AVERAGE function) and the Standard Deviation usc STDEV.PO1 population function NOT STDEVO sample function) As a check, you should find your average retums to be: MSFT - 2.0232% and SPY = 1.1062%. (5 points) 11. Assuming that dividend growth reverts back to a L-T sustainable growth rate = 6% aftor Year 5 (i.c., Year 6 to infinity), estimate is Ds and Ps? (5 points) 4. Calculate and Interpret the correlation Cocfficient (pl.) between MSFT and SPY. (use =CORRELO function). (5 points) 12. 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% alter Year S: and the required rate of return found in (7)- [HINT: You already have all the data, nal much work left here....simply find the PV of the cash flows.] (10 points) 5. Estimate the Beta for MSFT over the 120 month period by running a Regression in Excel of MSFT returns on the y-axis (dependent variable) and SPY returns on the x-axis (independent variable). The Bera = the SLOPE of the regression_So just use the SLOPEO function in Excel to find the Beta. Be careful use Returns NOT Prices! How does your estimate compare to the Finance Yahoo.com beta and the Value Line beta? (10 points) 13. EXPLAIN which of the two models do you think is more reasonable (Constant DDM or Nod-constant DDM). (5 points) 6. Now, let's check the stability of Beto. Again, use the SLOPE function in Excel, where SPY is dependent & MSFT is independent variable (a) Calculate Bcta (=slope) on tctums over the first 60 months (Rcturns 1-60): June 1. 2010 to May 1, 2015 (b) Calculate Bcta (=slope) on returns over the second 60 months (Returns 61-120: June 1, 2015 to May 1, 2020. 14. Look up the current market price of MSFT (sce finance.yahoo.com). Based on your analysis in this assignment, would you recommend buying this stock at that price? Must explain why or why not! (10 points) Comment on any differences between the 10-year vs the two 5-year Betas? (5 points)