Quick Navigation
YIELDDISC Function
Summary
The YIELDDISC function calculates the annual yield for a discounted security, such as commercial paper or Treasury bills, based on the purchase price and redemption value. This financial function helps investors determine the effective annualized return on discount instruments that don't pay periodic interest.
Syntax
YIELDDISC(settlement, maturity, pr, redemption, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security's settlement date - when the buyer receives the security after issue |
| maturity | Date |
Yes | Security's maturity date - when the security expires and is redeemed |
| pr | Number |
Yes | Price per $100 face value paid by the investor |
| redemption | Number |
Yes | Redemption value per $100 face value received at maturity |
| basis | Number |
No | Day count basis (0=30/360 US, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=30/360 European) |
Using the YIELDDISC Function
YIELDDISC is crucial for analyzing discount securities like Treasury bills, commercial paper, and bankers' acceptances. Use it to calculate the true annualized yield based on the discount from face value, considering the time period and appropriate day count convention for accurate financial analysis.
Common YIELDDISC Examples
Basic Discount Security Yield
=YIELDDISC("2/16/2008", "3/1/2008", 99.795, 100, 2)Calculates yield for a security bought at $99.795 that redeems at $100 face value using Actual/360 basis (result: 0.052823 or 5.28%)
Treasury Bill Yield Calculation
=YIELDDISC(DATE(2024,6,15), DATE(2024,9,15), 98.25, 100, 1)Computes annualized yield for a T-bill purchased at discount using actual/actual day count
Commercial Paper Analysis
=YIELDDISC(A2, B2, C2, 100, 0)Uses cell references for settlement date (A2), maturity (B2), and price (C2) with US 30/360 basis
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Settlement or maturity dates are invalid or entered as text
Solution: Use DATE(year,month,day) function or ensure dates are proper Excel dates
#NUM! error
Cause: pr ≤ 0, redemption ≤ 0, settlement ≥ maturity, or invalid basis
Solution: Verify positive prices/redemption values, chronological dates, and basis between 0-4
#NAME? error
Cause: Function name misspelled
Solution: Use exact spelling: YIELDDISC
Notes
- Dates stored as serial numbers (Jan 1, 1900 = 1)
- Settlement date always follows issue date
- Microsoft truncates settlement, maturity, and basis to integers
- Result expressed as decimal (0.0528 = 5.28%)
- Available in Excel 2007 and later versions
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel for Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+