F G C D Complete the steps below using cell references to given data or previous calculation. In some cases, a simple cell reference is all you need. To copy/paste a formule acron a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Compute the cost of capital for the firm for the following: Currently, new bond issues with a credit rating and maturity similar to those of the firm's outstanding debt are selling to yield 8 percent, while the borrowing firm's corporate tax rate is 34 percent. Given Data: Yield Rate 8.00% Tax rate 34.00% Cost of debt capital b. Common stock for a firm that paid a $2.05 dividend last year. The dividends are expected to grow at a rate of 5 percent per year into the foreseeable future. The price of this stock is now S25. Given Data: Last year dividend $2.05 Market price of stock $25.00 Dividend growth rate 5.00% Expected dividend (end of the year) Cost of equity capital c. A bond that has a $1,000 par value and a coupon interest rate of 12 percent with interest paid semiannually. A new issue would sell for $1.150 per bond and mature in 20 years. The firm's tax rate is 34 percent Given Data: Face value $1,000.00 Market Value $1.150.00 Number of years 20 Coupon rate 12.00% Tax rate 34.00% 14-6 D 6 H Number of payments per year Number of payment periods Semiannual coupon payment Yield to maturity After-tax cost of debt d. A preferred stock paying a 7 percent dividend on a $100 par value. If a new issue is offered, the shares would sell for $85 per share, Given Data: Par value $100.00 Market Value $85.00 Dividend rate 7.00% Cost of preferred stock 3 Requirements 1 Start Excel 2 In cell D11, by using cell references, calculate the cost of debt capital. (1 pt.) In cell D19, by using cell references, calculate the expected dividend at the end of the year. (1 pt.) 4 In cell D20, by using cell references, calculate the cost of equity capital. (1 pt.) 5 In cell D30, enter the number of payments made per year. (1 pt.) In cell D31, by using cell references, calculate the number of periods when dividends are puid. (1 pt.) 7 In cell D32, by using cell references, calculate the semiannual coupon payment. (1 pt.) In cell 034, by using cell references and the Excel RATE function, calculate the bond's yield to 8 maturity 6 Note: Do not enter any value for the Guess argument of the Excel RATE function. (1 pt.) 9 In cell D35, by using cell references, calculate the after-tax cost of debt. (I pt.) 10 In cell D43, by using cell references, calculate the cost of preferred stock. (1 pt.) 11 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed