YIELD function

YIELD returns a bond's annual yield to maturity as a percentage, and you use it when you know a bond's price and need its true return.

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

Generate a YIELD formula

Describe what you need. The generator will reach for YIELD where YIELD is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How YIELD reads its arguments
settlementrequiredmaturityrequiredraterequiredprrequiredredemptionrequiredfrequencyrequiredbasisoptionalYIELD →
ArgumentRequiredDescription
settlementRequiredA date serial number for the bond's settlement (purchase) date; it must fall before maturity or YIELD returns #NUM!.
maturityRequiredA date serial number for the bond's maturity (redemption) date; if it is on or before settlement, YIELD returns #NUM!.
rateRequiredA decimal number for the bond's annual coupon rate; it must be zero or positive, and negative values return #NUM!.
prRequiredA decimal number for the bond's price per 100 of face value; it must be greater than zero or YIELD returns #NUM!.
redemptionRequiredA decimal number for the redemption value per 100 of face value, typically 100; zero or negative values return #NUM!.
frequencyRequiredAn integer for coupon payments per year — 1 (annual), 2 (semiannual), or 4 (quarterly); any other value returns #NUM!.
basisOptionalAn optional integer from 0-4 selecting the day-count convention, defaulting to 0 (US 30/360) when omitted; values outside 0-4 return #NUM!.

Returns

A decimal number, formatted as a percentage, representing the bond's annual yield to maturity.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Yield on a bond bought at a discount

BondIssuerSettlementMaturityCouponPrice (pr)RedemptionFrequencyBasis
B-101Northwind Retail2018-03-102028-03-100.049510020
B-102Acme Industrial2020-01-152030-01-150.035101.510010
B-103Meridian Energy2021-09-012026-09-010.059910024
=YIELD(C2, D2, E2, F2, G2, H2, I2)

Result: 0.0463 (4.63%)

Row 2 is a ten-year bond bought at 95 per 100 of face value while paying only a 4% coupon, so it trades at a discount. The investor collects the coupon and also the 5-point gain between the 95 paid and the 100 redeemed at maturity, which is why the yield of 4.63% comes out above the 4% coupon rate.

2. Yield on a bond bought at a premium

BondIssuerSettlementMaturityCouponPrice (pr)RedemptionFrequencyBasis
B-101Northwind Retail2018-03-102028-03-100.049510020
B-102Acme Industrial2020-01-152030-01-150.035101.510010
B-103Meridian Energy2021-09-012026-09-010.059910024
=YIELD(C3, D3, E3, F3, G3, H3, I3)

Result: 0.0333 (3.33%)

Row 3 cost 101.5 but redeems at 100, so the investor loses 1.5 points over the life of the bond. That loss offsets part of the 3.5% coupon and pulls the yield down to 3.33%. Note frequency is 1 here rather than 2 — this bond pays annually, and changing that argument alone moves the answer.

3. Yield under the European 30/360 basis

BondIssuerSettlementMaturityCouponPrice (pr)RedemptionFrequencyBasis
B-101Northwind Retail2018-03-102028-03-100.049510020
B-102Acme Industrial2020-01-152030-01-150.035101.510010
B-103Meridian Energy2021-09-012026-09-010.059910024
=YIELD(C4, D4, E4, F4, G4, H4, I4)

Result: 0.0523 (5.23%)

Row 4 sets the optional basis argument to 4, which is European 30/360, instead of leaving it to default to 0 (US 30/360). The two conventions differ in how they count days at month ends, so on the same bond they return slightly different yields. State the basis explicitly whenever the result has to reconcile with someone else's system.

Common errors

Which YIELD error are you seeing?
YIELD returned an error#NUM!
Confirm settlement genuinely precedes maturity, and swap the two date cells if they were entered in the wrong order.
#VALUE!
Convert the text to a real date with DATEVALUE, or reformat the column as a date type before referencing it in YIELD.
#NUM!
Set frequency to 1, 2, or 4 to match how often the bond actually pays coupons.
ErrorWhy it happensHow to fix it
#NUM!settlement falls on or after maturity — for example, mistakenly swapping a hire date and a bond maturity date so settlement is later than maturity.Confirm settlement genuinely precedes maturity, and swap the two date cells if they were entered in the wrong order.
#VALUE!settlement or maturity is text that Excel or Sheets can't parse as a date, which commonly happens when HR roster hire dates are imported from a CSV as strings like "03/10/2018".Convert the text to a real date with DATEVALUE, or reformat the column as a date type before referencing it in YIELD.
#NUM!frequency is anything other than 1, 2, or 4 — for instance typing 3 for a mistakenly assumed tri-annual schedule.Set frequency to 1, 2, or 4 to match how often the bond actually pays coupons.

Tips and when to use something else

  • Settlement and maturity must be real date values, not text; if you're pulling hire dates from a roster export, wrap them in DATEVALUE first.
  • frequency only accepts 1, 2, or 4 — there's no built-in monthly option, so monthly-pay instruments need a different model entirely.
  • If you're solving for the rate on a loan or annuity payment schedule rather than a traded bond's market yield, use RATE instead of YIELD.
  • basis defaults to 0 (US 30/360) when omitted; using a basis that doesn't match the bond's actual day-count convention won't error, it will just silently shift the computed yield.

Related functions

RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.XIRRXIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.EFFECTEFFECT returns the effective annual interest rate given a nominal annual rate and number of compounding periods per year.

Frequently asked questions

What's the difference between YIELD and RATE in Excel?
YIELD solves for a traded bond's yield to maturity given its current market price, coupon rate, and redemption value, while RATE solves for the interest rate on a fixed-payment loan or annuity. Use YIELD when you're pricing an existing bond and RATE when you're modeling a payment schedule like a loan.
Why does YIELD return #NUM! even though my dates look correct?
#NUM! usually means settlement isn't strictly before maturity, or another numeric argument like rate, pr, redemption, frequency, or basis is out of its valid range. Double-check that frequency is 1, 2, or 4 and that pr and redemption are positive.
Can YIELD handle a bond that pays interest only at maturity?
YIELD assumes periodic coupon payments based on frequency, so it isn't designed for bonds that pay all interest at maturity. For that structure you'd need a different bond function built specifically for maturity-only interest, since forcing YIELD to model it will give an incorrect result.
Does YIELD work the same in Google Sheets as in Excel?
Yes, Google Sheets implements YIELD with the identical argument order and behavior as Excel, including the optional basis argument. Formulas built in one should calculate the same result after copying to the other.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17