YIELD Function
Calculates the yield to maturity of a bond that pays periodic interest.
Category
Financial
Available in
All versions, Microsoft 365
Returns
An annual yield
Overview
YIELD is the reverse of PRICE: given what you pay, it finds the annual return you'll earn if you hold the bond to maturity, counting coupons and the gain or loss at redemption.
Prices are quoted per $100 of face value, so 95.10 means you pay $951 for a $1,000 bond.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| settlement | Yes | The date the buyer takes ownership (after the issue date). |
| maturity | Yes | The date the security is repaid. |
| rate | Yes | The annual coupon rate. |
| pr | Yes | The price per $100 of face value. |
| redemption | Yes | The value paid at maturity per $100 of face value (usually 100). |
| frequency | Yes | Coupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly. |
| basis | No | Day count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360. |
Examples
Example 1 — What does the bond really earn?
Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026. It's offered at 95.25.
| A | B | |
|---|---|---|
| 1 | Settlement | 3/15/2026 |
| 2 | Maturity | 11/15/2033 |
| 3 | Coupon rate | 5% |
| 4 | Price | 95.25 |
| 5 | Redemption | 100 |
| 6 | Frequency | 2 |
| 7 | Basis | 0 |
| 8 | Yield | 5.77% |
| 9 | Yield at a price of 102 | 4.69% |
Buying below 100 lifts the yield above the 5% coupon; paying 102 pulls it below.
Common mistakes
- #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.
- #NUM! with a strange price. pr is per $100 — enter 95.25, not 952.50.
Notes
- Enter dates with DATE or as cells holding real dates. Text dates like "3/15/2026" typed straight into the formula can be misread on computers with other regional settings.
- Related functions: PRICE, YIELDDISC, YIELDMAT, RATE.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.