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.
A
B
C
D
1
0.06
2
3
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .