answer-the-following-questions-in-excel
- Go to Yahoo Finance, and search for MSFT (MSFT). Click on the historical Data tab, and pull the last 5 years of dividend (not stock price) data into excel. Calculate the yearly dividend payout using the trailing 12-months (does not have to correspond to calendar year).
- What is the average the growth rate of the ANNUAL dividend from the previous 4 years?
- If you use the dividend growth model, what should the price of the stock be if investors require an 18% return (using part a as your growth rate)? Or 15% return?Or 12% return?
- What is the required rate that yields a price closest to the current trading price? (Please write the down the current price that MSFT is trading at when you solve this problem).
- If you use the most recent annual growth rate (not the average calculated in part a), what is the required rate that yields a price closest to the current trading price?
- If MSFT decides to not increase their dividend anymore, and keep it at $5 a year indefinitely, what is the price of the stock if investors require an 18% return?
- Go to Yahoo Finance, and search for Microsoft (MSFT). Click on historical data tab, and pull the last 5 years of monthly stock return data into excel.
- What was the average (arithmetic) return for the past 5 years?
- What is the geometric return for the past 5 years?
- What is the variance and standard deviation of the monthly returns?
- What is the expected range of monthly returns we can expect to receive 68% of the time?
- What is the expected range of monthly returns we can expect to receive 95% of the time?
- What is the expected range of monthly returns we can expect to receive 99% of the time?
- GE has 20,000 bonds outstanding with a 7% coupon rate, paid semiannual. There are 20 years left to maturity and have a market price of 990.00 (face value 1000). The company also has 700,000 shares outstanding and currently sell at $32 a share. GE has a beta of 1.09. You expect the market risk premium to be 9% and the risk free rate is currently 2%. Ge also has 200,000 shares of preferred stock which pays an annual dividend of $5. The preferred stock is currently trading at $29 a share. Assuming a 21% tax rate, what is the WACC?