Question

Consider two 30-year maturity bonds. Bond A has a coupon rate of 4%, while bond B has a coupon rate of 12%. Both bonds pay their coupons semiannually. a. Compute the prices of the two bonds (using Excel's bond price function) at each interest rate. (Do not round intermediate calculations. Round your answers to 2 decimal places.) Yield 2% 3% 4% 5% 6% 7% 8% Price A 1,449.55 1,196.90 1,000.00 845.46 723.24 625.83 547.53 484.05 432.12 389.26 353.54 Price B 3,247.75 2,772.11 2,390.44 2,081.80 1,830.27 1,623.62 1,452.47 1,309.57 1,189.29 1,087.25 1,000.00 9% 10% 11% 12% 13% 323.52 924.84 14% 859.61 298.04 276.23 15% 802.61:

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

B D E F G H I J K L M Bond A $1,000 Bond B $1,000 30 12% 30 4% Face value Years to maturity Coupon rate Coupon frequency Settlement date Maturity date 2 1/1/2020 1/1/2050 1/1/2050 Yield 2% 3% Yield 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% Price A $1,449.55 $1,196.90 $1,000.00 $845.46 $723.24 $625.83 $547.53 $484.05 $432.12 $389.26 $353.54 $323.52 $298.04 $276.23 Price B $3,247.75 $2,772.11 $2,390.44 $2,081.80 $1,830.27 $1,623.62 $1,452.47 $1,309.57 $1,189.29 $1,087.25 $1,000.00 $924.84 $859.61 $802.61 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% Bond A |% Capital Gain 164.74% 118.60% 82.64% 54.41% 32.09% 14.30% 0.00% -11.59% -21.08% -28.91% -35.43% -40.91% -45.57% -49.55% Yield 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% Bond B % Capital Gain 123.60% 90.86% 64.58% 43.33% 26.01% 11.78% 0.00% -9.84% -18.12% -25.14% -31.15% -36.33% -40.82% -44.74% 26 d. Bond A 28 e. Bond B

Cell reference -

ДА В с D | E F G H I J K L Bond A Bond B 1000 30 0.12 nm + ONEM Face value 1000 Years to maturity 30 Coupon rate Coupon rate 0.04 Coupon frequency 2 Settlement date =DATE(2020,1,1) Maturity date =DATE(2050,1,1) =C8 Yield c. | 0.02 0.03 0.04 0.05 0.06 0.07 16 17 Price A =PRICE($C$ 7,$C$8,$C$5,B11,100,$C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B12,100,$C$6,3)%*$C$3 =PRICE($C$7,$C$8,$C$5,B13,100,$C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B14,100,$C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B15, 100, $C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B16,100,$C$6,3)%*$C$3 =PRICE($C$7,$C$8,$C$5,B17,100,$C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B18,100,$C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B19, 100, $C$6,3)%*$C$3 =PRICE($C$ 7,$C$8,$C$5,B20,100,$C$6,3)%*$C$3 =PRICE($C$7,$C$8,$C$5,B21,100,$C$6,3)%*$C$3 | =PRICE($C$7,$C$8,$C$5,B22,100,$C$6,3)%*$C$3 =PRICE($C$7,$C$8,$C$5,B23,100,$C$6,3)%*$C$3 =PRICE($C$7,$C$8,$C$5,B24,100,$C$6,3)%*$C$3 Price B =PRICE($D$ 7,$D$8, $D$5,B11,100,$D$6,3)%*$D$3 =PRICE($D$7,$D$8, $D$5,B12, 100, $D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B13,100,$D$6,3)%*$D$3 =PRICE($D$ 7,$D$8, $D$5,B14,100,$D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B15,100, $D$6,3)%*$D$3 =PRICE($D$ 7,$D$8, $D$5,B16, 100, $D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B17,100,$D$6,3)%*$D$3 =PRICE($D$ 7,$D$8, $D$5,B18, 100, $D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B19, 100, $D$6,3)%*$D$3 =PRICE($D$ 7,$D$8, $D$5,B20, 100, $D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B21,100,$D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B22,100,$D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B23,100, $D$6,3)%*$D$3 =PRICE($D$7,$D$8,$D$5,B24,100,$D$6,3)%*$D$3 Yield 0.02 0.03 0.04 0.05 0.06 0.07 0.08 Bond A % Capital Gain =C11/$C$17-1 =C12/$C$17-1 =C13/$C$17-1 =C14/$C$17-1 =C15/$C$17-1 =C16/$C$17-1 =C17/$C$17-1 =C18/$C$17-1 =C19/$C$17-1 =C20/$C$17-1 =C21/$C$17-1 =C22/$C$17-1 =C23/$C$17-1 =C24/$C$17-1 b. Yield 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.02 Bond B % Capital Gain =D11/$D$17-1 =D12/$D$17-1 =D13/$D$17-1 =D14/$D$17-1 =D15/$D$17-1 =D16/$D$17-1 =D17/$D$17-1 =D18/$D$17-1 =D19/$D$17-1 =D20/$D$17-1 =D21/$D$17-1 =D22/$D$17-1 =D23/$D$17-1 =D24/$D$17-1 10.09 0.1 20 0.11 0.13 0.14 0.14 0.15 26 d. Bond A 28 e. Bond B

Hope it will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.