Solve the problem using excel formula

A B C D E F G H K L M P-16: Dividend valuation model for new public issue (LO15-1) The investment banking firm of Einstein & Co. will use a dividend valuation model to appraise the shares of the Modern Physics Corporation. Dividends (D1) at the end of the current year will be $1.64. The growth rate (g) is 8 percent and the discount rate (Ke) is 13 percent. a. What should be the price of the stock to the public? b If there is a 7 percent total underwriting spread on the stock, how much will the issuing corporation receive? C. If the issuing corporation requires a net price of $31.30 (proceeds to the corporation) and there is a 7 percent underwriting spread, what should be the price of the stock to the public? Given: D, = End Per Div K. = Discount Rate g = growth rate C) D1 1.64 OUT A g 8.0% Ko 13.0% a) Price to Public: =D 1/(K. -g) 60 00 V Spread %: 7.0% b) Price to Firm: Firm Price = Public Price *(1-Spread) 10 Target Price to Firm: 31.30 ~ Recalculated to solve for Public Price: 11 Target Price to Public: ..Public Price = Firm Price/(1-Spread) 12 P-17: Comparison of private and public debt offering (LO15-1) The Landers Corporation needs to raise $1.60 million of debt on a 20-year issue. If it places the bonds privately, the interest rate will be 10 percent. Twenty thousand dollars in out-of-pocket costs will be incurred. For a public issue, the interest rate will be 9 percent, and the underwriting spread will be 2 percent. There will be $120,000 in out-of-pocket costs. Assume interest on the debt is paid semiannually, and the debt will be outstanding for the full 20-year period, at which time it will be repaid. For each plan, compare the net amount of funds initially available-inflow-to the present value of future payments of interest and principal to determine net present value. Assume the stated discount rate is 12 percent annually. Use 6 percent semiannually throughout the analysis. (Disregard taxes.) 13 14 Given: DEBT OPTIONS ~ On Own - Partial 15 Private Public 16 FV 1,600,000 1,600,000 17 n 40 40 18 5.0% 4.5% 19 6.0% 6.0% 20 PMT 21 Out of Pocket Costs: 20,000 120,000 22 Spread %: 2.0% 23 Spread Amount: 24 Funds Initially Available: 25 Minus PV: PVB = PMT*(1-1/(1+r) " )/r+FV/(1+r)" 26 Net Present Value: NPV =Funds Initially Available - PV of the Debt Obligation 27