Mastering Bond Mathematics through Excel

1 How this workbook guide works

Bond mathematics is easier to understand once you have built it yourself in a spreadsheet. Each section below matches one sheet of the companion Excel workbook. Every sheet follows the same steps: inputs → cash flows → present value → yield → sensitivity.

2 Notation and Excel conventions

The core equation behind every sheet: price equals the present value of the promised cash flows.

3 Sheet 1: Pricing a coupon bond three ways

Start with a 10-year, 7% semiannual bond priced at a 7.50% yield. Rebuilding the price three different ways shows that each Excel method computes the same thing.

① The cash-flow table: the method that shows every step

Put one row for each half-year: period, cash flow (3.50 coupon, and 103.50 in period 20), discount factor and present value. Adding up the PV column gives 96.5259. This is the slowest method, but it shows every step, and you will reuse the same table for duration and convexity on Sheet 8.

② PV: one line, valid only on a coupon date

=-PV(3.75%,20,3.5,100) treats the coupons as an annuity and the face value as a lump sum. It needs a whole number of periods, so it only works when settlement falls on a coupon date.

③ PRICE: real dates, between coupon dates

PRICE(settlement, maturity, rate, yld, redemption, frequency, basis) works from actual dates and returns the clean price per 100. On a coupon date all three methods agree, as the sheet shows. Between coupon dates only PRICE is still correct (see Sheet 7).

4 Sheet 2: Zero-coupon bonds

A zero-coupon bond has only one cash flow, the face value at maturity. Its price is a single discount factor, and its Macaulay duration equals its maturity. Treasury bills, STRIPS and deep-discount bonds are all priced this way.

5 Sheet 3: Floating-rate notes (FRNs)

An FRN pays reference rate + quoted margin (QM). The reference rate is a benchmark such as a 3-month T-bill rate, MIBOR or SOFR, and the coupon is reset each period. The coupon follows the market, so the price stays close to par. Two margins matter, and confusing them is the most common mistake:

Hull’s approach: valuing the FRN as a swap leg

John Hull (Options, Futures and Other Derivatives, interest rate swaps chapter) values a floating-rate bond with one argument. If the coupon resets to the market rate and you discount on that same curve, with no credit spread, the bond is worth exactly par immediately after every reset. Between resets only the next coupon k* is already fixed, and everything after it is worth par at the next payment date. So you discount just one cash flow:

Here L is the notional, k* the floating payment fixed at the last reset, t* the time to the next payment and r* the continuously compounded zero rate for t*.

Figure 3 above already uses Hull’s logic: the FRN is priced as (face + next coupon) discounted to the next reset, which is why it barely moves when rates shift.

Download the Hull FRN & Swap Valuation workbook (.xlsx)

6 Sheet 4: Convertible bonds

A convertible bond lets the holder swap the bond for a fixed number of shares (the conversion ratio). When the share price is low it behaves like a bond, and when the share price is high it behaves like the stock. In Excel, value it from two sides and take the higher.

7 Sheet 5 (part 1): Yield to maturity with RATE, YIELD and Goal Seek

Yield to maturity (YTM) is the single discount rate that makes the PV of the promised cash flows equal to the price, which is the bond’s internal rate of return. There is no closed-form formula, so Excel solves for it by iteration. Three ways to get it:

8 Sheet 5 (part 2): Yield to call and yield to worst

A callable bond can be redeemed early by the issuer, at a set call price, on set dates. Yield to call (YTC) uses the same YIELD function with two changes: maturity = call date and redemption = call price. Yield to worst (YTW) is the lowest of the YTM and every YTC. It is the yield you can count on whatever the issuer decides.

9 Sheet 6 (part 1): Money-market instruments and T-bills

Money-market instruments (T-bills, commercial paper, certificates of deposit) mature in a year or less and are usually issued at a discount. They pay no coupon, and the return is the difference between the purchase price and face value. The maths is simple. What trips people up is the different yield quotation conventions.

Indian T-bills: yield on price, 365-day year

US T-bills: discount basis, 360-day year, with dedicated Excel functions

10 Sheet 6 (part 2): T-notes, T-bonds and quotes in 32nds

US Treasury prices are quoted in 32nds: “98-16” means 98 + 16/32 = 98.50. Excel converts with DOLLARDE. Type the quote as 98.16 and the fraction denominator as 32, and DOLLARFR converts back.

11 Sheet 7: Accrued interest, clean price and dirty price

Bonds change hands between coupon dates. The seller has earned interest for part of the current period, so the buyer pays it on top of the quoted price. That gives three related numbers:

where A is the number of days from the last coupon to settlement and E is the number of days in the coupon period, both under the bond’s day-count basis. Excel’s coupon-calendar functions give you every piece:

12 Sheet 8 (part 1): Macaulay duration

Macaulay duration is the PV-weighted average time to receive the bond’s cash flows, measured in years. Each period’s weight is its share of the total price.

13 Sheet 8 (part 2): Modified duration and DV01

Macaulay duration is measured in time. Modified duration converts it into price sensitivity: the approximate percentage change in price for a 1-percentage-point (100 bp) change in yield.

14 Sheet 8 (part 3): Convexity and the short-cut formulas

The price–yield curve is convex, so duration alone underestimates price gains and overestimates price losses. Convexity measures the curvature. Excel has no CONVEXITY function, so build it from the cash-flow table:

Short-cut (effective) formulas: three prices and no cash-flow table

Reprice the bond at y − Δy (V₋), at y (V₀) and at y + Δy (V₊). With these three prices you can approximate duration and convexity for any bond you can price, which is how analysts do it for callable bonds, MBS and portfolios.

15 Interactive lab: the price–yield curve, the duration line and convexity

A bond’s price is a curved function of its yield. Duration replaces that curve with a straight line, the tangent at today’s yield. Convexity measures how much the true curve bends away from that line. This lab makes all three visible: move the yield and watch the gap open up.

Which bonds are more convex?

Tick bonds to compare their percentage price changes. Longer maturities and lower coupons make the curve steeper (higher duration) and more bent (higher convexity).

16 Interactive bond calculator: the workbook in your browser

Change any input. The calculator repeats the workbook’s logic for a bond settled on a coupon date: it prices the bond, computes Macaulay duration, modified duration and convexity, runs the short-cut formulas, and compares the estimated price shock with an exact repricing. The Excel formula for the current inputs is printed so you can paste it straight into a cell.

17 Excel function cheat sheet and common mistakes

18 Practice exercises (solve in Excel, then check)

19 References and next steps

1. Microsoft Support — PRICE function
2. Microsoft Support — YIELD function
3. Microsoft Support — ACCRINT function
4. Microsoft Support — COUPDAYBS function and COUPDAYS function
5. Microsoft Support — DURATION function and MDURATION function
6. Microsoft Support — TBILLPRICE, TBILLYIELD and TBILLEQ
7. Microsoft Support — DOLLARDE function
8. Microsoft Support — Financial functions reference
9. TreasuryDirect — Treasury bills, notes and bonds
10. Reserve Bank of India — FAQs on the Government Securities Market
11. CFA Institute, Fixed Income curriculum readings on bond valuation, yield measures and interest-rate risk (Level I).
12. John C. Hull, Options, Futures and Other Derivatives (Pearson), chapter on interest rate swaps: valuing a swap in terms of bond prices and as a portfolio of FRAs.
13. Frank J. Fabozzi, Bond Markets, Analysis, and Strategies (Pearson), chapters on pricing, yield measures, duration and convexity.

Educational content only. All bonds, quotes and yields are hypothetical examples chosen for teaching and are not market data or investment advice. Excel results shown were produced by the companion workbook.