Could some kind soul put this explanation into an excel spreadsheet for me please? I kept getting hung up on the YTM for Bond $4,$ and even with this explanation I$\text{'}$m still not getting it$.$ Thank you so much! Ying Import has several bond issues outstanding, each making semiannual interest

payments. The bonds are listed in the following table. If the corporate tax rate is $32$

percent, what is the aftertax cost of Ying's debt? Multiple Choice

$1\mathrm{.}66\%$

$1\mathrm{.}81\%$

$2\mathrm{.}54\%$

$1\mathrm{.}64\%$

$1\mathrm{.}72\%$ To find the aftertax cost of equity for the company, we need to find the weighted average of the four

debt issues. We will begin by calculating the market value of each debt issue, which is:

$M{V}_1=1\mathrm{.}09($$$24,000,000)$

$M{V}_1=$$$26,160,000$

$M{V}_2=1\mathrm{.}18($$$39,000,000)$

$M{V}_2=$$$46,020,000$

$M{V}_3=1\mathrm{.}11($$$45,000,000)$

$M{V}_3=$$$49,950,000$

$M{V}_4=1\mathrm{.}25($$$63,000,000)$

$M{V}_4=$$$78,750,000$

So$,$ the total market value of the company's debt is:

$M{V}_D=$$$26,160,000+46,020,000+49,950,000+78,750,000$

$M{V}_D=$$$200,880,000$

The weight of each debt issue is:

$w_1=$$$26,160,\frac{000}{\text{\$}}200,880,000$

$w_1=0\mathrm{.}1302,or13\mathrm{.}02\%$

$w_2=$$$46,020,\frac{000}{\text{\$}}200,880,000$

$w_2=0\mathrm{.}2291,or22\mathrm{.}91\%$

$w_3=$$$49,950,\frac{000}{\text{\$}}200,880,000$

$w_3=0\mathrm{.}2487,or24\mathrm{.}87\%$

$w_4=$$$78,750,\frac{000}{\text{\$}}200,880,000$

$w_4=0\mathrm{.}392,or39\mathrm{.}2\%$ Next, we need to find the YTM for each bond issue. The YTM for each issue is:

$P_1=$$$1,090=$$$28(PVIF{A}_{R\%,14})+$$$1,000(PVI{F}_{R\%,14})$

$R_1=2\mathrm{.}054\%$

$)1)1)2)R$

$)R$

$YT{M}_2=2\mathrm{.}1348\%2$

$YT{M}_2=4\mathrm{.}27\%$

$P_3=$$$1,110=$$$26(PVIFAR\%,42)+$$$1,000(PVIFR\%,42)$

$R_3=2\mathrm{.}15\%$

$YT{M}_3=2\mathrm{.}15\%2$

$YT{M}_3=4\mathrm{.}3\%$

$P_4=$$$1,250=$$$28(PVIFAR\%,72)+$$$1,000(PVI{F}_R\%,72)$

$R_4=-0\mathrm{.}058\%$

$YTM4=-0\mathrm{.}058\%2$

$YTM4=-0\mathrm{.}12\%$

The weighted average YTM of the company's debt is thus:

$YTM=0\mathrm{.}1302(0\mathrm{.}0411)+0\mathrm{.}2291(0\mathrm{.}0427)+0\mathrm{.}2487(0\mathrm{.}043)+0\mathrm{.}392(-0\mathrm{.}0012)$

$YTM=0\mathrm{.}025,or2\mathrm{.}54\%$

And the aftertax cost of debt is:

$R_D=0\mathrm{.}025(1-0\mathrm{.}32)$

$R_D=0\mathrm{.}0172,or1\mathrm{.}72\%$