need the before tax cost with excel RATE formula please

M3 f AA B D G H 17 18 19 20 21 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. e. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights shown in the table above. 1. Calculate the firm's weighted average cost of capital using new common stock and the capital structure weights shown in the table above. 22 23 24 25 Solution a. Calculate the after-tax cost of debt. 26 27 28 29 30 s 31 32 33 Par value Coupon rate Maturity (yrs) Discount Flotation cost Tax Rate Annual coupon payment Net proceeds Before-tax cost of debt After-tax cost of debt 1,000 6.50% 10 20.00 2.00% 21% S 34 35 36 37 38. 39 un Calenlate the cost of referred stock