A B 11 12 13 14 15 1234567890123456 Time until E PV of CF Payment (Discount rate = G Column (C) times Period (Years) Cash Flow 5% per period) Weight Column (F) A. 8% coupon bond 1 0.5 40 =D4/(1+$B$16)^B4 =E4/E$8 =F4*C4 2 1 40 =D5/(1+$B$16)^B5 =E5/E$8 =F5*C5 3 1.5 40 =D6/(1+$B$16)^B6 =E6/E$8 =F6*C6 4 2 1040 Sum: =D7/(1+$B$16)^B7 =E7/E$8 =SUM(E4:E7) =F7*C7 =SUM(F4:F7) =SUM(G4:G7) 10 B. Zero-coupon 1 0.5 0 2 1 0 =D10/(1+$B$16)^B10 =E10/E$14 =D11/(1+$B$16)^B11 =E11/E$14 =F10*C10 =F11*C11 3 1.5 0 =D12/(1+$B$16)^B12 =E12/E$14 =F12*C12 4 2 1000 Sum: =D13/(1+$B$16)^B13 =E13/E$14 =SUM(E10:E13) =F13*C13 =SUM(F10:F13) |=SUM(G10:G13) 16 Semiannual int rate: 0.05 Spreadsheet 16.2 Spreadsheet formulas for calculating duration