YIELD Function in Google Sheets: Formula, Examples & Troubleshooting

YIELD calculates an annual yield for a security paying periodic coupons from its price, dates, coupon rate, redemption value, and frequency. In the two-year annual-coupon example, a price of 98 per 100, a 6% coupon, and redemption at 100 give an independently expected yield of 7.107837246%.

Verification: NOT_TESTED_IN_GOOGLE_SHEETS. New results are independently solved from discounted cash flows. Google’s public example is a separate published formula/output control, not a fresh native test. This is a spreadsheet calculation guide, not investment advice.

=YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])

The first two arguments are delivery and redemption dates. rate is the annual coupon rate. Google specifies price and redemption per 100 face value. Frequency is 1, 2, or 4 payments annually. Basis is optional, default 0: US NASD 30/360. Codes 1, 2, 3, and 4 mean Actual/Actual, Actual/360, Actual/365, and European 30/360. Use real date values. Google YIELD reference.

There is no initial-guess argument in this documented signature. This guide does not assert the numerical solver or iteration limit Google uses internally.

Enter:

In B10 enter:

=YIELD(B2,B3,B4,B5,B6,B7,B8)

The independently expected decimal result is 0.071078372458. Format it as a percentage to display about 7.107837246%; multiplying by 100 and then also applying percentage formatting would scale the display twice.

The independent model has two complete annual coupon periods, with receipts of 6 after one year and 106 after two years. Settlement is at the start of that regular full-period model.

Let the annual yield be y. The price equation is:

98 = 6/(1+y) + 106/(1+y)^2

Multiplying by (1+y)^2 gives a quadratic. The positive accumulation-factor root produces the arithmetic audit:

=(6+SQRT(36+4*B5*106))/(2*B5)-1

For B5 equal to 98, this gives 0.071078372458. The formula is a special two-payment derivation, not a general replacement for YIELD.

With the computed result in B10, =6/(1+B10)+106/(1+B10)^2 reprices the two receipts to 98. This reverse calculation is more informative than merely checking whether the yield looks plausible.

The annual coupon divided by price, =6/98, is only 6.122448980%. It omits the gain from 98 to 100 at redemption and is not the same calculation as the two-year yield.

Put 98, 100, and 102 in D2:D4. Enter =YIELD($B$2,$B$3,$B$4,D2,$B$6,$B$7,$B$8) in E2 and fill through E4.

The same receipts imply a lower yield when the purchase price is higher. At price 100, the full-period model’s yield equals its 6% coupon rate. In F2 use =6/(1+E2)+106/(1+E2)^2 and fill through F4 to check that every row reproduces its original price.

Keep the price on a per-100 scale. For a 10,000 face holding bought for 9,800 before any separate charges, the comparable quoted price is =9800/10000*100, or 98, rather than 9800.

Google’s public sheet shows an explicit =YIELD(B1,B2,B3,B4,B5,B6,B7) with settlement February 15, 2005, maturity November 15, 2010, coupon 5.15%, price 94.0335, redemption 100, frequency 2, and basis 0. Its displayed yield is 0.06404626433. Published YIELD example.

An independent coupon-price audit at that displayed yield gives 94.033499998, agreeing with the stated price to display precision. That control supports a regular coupon model; it does not establish every special-date or convergence rule.

Check coupon rate versus desired output yield, price scale, payment frequency, and basis first. Make sure dates are stored as dates and maturity is later than settlement in this workflow. A price and a total holding value cannot be interchanged without face-value scaling.

The examples use regular schedules and positive yields. Exact error codes, negative-yield support, solver iterations, irregular coupons, and exhaustive month-end/boundary behavior were not established. Do not hide an unexplained result behind rounding or an error wrapper before checking the inputs.

This is Google’s YIELD signature and evidence. No cross-product solver equivalence is claimed. Introduction and retirement dates were not established in the reviewed sources.

Related: DATE, SQRT, and POWER.

First-party references reviewed October 17, 2026: Google YIELD help and its public example above.