Spreadsheet Practice

How to calculate the price of a bond in Excel

Formula (Excel and Google Sheets)

=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)

Use PV with the yield per period, the number of coupons, the coupon payment and the face value: =PV(B4/B6,B5*B6,-B2*B3/B6,-B2).

Result on the example
$925.21 in B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B7. Change any input and the result updates, or edit the formula to experiment.

How it works

A bond is a stream of coupons plus the face value at the end, so its price is the present value of both at the market yield. With two coupons a year, the yield and the coupon are halved and the years doubled: 16 payments of $25, plus $1,000 at the end, discounted at 3.1% per half-year. The yield is above the 5% coupon, so the bond trades below face value.

=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)

Result925.2063417

  1. B46.20%

    The value in B4.

  2. B62

    The value in B6.

  3. B4/B60.031

    The / operator divides.

  4. B58

    The value in B5.

  5. B5*B616

    The * operator multiplies.

  6. B2$1,000

    The value in B2.

  7. -B2-1000

    Makes the value negative.

  8. B35.00%

    The value in B3.

  9. -B2*B3-50

    The * operator multiplies.

  10. -B2*B3/B6-25

    The / operator divides.

  11. -B2-1000

    Makes the value negative.

  12. PV(B4/B6,B5*B6,-B2*B3/B6,-B2)925.2063417

    PV: Present value of future payments.

    • rateB4/B60.031
    • nperB5*B616
    • pmt-B2*B3/B6-25
    • fv-B2-1000

In Google Sheets

The same formula works in Google Sheets: =PV(B4/B6,B5*B6,-B2*B3/B6,-B2). We ran it in Google Sheets mode on the same data and it returns $925.21.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=PV(B3/B6,B5*B6,-B2*B3/B6,-B2)$1,000.00Different resultPriced at its own coupon rate, a bond is worth exactly its face value. At par: $1,000.00.

Common mistakes

  • Forgetting to halve the yield and coupon for semiannual bonds misprices them.
  • This prices the bond on a coupon date. Between coupons, Excel's PRICE function, which takes settlement and maturity dates, handles accrued interest.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the price of a bond

In B7, calculate the price of the bond in B2:B6: semiannual coupons, priced at the yield to maturity in B4.

Questions

How do I calculate yield to maturity from the price?

Use RATE: =RATE(B5*B6,B2*B3/B6,-B7,B2)*B6.

Why does the price fall when yields rise?

The fixed coupons are discounted more heavily, so the same cash flows are worth less today.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.