Suppose the interest rate decreases to 9%. What will happen to the price and duration of each bond in Spreadsheet 11.1?

Transcribed Image Text:

SPREADSHEET 11.1 Calculation of the duration of two bonds using Excel spreadsheet excel Please visit us at www.mhhe.com/bkm 1 Interest rate: 2 3 4 1 2 3 4 2|3|4 5 6 A. 8% coupon bond 7 12 ■ 8 9 10 11 B. Zero-coupon 13 A 5 6 A. 8% coupon bond 7 8 9 10 11 B. Zero-coupon bond 12 13 14 15 16 *Weight Present value of each payment (column D) divided by bond price A Interest rate: 14 Sum: Sum: Sum: Sum: 0.1 1 2 3 1 2 3 B Time until Payment (Years) 1 2 3 B 10% 1 2 3 me until Payment (Years) 80 80 1080 0 OOF 0 C 1000 Payment Discounted Payment at 10% C 80 80 1080 Payment D 0 0 1000 72.727 66.116 811.420 950.263 0.000 0.000 751.315 751.315 D Payment Discounted at 10% -C6/(1+$B$1)^B6 =C7/(1+$B$1)^B7 -C8/(1+$B$1)^B8 =SUM(D6:D8) E Weight 0.0765 0.0696 0.8539 1.0000 0.0000 0.0000 1.0000 1.0000 F Column (B) X Column (E) E 0.0765 0.1392 2.5617 2.7774 0.0000 0.0000 3.0000 3.0000 Weight -D6/D$9 =D7/D$9 =D8/D$9 -D9/D$9 F Column (B) times Column (E) -E6*B6 =E7*B7 -E8*B8 -SUM(F6:F8) -C11/(1+$B$1)^B11 -D11/D$14 -E11 B11 |=C12/(1+$B$1)^B12 =D12/D$14 |=E12*B12 -C13/(1+$B$1)^B13 -D13/D$14 -E13*B13 -SUM(D11:D13) -D14/D$14 -SUM(F11:F13)