YIELDDISC function
YIELDDISC returns the annual yield of a discounted security, such as a zero-coupon one, from its price and redemption value. The yield is based on the price paid.
Example
Read it as a sentence: find the yield on a security from January 1, 2024 to July 1, 2024 bought at 97 with a redemption of 100, using basis 0, which gives 0.0618556701030928.
A
B
C
D
1
0.0618556701030928
2
3
Syntax
YIELDDISC(settlement, maturity, pr, redemption, [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.
-
pr(required) - Price per 100 face value.
-
redemption(required) - Redemption value per 100 face value.
-
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 discounted security.
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 |
|---|---|---|
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100,0) | 0.004108422 | Typical |
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100) | 0.004108422 | Basis omitted |
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100,0) | 0.004108422 | Basis 0 |
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100,1) | 0.004130996 | Basis 1 |
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100,2) | 0.004063275 | Basis 2 |
=YIELDDISC(DATE(2024,2,15),DATE(2024,8,15),99.795,100,3) | 0.004119709 | Basis 3 |
=YIELDDISC(DATE(2024,2,15),DATE(2024,2,15),99.795,100,0) | #NUM! | Settlement = maturity |
=YIELDDISC(DATE(2024,8,15),DATE(2024,2,15),99.795,100,0) | #NUM! | Settlement after maturity |
Watch out for
- Yield is on the price paid; DISC is on face value.
Where it works
- Excel
- YIELDDISC 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 .