# How to calculate the price of a bond in Excel

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)`.

**Excel:** `=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Bond | Value |
| Face value | 1000 |
| Coupon rate | 0.05 |
| Yield to maturity | 0.062 |
| Years to maturity | 8 |
| Coupons per year | 2 |
| Price |  |

Formula in B7: `=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)`

Result: $925.21

## 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.

## Other ways

- `=PV(B3/B6,B5*B6,-B2*B3/B6,-B2)`: Priced 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-the-price-of-a-bond