answer the following questions in excel

/in /by

  1. 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).
    1. What is the average the growth rate of the ANNUAL dividend from the previous 4 years?
    2. 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?
    3. 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).
    4. 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?
    5. 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?
  1. 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.
    1. What was the average (arithmetic) return for the past 5 years?
    2. What is the geometric return for the past 5 years?
    3. What is the variance and standard deviation of the monthly returns?
    4. What is the expected range of monthly returns we can expect to receive 68% of the time?
    5. What is the expected range of monthly returns we can expect to receive 95% of the time?
    6. What is the expected range of monthly returns we can expect to receive 99% of the time?
  1. 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?