Practice free

YIELD function

YIELD returns the annual yield to maturity of a bond that pays periodic interest, worked out from its price. It is the reverse of PRICE.

Example

Read it as a sentence: find the yield on a 5-year bond from January 1, 2024 to January 1, 2029 that pays 6 percent in two coupons a year and sells at 100, rounded to 6 decimals, which gives 0.06.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

settlement (required)
Date the security is bought (after issue, before maturity).
maturity (required)
Date the security expires and is repaid.
rate (required)
Annual coupon rate.
pr (required)
Price per 100 face value.
redemption (required)
Redemption value per 100 face value.
frequency (required)
Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
basis (optional)
Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Returns: Annual yield of a periodic-interest bond.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,2,0) 0.063931803 Typical
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,2) 0.063931803 Basis omitted
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,1,0) 0.063974055 Frequency 1
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,4,0) 0.063910667 Frequency 4
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,2.9,0) 0.063931803 Frequency 2.9 truncates
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,2,0) 0.063931803 Basis 0
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,3,0) #NUM! Frequency 3
=YIELD(DATE(2024,2,15),DATE(2034,11,15),0.0575,95.04287,100,0,0) #NUM! Frequency 0

Watch out for

  • Solved iteratively; may return #NUM! if it cannot converge.

Where it works

Excel
YIELD is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .