Bond Pricing Spreadsheet Example
Bond Pricing Spreadsheet Example
A practical spreadsheet model that prices a bond, calculates accrued interest, and determines yield to maturity. Copy this and modify it for your own securities.
Key takeaways
- A bond pricing spreadsheet uses the present-value formula to compute price from yield, or solves for yield given a price.
- Input the coupon, face value, years to maturity, and yield; the spreadsheet calculates price.
- Add accrued-interest calculation to find the dirty price.
- Modify the yield and see how price changes to visualize the inverse relationship.
- Financial calculators and Excel functions make this straightforward; the logic is always the same.
The basic spreadsheet structure
Below is a simple bond pricing model in Excel or Google Sheets. You can copy and use this as a template.
Input Section
[A1] Bond Pricing Worksheet
[A3] INPUTS:
[A4] Face Value [B4] 1000
[A5] Annual Coupon % [B5] 4%
[A6] Years to Maturity [B6] 5
[A7] Market Yield [B7] 5%
[A8] Days Since Coupon [B8] 90
[A9] Days in Period [B9] 180
[A10] Day-Count Convention [B10] Actual/360
Derived Calculations
[A12] CALCULATIONS:
[A13] Semi-Annual Coupon Payment [B13] =B4*B5/2
[A14] Semi-Annual Yield [B14] =B7/2
[A15] Number of Periods [B15] =B6*2
Present Value of Coupons
Use a formula to sum the present value of all coupon payments:
[A17] PV of Coupons:
[A18] Period 1 PV: =B13/(1+B14)^1
[A19] Period 2 PV: =B13/(1+B14)^2
[A20] Period 3 PV: =B13/(1+B14)^3
...
[A27] Period 10 PV: =B13/(1+B14)^10
[A28] Total PV of Coupons: =SUM(A18:A27)
Or, use Excel's PV function to simplify:
[A28] Total PV of Coupons: =PV(B14, B15, -B13)
The PV function returns the present value of an annuity (the coupon payments). The negative sign on B13 indicates a cash outflow (the coupon is paid to the investor, so it is negative from the issuer's perspective).
Present Value of Principal
[A30] PV of Principal:
[A31] =B4/(1+B14)^B15
Clean Price
[A33] CLEAN PRICE:
[A34] =ABS(A28)+A31
The ABS function converts the negative PV result to positive. The clean price is the sum of the PV of coupons and the PV of principal.
Accrued Interest
[A36] ACCRUED INTEREST:
[A37] Semi-Annual Coupon: =B4*B5/2
[A38] Fraction of Period: =B8/B9
[A39] Accrued Interest: =A37*A38
Dirty Price
[A41] DIRTY PRICE:
[A42] =A34+A39
Yield to Maturity (solving for yield given price)
If you have the clean price and want to find the yield, use Excel's RATE or YIELD function:
[A44] Yield to Maturity (check):
[A45] =YIELD(TODAY(), TODAY()+B6*365, B5, A34, 100, 2)
(The YIELD function syntax is: settlement date, maturity date, annual coupon rate, price as % of par, redemption value as % of par, and frequency. This is specific to Excel and may vary in other spreadsheets.)
Full worked example
Let us price a corporate bond with the following characteristics:
- Face Value: $1,000
- Coupon: 4% annual (2% semi-annually, or $20 per period)
- Years to Maturity: 5 years
- Market Yield: 5% annual (2.5% semi-annually)
- Days since last coupon: 90 days
- Days in coupon period: 180 days
Step 1: Semi-annual parameters
- Semi-annual coupon: $1,000 × 4% / 2 = $20
- Semi-annual yield: 5% / 2 = 2.5%
- Number of periods: 5 × 2 = 10
Step 2: Present value of coupons
PV_{coupons} = \frac{20}{1.025^1} + \frac{20}{1.025^2} + ... + \frac{20}{1.025^{10}}
= 19.51 + 19.02 + 18.56 + 18.11 + 17.67 + 17.25 + 16.84 + 16.46 + 16.09 + 15.74
= 175.25
Using Excel's PV function: =PV(0.025, 10, -20) = $175.25
Step 3: Present value of principal
PV_{principal} = \frac{1,000}{1.025^{10}} = \frac{1,000}{1.2800} = 781.20
Step 4: Clean price
\text{Clean Price} = 175.25 + 781.20 = 956.45
The bond is priced below par because the coupon (4%) is below the market yield (5%).
Step 5: Accrued interest
\text{Accrued Interest} = 20 \times \frac{90}{180} = 10.00
Step 6: Dirty price
\text{Dirty Price} = 956.45 + 10.00 = 966.45
The buyer pays $966.45 to settle the trade. The seller receives $966.45, of which $956.45 is the economic price and $10.00 is accrued interest compensation.
Step 7: Verify yield
To verify that the dirty price of $966.45 indeed corresponds to a 5% yield, the IRR of the cash flows should be 5%:
- Today: Pay $966.45
- Period 1 (6 months): Receive $20 coupon
- Period 2: Receive $20 coupon
- ... (8 more coupon periods)
- Period 10: Receive $20 coupon + $1,000 principal = $1,020
The internal rate of return (per period) is 2.5%, which annualizes to 5%. This confirms the pricing.
Sensitivity analysis: changing the yield
Now change cell B7 (market yield) and watch the clean price (A34) change. Try the following:
| Market Yield | Clean Price | Change |
|---|---|---|
| 4% | $1,043.88 | +$87.43 |
| 4.5% | $999.50 | +$43.05 |
| 5% | $956.45 | (base) |
| 5.5% | $915.39 | -$41.06 |
| 6% | $876.20 | -$80.25 |
The bond is worth more when yields fall ($1,043.88 at 4%) and less when yields rise ($876.20 at 6%). The price changes illustrate the inverse relationship: a 1% change in yield moves the price by about $40–$45. This is the bond's duration (interest-rate sensitivity) in action.
Adding multiple bonds
Extend the spreadsheet to include several bonds and compare their prices and yields:
[A1] Bond 1 | Bond 2 | Bond 3
[A2] Face Value: | 1000 | 1000 | 1000
[A3] Coupon %: | 3% | 4% | 5%
[A4] Years to Maturity: | 5 | 5 | 5
[A5] Market Yield: | 5% | 5% | 5%
[A6] Clean Price: | 913.37 | 956.45 | 1004.06
Bond 1 (low coupon) is deeply discounted. Bond 3 (high coupon) is at a premium. All are priced at a 5% yield.
Using a financial calculator or online tool
If you prefer not to build a spreadsheet, many online bond calculators exist:
- Wolfram Alpha: Search "bond pricing calculator"
- Bloomberg: Professional traders use Bloomberg terminals, which include sophisticated bond analytics.
- Excel Goal Seek: Use this feature to solve for yield given a price.
The underlying math is always the same. The spreadsheet makes it explicit and allows sensitivity analysis.
Practical uses
- Comparing bond offers: Get a quote for a bond, input it into the spreadsheet, and verify the quoted yield.
- Scenario analysis: Model how your holdings would change in value if yields move (e.g., Fed raises rates by 0.5%).
- Ladder construction: Price bonds at different maturities and build a ladder that meets your cash-flow needs.
- Relative value: Price two bonds with different coupons and maturities to decide which offers better value.
Flowchart
Next
We have now completed the Price-Yield Relationship chapter. You understand the fundamental inverse relationship, why it exists (present value, supply and demand), how magnitude varies (coupon and maturity effects), how to price bonds (present value formula), and how real-world quotations work (clean price, accrued interest, day counts). The next chapter explores duration and convexity—more sophisticated measures of bond risk that extend the intuition developed here.