Nova Corporation is interested in measuring the cost of each specific type of capital as as the weighted average cost of capital. Historically, the firm has raised capital in the following manger: Source of Capital Weight Long Term Debt 35% Preferred Stock 12 Common Stock Equity 53 The tax rate of the firm is currently 40%. The needed financial information and data are as follows: Debt Nova can raise debt by selling $1,000-par value, 6.5% coupon interest rate, 10 year bonds on which annual interest payments will be made. To sell the issue, an average discount of $20 per bond needs to be given. There is an associated allocation cost of 2% of par value. Preferred Stock Preferred Stock can be sold under the follow terms: The Security has a par value of $100 per share, the annual divedend rate is 6% of the par value, and the flotation cost is expected to be $4 per share. The preferred stock is expected to sell for $102 before cost considerations. Common Stock The current price of Nova's common stock is $35 per share. The cash dividend is expected to be $3.25 per share next year. The firm's dividends have grown at an annual rate of 5% and it is expected that the dividend will continue at this rate for the forseeable future. The flotation costs are expected to be approximately $2 per share. Nova can sell new common stock under these terms. Retained Earnings The firm expects to have available $100,000 of retained earnings in the coming year. Once these retained are exhausted, the firm will use new common stock as the form of common stock equity financing.

To Do Create a spreadsheet to answer the following questions.

A. Calculate the after-tax cost of debt

B. Calculate the cost of preferred stock

C. Calculate the cost of retained earnings

D. Calculate the cost of new common stock- I completed D, but need help with putting it into a spreadsheet.

The answer I came up with in the attached document, please check to make sure I have the correct answer.

E. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights shown in the table above.

F. Calculate the firm's weigted average cost of capital using new common stock and the capital structure weights shown in the table above.

Here is the problem: Nova Corporation is interested in measuring the cost of each specific type of capital as as the weighted average cost of capital. Historically, the firm has raised capital in the following mannger: Source of Capital Weight Long Term Debt 35% Preferred Stock 12 Common Stock Equity 53 The tax rate of the firm is currently 40%. The needed financial information and data are as follows: Debt Nova can raise debt by selling $1,000par value, 6.5% coupon interest rate, 10 year bonds on which annual interest payments will be made. To sell the issue, an average discount of $20 per bond needs to be given. There is an associated flocation cost of 2% of par value. Preferred Stock Preferred Stock can be sold under the follow terms: The Security has a par value of $100 per share, the annual divedend rate is 6% of the par value, and the flotation cost is expected to be $4 per share. The preferred stock is expected to sell for $102 before cost considerations. Common Stock The current price of Nova's common stock is $35 per share. The cash dividend is expected to be $3.25 per share next year. The firm's dividends have grown at an annual rate of 5% and it is expected that the dividend will continue at this rate for the forseeable future. The flotation costs are expected to be approximately $2 per share. Nova can sell new common stock under these terms. Retained Earnings The firm expects to have available $100,000 of retained earnings in the coming year. Once these retained are exhausted, the firm will use new common stock as the form of common stock equity financing. To Do Create a spreadsheet to answer the following questions. A. Calculate the aftertax cost of debt B. Calculate the cost of preferred stock C. Calculate the cost of retained earnings D. Calculate the cost of new common stock E. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights shown in the table above. F. Calculate the firm's weigted average cost of capital using new common stock and the capital structure weights shown in the table above. This is what I came up with: I don't know how to put it in Excel format on the spreadsheet. D)cost of new common stock =Dividend 1 / Price * (1F) + Growth Rate = $3.25 / $35 * (10.06)+ 5% =0.093 *$0.94 +0.05 =0.087 +0.05 =13.7% Note=$35 per stock and floating is $2 =$35 *6/100=$2, ::: 6/100=0.06