Its calculates the annual yield of a discounted security (e.g., Treasury bills or commercial paper) that pays no periodic interest but is issued at a discount and redeemed at face value.
Syntax
YIELDDISC(Settlement; Maturity; Price; Redemption; [Basis])
Arguments
| Argument | Required | Description | Validation Rules |
| Settlement | Yes | Purchase date of the security. | Must be valid date < Maturity. |
| Maturity | Yes | Maturity/redemption date. | Must be valid date > Settlement. |
| Price | Yes | Purchase price per $100 face value. | Must be positive and < Redemption. |
| Redemption | Yes | Redemption value per $100 face value. | Typically 100. |
| [Basis] | No | Day-count convention (0-4). Default=0. | See Table 15-2. |
Error Conditions
- #VALUE!: Invalid dates.
- #NUM!: If:
- Price ≤ 0 or ≥ Redemption
- Settlement ≥ Maturity
- Basis ∉ {0,1,2,3,4}
Key Formula
Where:
- Days_in_Year: 360 (Basis=0,2,4) or 365 (Basis=1,3).
- Days_to_Maturity: Actual calendar days between Settlement and Maturity.
Examples
- Bill of Exchange (Supplier Loan)
Scenario:
- Face Value: $5,000
- Purchase Price: $4,958.33 (5% discount)
- Settlement: 10-May-2010
- Maturity: 10-Jul-2010 (61 days)
- Basis: 4 (European 30/360)
Calculation:
=YIELDDISC(« 5/10/2010 », « 7/10/2010 », 4958.33, 5000, 4)
Result: 5.04% annual yield.
Background
- Discount vs. Yield:
- Discount Rate: Anticipative interest (applied upfront).
- Yield: Equivalent interest-in-arrears return.
- Day-Count Conventions:
| Basis | Method | Year Days |
| 0 | US (NASD) 30/360 | 360 |
| 1 | Actual/actual | 365/366 |
| 2 | Actual/360 | 360 |
| 3 | Actual/365 | 365 |
| 4 | European 30/360 | 360 |
- Comparison with RECEIVED():
- YIELDDISC() solves for yield given price.
- RECEIVED() solves for maturity value given yield.