Bond Pricing Model: How to Value Fixed Income Securities
A bond pricing model calculates the fair value of a bond by discounting its future cash flows — coupon payments and principal repayment — back to present value. It’s the core analytical tool for fixed income investors, credit analysts, and treasury professionals. Whether you’re pricing a corporate bond, a Treasury, or a zero-coupon bond, the mechanics are the same: cash flows discounted at the appropriate rate.
The Fundamental Bond Pricing Formula
Where C is the periodic coupon payment, r is the discount rate per period, F is the face value, t is each period, and n is the total number of periods. This is straightforward — the challenge lies in choosing the right discount rate and understanding how price sensitivity works.
Key Bond Pricing Concepts
| Concept | Definition | Why It Matters |
|---|---|---|
| Coupon Rate | Annual interest payment as % of face value | Determines cash flow magnitude |
| Yield to Maturity (YTM) | Total return if held to maturity | The single most important yield metric for comparison |
| Current Yield | Annual coupon / current market price | Quick income comparison across bonds |
| Par Value | Face value repaid at maturity (usually $1,000) | Terminal cash flow in the pricing model |
| Duration | Weighted average time to receive cash flows | Measures price sensitivity to rate changes |
| Convexity | Rate of change of duration | Captures non-linear price/yield relationship |
Premium, Par, and Discount Bonds
| Condition | Price vs. Par | Coupon vs. Market Rate |
|---|---|---|
| Premium Bond | Price > Par | Coupon rate > market yield |
| Par Bond | Price = Par | Coupon rate = market yield |
| Discount Bond | Price < Par | Coupon rate < market yield |
This inverse relationship between price and yield is the most fundamental concept in fixed income. When market rates rise, existing bonds with lower coupons become less attractive, so their price falls. When rates drop, existing higher-coupon bonds become more valuable.
Yield to Maturity Calculation
YTM is the internal rate of return that equates the bond’s price to its future cash flows. There’s no closed-form solution — you need to solve iteratively (or use Excel’s RATE or YIELD function).
In Excel, use the YIELD function: =YIELD(settlement, maturity, rate, price, redemption, frequency). For more control, build the cash flow schedule manually and use Goal Seek or RATE to solve for YTM.
Duration and Price Sensitivity
Modified duration tells you the approximate percentage price change for a 1% change in yield. A bond with modified duration of 7 will drop roughly 7% in price if yields rise by 1%. This is your primary risk metric for fixed income portfolios.
Convexity Adjustment
Duration gives a linear approximation, but the price/yield relationship is actually curved. Convexity captures this curvature:
For large rate moves (50bp+), the convexity adjustment matters. Bonds with higher convexity perform better in both rising and falling rate environments — they gain more when rates fall and lose less when rates rise.
Building a Bond Pricing Model in Excel
| Step | Action | Excel Implementation |
|---|---|---|
| 1 | Define bond parameters | Input cells for face value, coupon rate, maturity, payment frequency |
| 2 | Build cash flow schedule | List each payment date and amount (coupons + final principal) |
| 3 | Discount cash flows | Apply PV formula to each cash flow using the discount rate |
| 4 | Sum to get price | Total PV of all cash flows = bond price |
| 5 | Calculate YTM | Use Goal Seek or YIELD function to find yield given market price |
| 6 | Compute duration & convexity | Build weighted cash flow schedule for Macaulay duration |
| 7 | Add sensitivity analysis | Data table showing price across range of yields |
Pricing Different Bond Types
Zero-coupon bonds: Simplest case. Price = Face Value / (1 + r)^n. No coupon payments — all return comes from the discount to par. Duration equals maturity.
Callable bonds: Price with an option-adjusted spread (OAS) to account for the embedded call option. The issuer’s right to call reduces value to the bondholder. Use yield-to-call as an additional metric alongside YTM.
Floating-rate notes: Price stays near par because coupons reset to market rates. Value fluctuations come from changes in the credit spread, not rate movements.
Key Takeaways
- Bond price = present value of all future cash flows (coupons + principal) discounted at the appropriate yield
- Price and yield move inversely — this is the most fundamental fixed income relationship
- Modified duration measures price sensitivity to rate changes; convexity captures the non-linear component
- Use the yield curve for spot-rate discounting rather than a single flat rate for more accurate pricing
- Always distinguish between clean price (quoted) and dirty price (what you pay, including accrued interest)
Frequently Asked Questions
How do you calculate bond price in Excel?
Use the PRICE function: =PRICE(settlement, maturity, rate, yield, redemption, frequency). Or build a manual cash flow schedule, discount each payment at the yield rate, and sum the present values. The manual approach gives you more flexibility for sensitivity analysis and custom structures.
What is the relationship between bond price and yield?
They move inversely. When yields rise, bond prices fall, and vice versa. This happens because a bond’s fixed coupon becomes more or less attractive relative to current market rates. The magnitude of the price change depends on the bond’s duration and convexity.
Why is duration important in bond pricing?
Duration measures how sensitive a bond’s price is to interest rate changes. A duration of 5 means the bond price will change approximately 5% for every 1% change in yield. Longer maturity, lower coupon, and lower yield all increase duration — and therefore interest rate risk.
How do you price a zero-coupon bond?
A zero-coupon bond has no coupon payments, so the price is simply the face value discounted back to today: Price = Face Value / (1 + r)^n. These bonds trade at a deep discount to par and are most sensitive to rate changes since their entire return comes at maturity.
What discount rate should I use for bond pricing?
For Treasuries, use the risk-free spot rate corresponding to each cash flow’s maturity. For corporate bonds, add the appropriate credit spread to the risk-free rate. The spread reflects the bond’s credit risk, liquidity, and any embedded options. Use the yield curve for the most accurate approach.