Please help fill out this cost of capital spreadsheet with the given information in the last two pictures! I already tried to fill out a little of the spreadsheet but let me know if my calculations are wrong. Thanks im advance!

2 Cost of Capital Spreadsheet Example 4 Module IX Name: Date: 12/6/2021 weight 0.3 0.1 0.6 1 cost 0.05 0.1 0.18 WACC 0.015 0.01 0.108 13.30% 3.42% 6.84% 4.10% 13.70% 11.40% 12.60% 6 71 Capital Structure 8 Bonds $ 3,000,000 9 Preferred stock $ 1,000,000 10 Common stock $ 6,000,000 11 Total $ 10,000,000 12 13 11-1 After-tax cost of bonds: Semi-Annual YTM= 14 Annual YTM= 15 Cost of Bonds 16 II-2 Cost of retained earnings: 17 DCF approach 18 CAPM approach 19 Average 20 11-3 Cost of preferred stock 21 11-4 Cost of New Common Stock 22 23 11-5a Amount Weight 24 Bonds 3,000,000 25 Preferred Stock 1,000,000 26 Retained earnings 6,000,000 27 Total 10,000,000 28 29 11-5b Amount Weight 30 Bonds 3,000,000 31 Preferred Stock 1,000,000 32 New Common Stock 6,000,000 33 Total 10,000,000 34 Cost WACC Cost WACC Cost of Capital Exercises: 1. The capital structure for 3C Corporation is provided below. The company plans to maintain it capital structure in the future. If the firm has a 5% after-tax cost of debt, a 10% cost of preferred stock, and a 18% cost of common stock, what is 3C's weighted cost of capital? Capital structure $3,000,000 1,000,000 6,000,000 $10,000,000 Bonds Preferred stock Common stock Total II. Comprehensive Cost of Capital Problem. a. b. The Triple Seven Systems, Inc. (TSS), is starting its planning process for next year. Jack Tripper, the firm's CFO, calculates the weighted cost of capital each year to be used as a discount rate in the NPV analysis. You have been asked to help Mr. Tipper to compute the firm's cost of capital. You have collected the following data to do the job: TSS bonds carry a coupon rate of 5% with semi-annual payments, a $1,000 par, a AAA rating, and a maturity of 10 years. The current price of the TSS bonds is $900. TSS preferred stock currently pays a 10% dividend rate on a $100 par value and has a market value of $90 per share. TSS common shares paid a dividend of $4 last year. Dividends have grown at a rate of 6% per year and are expected to continue for a foreseeable future. The market price of the stock is $50 and there would be a flotation cost of 5% on a new issue. d. TSS' capital structure is as follows: Bonds (3,000 bonds outstanding) 3,000,000 Preferred stock (10,000 shares outstanding) 1,000,000 Common stock (600,000 shares) 6,000,000 c. 1 e. f. The firm's average tax rate is 35% and its marginal tax rate is 40%. TSS uses five companies for peer-group comparisons. These five firms along with their betas after being adjusted for financial leverage effects are as follows: 49 Peer Group Firms Beta Peer Group Firms Beta Western Pacific 1.30 Southwestern, Inc. 1.26 Telectroics, Inc. 1.32 Ecological System, Inc 1.28 Pacific United 1.34 Treasury bond currently yields 3% (rre) and the expected market return (rm) is 10%. Assignments: 1. 2. Compute the after-tax cost of bonds. What is the cost of retained earnings, first based on the DCF approach and then based on the CAPM approach using an average beta of peer groups as TSS' beta. Calculate the average of the two approaches. What is TSS' cost of preferred stock? 3. 4. Calculate the cost of new stock according to the DCF approach. 5. Calculate the weighted cost of capital for TSS assuming (a) only internal equity (retained earnings) is used in financing the equity portion of the new investment, and then (b) new common stock is issued to finance the firm's investments