Bond analytics: seven bonds, one curve
The case. Seven plain fixed-coupon bonds went in as a basket, read from a position report image and scaled to a notional of 100 per line, with a key-rate curve and a spread in basis points for each bond. There are no calls, puts, sinks or floaters, and no bootstrapped curve: the key rates are inputs, not instruments. The first bond's spread is 433.77 basis points, and portfolio value is 663.57. Everything else is computed twice: cash flows, prices, yields, durations, convexities, key-rate durations and the P&L under five curve shocks. This is a synthetic case for method demonstration, not investment, tax or legal advice.
Seven bonds, 55 checks. Modified duration 5.090, effective duration 5.083, convexity 35.675 and DV01 (the value change for a one-basis-point move) 0.337 per basis point. Every analytic number is computed from its formula and again by bumping the curve. The mechanics of keeping both columns in one sheet are in the Excel-for-finance guide.
| Measure | Result |
|---|---|
| Positions | 7 |
| Portfolio value | 663.57 |
| Macaulay duration | 5.354 years |
| Modified duration, flat yield | 5.090 years |
| Effective duration, spot curve | 5.083 years |
| Convexity, flat yield | 35.675 |
| Effective convexity, spot curve | 35.605 |
| Sum of key-rate durations | 5.083 years |
| DV01 | 0.337 per basis point |
Fifty-five checks back the workbook. All pass, and the tightest of them sits at 43.6% of its tolerance.
The pass count is not the interesting number; the tightest control is. The 55 checks are conditional on the curve: they test the analytics that follow from it, not the curve itself.
What fifty-five checks show
The checks run in six groups of seven, one group of eight key-rate checks, and five shock tests.
| Group | Checks | What it establishes |
|---|---|---|
| Cash flows | 7 | Nominal flows equal coupon × periods + face |
| Price | 7 | Price equals the sum of discounted cash flows, and an inline rebuild agrees |
| Yield | 7 | Solved yield equals the curve rate at maturity plus the spread |
| Solver | 7 | The price at the solved yield reproduces the curve price |
| Modified duration | 7 | Closed form against a 1bp finite difference |
| Convexity | 7 | Closed form against a 1bp finite difference |
| Key-rate duration | 8 | Key rates sum to the effective duration, including the portfolio |
| Shock P&L | 5 | Full reprice against the duration-plus-convexity prediction |
Two of those groups are the heart of the topic.
Duration and convexity are each computed two ways. The closed forms are the textbook expressions; the finite differences bump the curve by one basis point and reprice. Agreement to the stated tolerance means the analytic formula and the numerical behavior of the bond agree. That is a stronger statement than either one alone. The same discounting machinery is in the derivatives engine.
Key-rate durations have to sum to the effective duration. This is an additivity property, not an identity by definition: it holds when the curve is built from key rates and the bumps are properly isolated. If a key-rate decomposition misses a segment, the 30-year point for instance, the sum falls short of the effective duration. The gap is exactly the exposure that is not being measured.
Macaulay vs modified vs effective duration
Macaulay duration is 5.354 years. Modified duration is 5.090. Effective duration is 5.083.
Three numbers, all called "duration", within about 5.5% of each other on the same portfolio. Each answers a slightly different question:
- Macaulay is the weighted average time to the cash flows, in years.
- Modified divides Macaulay by one plus the yield per period, giving the percentage price change per unit change in yield.
- Effective reprices the bond under a small parallel shift of the spot curve, which captures any optionality and does not assume a flat yield.
Quoting one and labeling it another is a 0.26-year error on this portfolio and a much larger one on anything with embedded options. The workbook computes all three and keeps them distinct, which is the only way the distinction survives.
The rest of the bond series
The yield solve is the solver and its residual. Duration is three numbers with the same name. Convexity is the second-order term. Key-rate duration is the decomposition that has to sum to the whole. The credit spread is an input with one right way to enter. The shock test is where the first two orders meet a full reprice. The integration article is how the 55 checks are arranged.
What the numbers do not cover
Six limits matter, and the workbook's own note is the sharpest:
> A PASS means the two values agree within the stated tolerance. It evidences the arithmetic and the wiring of the model, not the correctness of the inputs or of the market convention.
The curve is a set of key rates, not a market curve. No bootstrapping from instruments, no interpolation convention beyond what the sheet does, no basis.
One curve for discounting and for projecting. No overnight indexed swap (OIS) discounting, no dual curve.
Accrual conventions are simple. No odd periods, no business-day calendars, no settlement-date effects.
Credit is a spread, not a model. The spread at maturity is an input; there is no default probability, no recovery, no migration.
The shock tests are parallel and instantaneous. Five curve shocks, one repricing each: not a scenario set, not a path.
All seven bonds are plain. No calls, puts, sinks or floaters, so effective duration never has to diverge from modified for optionality reasons.
The useful reading is that this workbook is a measurement instrument, not a risk system. It measures duration, convexity and key-rate exposure to a precision it can demonstrate; it does not tell you what the curve will do. The reporting side of these analytics is in the financial-reporting walkthrough.
The closed form and the bumped price sit side by side in the Mica workbook, so the finite-difference check runs in the sheet rather than in a review.
Frequently asked questions
What is the difference between Macaulay, modified and effective duration?
Macaulay duration is the weighted average time to cash flows; modified duration divides it by (1 + yield per period) to give the percentage price change for a yield change; effective duration reprices the bond for a small parallel shift. On this portfolio they are 5.354, 5.090 and 5.083 years.
How do you check duration in Excel?
Compute it in closed form from the bond's cash flows, then reprice the bond after a one-basis-point shift and compare the two. Each bond in this workbook is checked that way, and the closed-form and finite-difference durations agree.
What is DV01 and how is it calculated?
DV01 is the change in value for a one-basis-point move in yields. Here it is 0.337 per basis point on a portfolio value of 663.57.
Why does effective duration differ from modified duration?
The two coincide only when cash flows do not change when yields change. A gap points to embedded options or floating coupons; here the gap is 0.007 years.