A bond’s price is the present value of its coupon payments plus its face value at maturity. PV bundles both into one figure at the market yield.
The example
$1,000 face, 5% coupon, 10 yrs, 6% market yield.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Face | $1,000 |
| 3 | Coupon 5% | $50/yr |
| 4 | Price at 6% yield | $926 |
The formula
The formula:
How it works
How it works:
- The coupon each period is
face × couponRate— the regular payment. PV(yield, periods, coupon, face)discounts the coupons and the face value back at the market yield.- Negate the result for a positive price. When the yield exceeds the coupon, the bond sells below face (a discount); below, at a premium.
- For semiannual coupons, halve the yield and coupon and double the periods.
Excel’s PRICE function handles real settlement/maturity dates and day-count conventions for traded bonds. The PV approach is the clean, intuitive version for coupon bonds with whole periods.
Try it: interactive demo
Face, coupon, years, yield.
Variations
Semiannual coupons
Halve & double:
PRICE (real dates)
Traded bonds:
Discount or premium?
Compare to face:
Pitfalls & errors
Sign convention. Negate PV (or expect a negative) so the price is positive.
Match coupon frequency. Semiannual bonds need yield/2, coupon/2, periods×2.
Yield vs coupon. Price moves opposite to yield — rising yields lower prices.
Practice workbook
Frequently asked questions
How do I price a bond in Excel?
How do I price a semiannual bond?
Why does the bond price differ from face value?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class