Free Excel templates

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.

Get the book on Apple Books → Or on Google Play

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
10-year: price, DV01 per $1m100.00, $808
10-year modified duration8.08
2-year and 30-year modified duration1.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

  1. Enter your coupons and yields on Inputs.
  2. Read price, DV01 and duration on Bond Pricing, then the shock table for convexity.
  3. Size a curve trade on Curve and Roll by DV01, not by notional.
  4. 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.

Open the companion files →

Worked question on the same ground: G-spread vs I-spread: how do you calculate each on a bond?

Other free templates

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.