Master the DISC Function: Calculate Discount Rates for Financial Securities
=DISC(settlement, maturity, pr, redemption, [basis])The DISC function is a powerful financial tool in Excel that calculates the annual discount rate for a security based on its settlement date, maturity date, current price, and redemption value. This function is essential for financial analysts, investment professionals, and bond traders who need to determine the effective discount rate applied to discounted securities such as Treasury bills and commercial paper. Understanding how to use DISC effectively allows you to quickly analyze investment opportunities and compare different securities on a standardized basis. The DISC function operates by calculating the difference between a security's redemption value and its purchase price, then annualizing this discount based on the time period between settlement and maturity. This calculation is fundamental in fixed-income analysis and helps investors understand the true yield they receive from discounted instruments. Whether you're evaluating short-term government securities or corporate discount instruments, the DISC formula provides the standardized metric needed for informed investment decisions.
Syntax & Parameters
The DISC function syntax is: =DISC(settlement, maturity, pr, redemption, [basis]). Each parameter plays a critical role in the calculation. The 'settlement' parameter represents the security's settlement date—the date when the buyer receives the security. The 'maturity' parameter specifies when the security reaches its end date and the redemption value is paid. The 'pr' parameter is the security's current market price expressed as a percentage of face value or in absolute terms, depending on your data structure. The 'redemption' parameter indicates the security's value at maturity, typically expressed per $100 face value. The optional 'basis' parameter determines the day-count convention used: 0 (US 30/360), 1 (Actual/actual), 2 (Actual/360), 3 (Actual/365), or 4 (European 30/360). If omitted, basis defaults to 0. Practical tip: Always ensure your settlement date precedes your maturity date, and maintain consistent units for price and redemption values. When working with Treasury bills, redemption is typically $100 and price is quoted as a percentage.
settlementmaturityprredemptionbasisPractical Examples
Treasury Bill Discount Rate Calculation
=DISC(DATE(2024,1,15), DATE(2024,4,15), 98.50, 100, 0)This formula calculates the annual discount rate for the Treasury bill. The settlement date is January 15, 2024, maturity is April 15, 2024 (91 days), the purchase price is $98.50, redemption value is $100, and we use the 30/360 day-count basis standard for US Treasury instruments.
Commercial Paper Discount Analysis
=DISC(DATE(2024,3,1), DATE(2024,6,1), 97.25, 100, 1)This example demonstrates discount calculation for corporate commercial paper using actual/actual day-count basis (basis=1), which is more precise for shorter-term instruments. The 92-day holding period and $2.75 discount are annualized to determine the effective discount rate.
Municipal Bond Discount Rate with Actual/365 Basis
=DISC(DATE(2024,5,10), DATE(2024,11,10), 96.00, 100, 3)Municipal securities often use the Actual/365 day-count basis (basis=3). This formula calculates the discount rate for a 184-day holding period, allowing municipal bond traders to compare yields across different bond types using their preferred day-count convention.
Key Takeaways
- DISC calculates the annual discount rate for zero-coupon securities by annualizing the difference between purchase price and redemption value over the holding period.
- The optional basis parameter (0-4) determines day-count convention; basis=0 (30/360) is standard for US Treasury instruments, while basis=1 (Actual/actual) is common for commercial paper.
- Settlement date must precede maturity date, and both price and redemption values must be positive numbers to avoid #NUM! or #VALUE! errors.
- DISC differs from YIELDDISC in that it measures discount rate specifically, while YIELDDISC calculates total annual yield; use DISC for discount-focused analysis and YIELDDISC for yield-based comparisons.
- DISC is specifically designed for discount securities without coupon payments; for coupon-bearing bonds, use YIELD or YIELDMAT functions instead.
Pro Tips
Create a data validation dropdown for the basis parameter to ensure only valid values (0-4) are entered, preventing #VALUE! errors in production spreadsheets.
Impact : Reduces formula errors by 80% and makes spreadsheets more user-friendly for team members unfamiliar with basis conventions.
Use named ranges for settlement and maturity dates (e.g., 'Settlement' and 'Maturity') to make DISC formulas more readable and easier to audit: =DISC(Settlement, Maturity, Price, Redemption, Basis)
Impact : Improves spreadsheet maintainability and reduces errors when formulas are copied across multiple rows or shared with other analysts.
Format DISC results as percentages with 2-4 decimal places for financial reporting: multiply result by 100 or use percentage formatting to match industry standards.
Impact : Ensures consistency with Bloomberg, Reuters, and other financial data providers, facilitating seamless data comparison and stakeholder communication.
Build a sensitivity table varying the price parameter while keeping other inputs constant to understand how discount rates change across different purchase prices.
Impact : Enables quick 'what-if' analysis for investment decision-making and helps identify price thresholds where securities become attractive investments.
Useful Combinations
Comparing Multiple Securities with DISC and MAX
=MAX(DISC(DATE(2024,1,15), DATE(2024,4,15), 98.50, 100, 0), DISC(DATE(2024,1,15), DATE(2024,6,15), 97.00, 100, 0))Combines DISC with MAX to identify the security offering the highest discount rate. Useful for portfolio managers selecting between multiple discounted instruments to maximize returns. Wrap multiple DISC calculations to compare up to 255 securities simultaneously.
Converting DISC to YIELDDISC for Comparative Analysis
=YIELDDISC(DATE(2024,1,15), DATE(2024,4,15), 98.50, 100, 0) - DISC(DATE(2024,1,15), DATE(2024,4,15), 98.50, 100, 0)Calculates the spread between yield and discount rate, revealing the time-value adjustment for a specific security. This combination helps analysts understand how much of the return represents pure discount versus time-value benefits.
Conditional DISC Calculation with IF and Data Validation
=IF(maturity - settlement > 30, DISC(settlement, maturity, pr, redemption, 0), DISC(settlement, maturity, pr, redemption, 1))Automatically selects day-count basis based on security duration: uses 30/360 for longer-term instruments and Actual/actual for shorter-term securities. Streamlines analysis of mixed portfolios containing various instrument types and maturities.
Common Errors
Cause: Settlement date is equal to or after the maturity date, or price is zero or negative, or redemption value is zero or negative.
Solution: Verify that settlement < maturity dates. Check that both price and redemption values are positive numbers. Use DATE function to ensure proper date formatting: =DISC(DATE(2024,1,15), DATE(2024,4,15), 98.50, 100)
Cause: Non-numeric values entered for price or redemption parameters, or invalid basis value outside the range 0-4.
Solution: Ensure price and redemption are numbers, not text. Verify basis parameter is between 0 and 4. Convert text to numbers using VALUE() if needed: =DISC(settlement, maturity, VALUE(pr), redemption, 0)
Cause: Cell references are broken or deleted, or date cells contain invalid date values.
Solution: Check that all referenced cells exist and contain valid data. Verify dates are recognized by Excel using ISNUMBER(settlement) test. Recreate broken references by clicking cells directly instead of typing references.
Troubleshooting Checklist
- 1.Verify settlement date is earlier than maturity date using formula: =IF(settlement < maturity, 'Valid', 'ERROR: Settlement >= Maturity')
- 2.Confirm price and redemption values are positive numbers, not text or formulas returning errors: =IF(AND(ISNUMBER(pr), ISNUMBER(redemption), pr > 0, redemption > 0), 'Valid', 'Check values')
- 3.Validate basis parameter is between 0 and 4: =IF(AND(ISNUMBER(basis), basis >= 0, basis <= 4), 'Valid', 'Basis must be 0-4')
- 4.Check date cells are formatted as dates, not text: =IF(ISNUMBER(settlement), 'Date format OK', 'Convert to date format')
- 5.Ensure price is less than redemption value (security is purchased at discount): =IF(pr < redemption, 'Valid discount', 'Price >= Redemption')
- 6.Test formula with known values from financial sources to validate accuracy: compare DISC result with Bloomberg or Treasury data
Edge Cases
Settlement and maturity are exactly 1 day apart
Behavior: DISC calculates the annualized rate for a 1-day holding period, resulting in very high discount rates (typically 360x the daily discount for 30/360 basis)
Solution: This is mathematically correct but practically unusual; verify data entry if you encounter 1-day securities
Overnight discount rates can be legitimately high in money markets, so this isn't necessarily an error
Price equals redemption value (no discount)
Behavior: DISC returns 0, indicating zero discount rate
Solution: This is correct behavior; a security purchased at par value has no discount component
In practice, discounted securities always trade below redemption value, so this scenario is rare
Using leap year dates (February 29) with different basis conventions
Behavior: Results vary depending on basis: 30/360 ignores leap years, while Actual/actual and Actual/365 account for the extra day
Solution: Specify basis consistently; verify against financial data provider calculations when leap years are involved
Differences are typically small but can affect pricing in February transactions
Limitations
- •DISC only works for zero-coupon or discount securities; it cannot calculate discount rates for bonds with periodic coupon payments. For coupon-bearing instruments, use YIELD, YIELDMAT, or YIELDCALL functions.
- •The function assumes the security is held to maturity; it does not account for early redemption, callable features, or putability that might affect actual returns.
- •DISC returns an annualized rate regardless of actual holding period; for very short-term securities (days), the annualized rate may not reflect practical investment returns.
- •Day-count basis conventions vary by market and instrument type; using the wrong basis can produce significantly different results. Always verify which convention applies to your specific security before relying on DISC calculations.
Alternatives
Compatibility
✓ Excel
Since 2007
=DISC(settlement, maturity, pr, redemption, [basis]) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and 365✓Google Sheets
=DISC(settlement, maturity, pr, redemption, [basis]) - Identical syntax and functionalityGoogle Sheets supports all basis conventions (0-4). Results may vary slightly due to date calculation differences; verify with known values.
✓LibreOffice
=DISC(settlement, maturity, pr, redemption, [basis]) - Fully compatible with LibreOffice Calc