Effective Duration & Key-Rate Duration (Callable Bond Finite Difference)

Start with repriced values, not a promised call-price cap

Effective duration measures sensitivity to a benchmark-curve shift. It is useful when options can change future cash flows; key-rate duration instead measures sensitivity to a specified maturity region. A callable bond can exhibit negative convexity, but its price is not automatically capped at the stated call price at every valuation date.

This worksheet consumes illustrative prices. It does not price a callable bond or mortgage security. Obtain base, up-shock and down-shock values from the same valuation model, with consistent cash-flow assumptions, price units and settlement date. Hold other model parameters, including the option-adjusted spread, fixed when measuring curve sensitivity.

Enter the shock in decimal yield units

B2 is the base price 100; B3 and B4 are prices after a parallel down/up shock, 102.45 and 97.65. B5 is the positive shock magnitude: 0.005 means 50 basis points, not 0.5 basis points. Negative benchmark yields are possible; B5 is a magnitude and must still be positive.

B7 rejects missing, text or negative prices and nonpositive base price or shock. B8 and B9 use the same validation. Results remain unrounded in the cells.

=IF(COUNT(B2:B5)<>4,"Check numeric inputs",IF(OR(B2<=0,B3<0,B4<0,B5<=0),"Check prices and shock","OK"))

Calculate effective duration

B8 returns 4.8 for the initial price pair. This is a local price-sensitivity measure, not a time-to-maturity forecast. A small decimal yield move dy gives a first-order relative price estimate of −B8 × dy.

=IF(B7<>"OK",B7,(B3-B4)/(2*B2*B5))

Calculate effective convexity

B9 returns 40 initially. Change B3 to 101.10, keeping the other parallel inputs fixed: duration becomes 3.45 and convexity becomes −500. These are illustrative scenario prices, not an independently calibrated callable-bond model.

For a small parallel move dy, the second-order relative price estimate is −duration × dy + 0.5 × convexity × dy². Convexity measures curvature over the selected shock; its sign is not a definitive classification test for a bond.

=IF(B7<>"OK",B7,(B3+B4-2*B2)/(B2*B5^2))

Calculate key-rate duration for each tenor

E2:F4 contain separate down/up prices for isolated 2Y, 5Y and 10Y shocks of the same magnitude B5. G2:G4 return 1, 1.5 and 2.3. Each pair must come from repricing that tenor shock, with a defined interpolation rule between tenor nodes. They cannot be derived from the single parallel price pair.

=IF(B7<>"OK",B7,IF(COUNT(E2:F2)<>2,"Check key prices",IF(MIN(E2:F2)<0,"Check key prices",(E2-F2)/(2*$B$2*$B$5))))

Reconcile the key-rate sum

G6 sums the three sensitivities and G7 subtracts parallel duration. The supplied synthetic scenario gives 4.8 and zero respectively. If you change B3 to test the callable scenario, the original key-price pairs are unchanged: the gap becomes 1.35. Reprice all key scenarios before treating them as sensitivities of that changed instrument.

A key-rate decomposition can reconcile to parallel sensitivity when its shocks collectively span the same parallel move. For finite shocks on nonlinear prices, interpolation and cross-effects can leave a residual; do not force a sum to equal parallel duration. Compare several shock sizes using newly repriced values, rather than changing B5 alone.

=IF(COUNT(G2:G4)<>3,"Check key-rate inputs",SUM(G2:G4))

Try controlled changes

Scale every price, including all key-price pairs, by the same positive factor: sensitivities should be unchanged. Replace a key price with text to check that its result and the total show a validation message. Set B5 to zero to verify that both duration and convexity stop before division.

Very small shocks amplify numerical cancellation in convexity; large shocks capture nonlinear behavior beyond a local sensitivity. This arithmetic exercise does not recommend a trading or hedging decision.

Further reading

• CFA Institute — Curve-Based and Empirical Fixed-Income Risk Measures
• Microsoft Support — ROUND function