A manufacturer wants to determine its WACC. Today, 1/1/2018, the firm issued 7,000 bonds that will mature in 1/1/2038 with $1,000 face value. These bonds will pay a 9% coupon rate semiannually and are currently selling for $950. The firm has 100,000 preferred shares of stock outstanding with a book value of $40, but currently selling for $50 per share. The most recent preferred and common dividends were $3.50 and $2.50 per share, respectively. The firms EPS five years ago was $8.00 and it expects to increase its next dividend payment by the implied 5-year earnings per share growth rate. Flotation costs on debt and preferred equity are both 3%, but 7% in the case of common stocks. The common stock is selling today for $25 and the firms tax rate and payout ratio are 40% and 25%, respectively. The firm has 200,000 shares of common stock outstanding with the same book value as that of its preferred stock.

Calculate the book value and market value weights for each source of capital

Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity).

Determine the weighted average costs of capital using both the market and the book value weight

Source	Quantity	Book Value	Market Value	BV Weights	MV Weights
Common	7000	7000000	6650000	36.84%	39.94%
Preferred	100000	4000000	5000000	21.05%	30.03%
Debt	200000	8000000	5000000	42.11%	30.03%
Totals		19000000	16650000	100%	100%
Yield to Maturity
After-tax Cost of Debt
Cost of Preferred Equity
Growth Rate
Cost of Retained Earnings
Cost of New Common Equity
Weighted Average Cost of Capital
Book Value Weights
With Retained Earnings
With New Common Equity
Market Value Weights
With Retained Earnings
With New Common Equity

Please show any details in the EXCEL!!!!