Problem 14-6 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across 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: a. 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 Tax rate 8.00% 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 $25. Given Data: Last year dividend Market price of stock Dividend growth rate $2.05 $25.00 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 Market Value Number of years Coupon rate Tax rate $1,000.00 $1,150.00 201 12.00% 34.00% 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