Looking for the answer in a re-usable excel formula / calculator. I've been able to figure out all the questions except for the last one cost of debt. I've attached what I've done so far in excel.

Shanken Corp. issued a 15-year, 8 percent semiannual bond 3 years ago. The bond currently sells for 96 percent of its face value. The book value of the debt issue is $60 million. In addition, the company has a second debt issue on the market, a zero coupon bond with 10 years left to maturity; the book value of this issue is $35 million and the bonds sell for 51 percent of par. The company?s tax rate is 35 percent.

What is the company's total book value of debt? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, e.g., 1,234,567.)

Total book value

$95000000

What is the company's total market value of debt? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, e.g., 1,234,567.)

Total market value

$75450000

What is your best estimate of the aftertax cost of debt? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.)

Cost of debt

%

6) Shanken Corp. issued a 15-year, 8 percent semiannual bond 3 years ago. The bond currently sells for 96 percent of its face value. The book value of the debt issue is $60 million. In addition, the company has a second debt issue on the market, a zero coupon bond with 10 years left to maturity; the book value of this issue is $35 million and the bonds sell for 51 percent of par. The company's tax rate is 35 percent. What is the company's total book value of debt? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, e.g., 1,234,567.) Total book value $ 95000000 What is the company's total market value of debt? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, e.g., 1,234,567.) $ Total market value 75450000 What is your best estimate of the aftertax cost of debt? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) Cost of debt % Chapter 14 Question 7 Input Area: Settlement Maturity Coupon rate Price (% of par) Payments per year Tax rate 01/01/00 01/01/12 0% 96 2 35% Output Area: a. Pretax cost of debt 0.34% b. Aftertax cost of debt 0.22% c. The aftertax rate is more relevant because that is the actual cost to the company. Input Area: Book value of debt issue (1) Second issue Settlement date Maturity date Annual coupon rate Coupons per year Bond price (% of par) Tax rate Book value debt issue (2) $ 60,000,000 01/01/00 01/01/12 0% 2 51 35% $35,000,000 Output Area: Book value of debt $ 95,000,000 Market value of first bond $ 57,600,000 Market value of second bond $ 17,850,000 Market value of debt $ 75,450,000 Pretax cost of second issue 5.691% Aftertax cost of second issue 3.699% Aftertax cost of debt 1.04%