MSFT Stock Valuation - 2022 May 1, 2012 to May 1, 2022 0 1 2 3 14 25 16 17 18 2090302222EEEN1820954 26 27 33 36 19 20 21 22 15 23 16 37 44 HN34567 46 1 2 7 10 HANSSA28232=RRA 11 12 13 14 17 18 19 20 21 24 25 26 27 29 30 31 33 35 36 37 39 Ready Prices Microsoft MSFT 23.68 24.98 24.06 25.16 9/1/12 24.46 23.46 Date 5/1/12 6/1/12 7/1/12 8/1/12 10/1/12 11/1/12 12/1/12 21.88 22.13 1/1/13 22.75 2/1/13 23.04 3/1/13 23.90 4/1/13 27.66 5/1/13 29.16 29.06 26.79 28.10 28.20 6/1/13 7/1/13 8/1/13 9/1/13 10/1/13 11/1/13 12/1/13 1/1/14 2/1/14 3/1/14 4/1/14 5/1/14 6/1/14 7/1/14 8/1/14 9/1/14 10/1/14 11/1/14 12/1/14 1/1/15 2/1/15 3/1/15 4/1/15 5/1/15 6/1/15 7/1/15 Sheet1 30.00 32.31 31.94 32.30 32.71 35.26 34.75 35.21 36.12 37.38 39.35 40.41 40.92 41.67 40.74 35.43 38.46 35.92 42.97 41.39 39.25 41.52 + Prices S&P 500 Index SPY 108.69 112.52 114.44 117.31 119.64 118.09 118.76 118.98 125.96 127.56 131.82 134.95 138.14 135.58 143.34 139.04 142.74 150.08 154.53 157.68 152.95 159.91 160.53 162.36 166.12 168.75 167.28 173.88 170.68 175.52 180.34 178.89 174.55 184.36 180.66 183.25 185.61 180.96 185.94 38 CHNM4 1 2 5 6 0 3 7 24 26 8 SHAAAAAAAAAAAAAA22=2===== 9 10 11 18 12 14 16 20 17 RETURN DATA SPY Returns MSFT Returns FIN 3210-Investments 1 Dr. Michael Long 3 Stock Valuation Assignment (May 2022 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! 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, 2012 to May 1, 2022) for MSFT and S&P 500 Market Index (SPY). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate monthly returns. I 2. Using the prices provided, calculate the monthly returns for each of the stocks, where r= (P/P-1)-1; which is the same as [(P-P-1)/P] as I covered in the Lecture Video. PLEASE NOTE THAT THE DATA IS LISTED FROM May 2012 TO May 2022! SO BE CAREFUL WITH YOUR RETURN FORMULA! 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 013333 or 1.3333 %. (10 points)
