The balance sheet that follows indicates the capital structure for Nealon. Inc. Flotation costs are (a) 15 percent of market value for a new bond issue, and (b) $2.01 per share for preferred stock. The dividends for common stock were $2.50 last year an are projected to have an annual growth rate of 6 percent. The firm is in a 34 percent tax bracket.. what is the weighted average cost of capital if the firm's finances are in the following proportions?
TYPE OF FINANCING PERCENTAGE OF FUTURE FINANCING
Bonds [8%, $1,000 par, 16 year maturity] 38%
Preferred stock [5,000 shares outstanding,
$50par, $1.50 dividend] 15%
Common equity 47%
Total 100%
a. Market prices are $1.0.35 for bonds, $19 for preferred stock and $35 for common stock. There will be sufficient internal common equity funding (i.e., retained earnings) available suck that the firm does not plant to issue new common stock.
b. In part a we assumed that Nealon would have sufficient retained earnings such that it would not need to sell additional common stock to finance its new investments. Consider the situation now, when Nealon's retained earnings anticipated for the coming year are expected to fall short of the equity requirement of 47 percent of new capital raised. Consequently, the firm foresees the possibility that new common shares will have to be issued. To facilitate the sale of shares, Nealon's investment banker has advised management that they should expect a price discount of approximately 7 percent, or $2.45 per share. Under these terms, the new shares should provide net proceeds of about $32.55.
I need help with computing the bond net price in Excel. What is the formula?