A free bond price, DV01 and duration calculator in Excel, on a trading desk
Price, DV01 and modified duration for the 2, 7, 10 and 30-year notes, yield shocks, the curve and roll-down, a DV01-neutral steepener, a client trade hedged with Treasuries, risk limits and the desk's P&L: eight sheets, no macros.
The file prices four Treasuries and a corporate bond on a coupon date and measures their rate risk. The 10-year note at a 4.25% yield and coupon prices at par with a DV01 of $808 per $1 million and a modified duration of 8.08; the 30-year bond carries twice the duration at 16.05. A 100 basis point rise costs the 10-year 7.70 points, a fall gains 8.48: the asymmetry is convexity. A DV01-neutral steepener sells $10 million of 10-years against $42.4 million of 2-years and makes $80,779 if the curve steepens 10 basis points.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Bond_Pricing_DV01_Duration_Model.xlsx (24 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Fixed Income Trader. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Eight sheets. Blue cells on a light blue background are inputs. Yields in per cent, semiannual coupons.
Read Me and Inputs
The curve, the Treasury coupons, the corporate bond, the client trade, day two, repo, risk limits and the desk's economics.
Bond Pricing
Price, DV01 per 100 and per $1 million, and modified duration for each bond, and the 10-year under yield shocks from -100 to +100 basis points.
Curve and Roll
The 2s10s, 2s5s and 5s30s slopes, the roll-down of the 10-year over a year, and a DV01-neutral steepener.
Client Trade
A request for quote on a corporate bond: edge against mid, the Treasury hedge and its ratio, day two, the resale and carry.
Risk
DV01 and CS01 limits and headroom, one-day VaR and the rate and spread stresses.
Desk P&L and Checks
A year of flow, risk management, portfolio trading and carry against direct costs; then the book's figures against the formulas: all OK.
What the worked case returns
Measure
Value
10-year: price, DV01 per $1m
100.00, $808
10-year modified duration
8.08
2-year and 30-year modified duration
1.91 and 16.05
10-year: +100 bp and -100 bp
-7.70 and +8.48 points
Roll-down value of the 10-year, one year
$3,715 per $1m
Steepener: 2-years against $10m of 10-years
$42.4m
Steepener P&L, 10 bp steeper
$80,779
Client trade: edge against mid
$57,751
Treasury hedge for the client trade
$19.2m of 7-years
DV01 is the dollar change for one basis point, and modified duration is the same thing as a percentage of price. Matching DV01, not face amount, is what makes the steepener neutral to a parallel move: it takes $4.24 of 2-years to offset $1 of 10-years, because the 2-year moves only a quarter as much per basis point.
How to use it on your own bonds
Enter your coupons and yields on Inputs.
Read price, DV01 and duration on Bond Pricing, then the shock table for convexity.
Size a curve trade on Curve and Roll by DV01, not by notional.
For a trade with a hedge, check the remaining DV01 and CS01 against your limits on Risk.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference: bond prices are present values of semiannual cash flows, and DV01 is the price change for a one basis point move.
What is the difference between DV01 and duration?
DV01 is the change in price, in dollars, for a one basis point change in yield. Modified duration is that change as a percentage of price, for a 100 basis point move. At par they give the same number in different units: 8.08 and $808 per $1 million on the 10-year.
Does it calculate convexity?
Not as a separate statistic. The yield shock table shows convexity at work: the price gain for a fall in yield is larger than the loss for the same rise.
The rest of the files
This template is the companion workbook for The Fixed Income Trader; the companion page also carries the other files that go with it. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from The Fixed Income Trader. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.