YIELDDISC Function
Calculate discount security yields in Excel and Sheets. Learn YIELDDISC syntax, examples for Treasury bills, and common errors.
Excel
Google SheetsExcelGoogle Sheets=YIELDDISC(settlement, maturity, pr, redemption, [basis])Quick Answer
YIELDDISC function YIELDDISC function calculates the annual yield for a discounted security (such as Treasury bills) that does not pay periodic interest. The syntax is `=YIELDDISC(settlement, maturity, pr, redemption, [basis])` where settlement is the purchase date, maturity is the expiration date, pr is the price per $100, redemption is the value at maturity (typically 100), and basis is the day count method.
=YIELDDISC(settlement, maturity, pr, redemption, [basis])- Example: `=YIELDDISC(DATE(2025,1,15), DATE(2025,7,15), 98.5, 100, 1)` returns 0.0305 or 3.05% for a 6-month Treasury bill.
Practical Examples
Basic Treasury Bill Yield
Calculate the annual yield for a 6-month Treasury bill
Commercial Paper Yield Calculation
Calculate yield for short-term commercial paper
Comparing Different Day Count Bases
See how different basis calculations affect yield
Investment Decision Analysis
Compare yields across multiple discount securities
Error-Handled Yield Calculation
Prevent errors with data validation
Treasury Bill Auction Analysis
Evaluate competitive bidding results from T-bill auctions
Common Errors and Solutions
YIELDDISC returns #NUM! error
Settlement date is greater than or equal to maturity date, dates are invalid, maturity is more than one year after settlement, or price is greater than or equal to redemption value
Ensure settlement date comes before maturity date and both are valid dates. Verify that maturity is not more than one year after settlement. Confirm that price (pr) is less than redemption value, as this is required for discount securities.
Validate inputs before calculation: settlement < maturity, (maturity - settlement) <= 365 days, pr < redemption, and basis is 0-4. Add data validation rules to prevent invalid date sequences.
Example:
YIELDDISC returns #VALUE! error
Non-numeric values provided for pr, redemption, or basis parameters, or settlement/maturity are not valid dates
Verify all price and redemption values are numbers, not text. Check that basis is an integer between 0 and 4. Use DATE() function to construct dates rather than text strings to avoid format issues.
Use DATE() function for all date inputs: =YIELDDISC(DATE(2025,1,15), DATE(2025,7,15), 98.5, 100, 1). Ensure numeric cells are formatted as numbers, not text. Use VALUE() to convert text numbers if necessary.
Example:
YIELDDISC returns unexpected negative or very high yields
Price (pr) is equal to or greater than redemption value, which violates the discount security requirement
For discount securities, price must be less than redemption value. Review your pricing data to ensure the security is trading at a discount. If price >= redemption, the security is not a discount security and YIELDDISC is not appropriate.
Validate that pr < redemption for all discount security calculations. Use conditional formatting to highlight cells where price >= redemption. Consider using YIELD function for securities trading at par or premium.
Example:
Best Practices and Advanced Tips
Understanding Day Count Basis
Different markets use different day count conventions. US Treasury bills use Actual/actual (1), while money market instruments often use Actual/360 (2). Using the correct basis is critical for accurate yield calculations and market-standard compliance.
Date Input Methods
Use the DATE() function instead of text strings for dates to avoid regional formatting issues. For example, use DATE(2025,1,15) instead of "1/15/2025". This ensures consistency across different regional settings and Excel versions.
Annualization Accuracy
YIELDDISC automatically annualizes the yield based on the time to maturity and day count basis. The result is always expressed as an annual rate, not a holding period return. This allows for direct comparison across securities with different maturities.
One-Year Limitation
YIELDDISC is designed for securities with less than one year to maturity. For longer-term securities with coupon payments, use the YIELD function instead. Most discount securities (T-bills, commercial paper) naturally fall within this timeframe.
Percentage Formatting
YIELDDISC returns a decimal (e.g., 0.0305 for 3.05%). Format cells as percentage with 2-3 decimal places for proper display. Use custom format "0.00%" for consistency across your analysis.
Discount vs Investment Yield
YIELDDISC calculates investment yield (also called bond equivalent yield), which differs from discount yield used in some markets. Investment yield is based on purchase price, while discount yield is based on face value. YIELDDISC uses the more accurate investment yield method.
Choosing the Right Yield Function
Use YIELDDISC for zero-coupon discount securities (T-bills, commercial paper), YIELD for bonds with periodic coupon payments, and YIELDMAT for securities that pay interest at maturity. Using the wrong function will produce incorrect results.
Relationship with PRICEDISC
YIELDDISC and PRICEDISC are inverse functions. If you calculate yield from a price using YIELDDISC, then use that yield in PRICEDISC, you should get back the original price. This relationship is useful for validation and verification of calculations.
Need Help with YIELDDISC Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Calculate the price of discounted securities. Learn PRICEDISC syntax, examples, and how to value Treasury bills and discount bonds effectively.
ExcelSheetsCalculate the price per $100 face value for Treasury bills. Learn TBILLPRICE syntax, examples, and T-bill pricing with settlement and maturity dates.
ExcelSheetsCalculate Treasury bill yields in Excel and Sheets. Learn the TBILLYIELD function syntax, parameters, examples, and common errors with this guide.
ExcelSheetsCalculate bond yields in Excel and Sheets. Learn the YIELD function syntax, parameters, examples, and common errors with this comprehensive guide.
ExcelSheets