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

Want to practice YIELD in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
rateYesThe annual coupon rate.
prYesThe price per $100 of face value.
redemptionYesThe value paid at maturity per $100 of face value (usually 100).
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay 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.

B8fx=YIELD(B1,B2,B3,B4,B5,B6,B7)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Coupon rate5%
4Price95.25
5Redemption100
6Frequency2
7Basis0
8Yield5.77%
9Yield at a price of 1024.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.
Kyle Stott

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.