What is the Excel DISC Function in Excel?

The DISC function calculates the discount rate for a security based on its settlement date, maturity date, price, redemption value, and day count basis. =DISC(settlement, maturity, pr, redemption, [basis]) Use AskFormulas to generate validated formulas instantly.

How do I use Excel DISC Function to calculate the discount rate for a 91-day treasury bill?

To calculate the discount rate for a 91-day treasury bill, use the formula: =DISC(DATE(2025,1,15), DATE(2025,4,15), 98.5, 100, 1). Basic Treasury Bill Discount Rate This formula returns: 0.0608 or 6.08%.

Why does Excel DISC Function return #NUM! Error error?

The #NUM! Error error in Excel DISC Function occurs when disc returns #num! error. Verify settlement date is before maturity date

How can I optimize Excel DISC Function performance?

To optimize Excel DISC Function: 1) Use specific ranges instead of entire columns, 2) Avoid volatile functions within Excel DISC Function, 3) Consider array formulas for bulk operations, 4) Cache results when possible. AskFormulas automatically applies optimization best practices.

What is the syntax for Excel DISC Function?

The Excel DISC Function syntax in Excel is: =DISC(settlement, maturity, pr, redemption, [basis]). It takes 5 parameter(s): settlement (required), maturity (required), pr (required), redemption (required), basis (optional).

Why does Excel DISC Function return #VALUE! Error error?

The #VALUE! Error error in Excel DISC Function occurs when disc returns #value! error. Ensure all numeric parameters are valid numbers

How do I use Excel DISC Function to determine discount rate for 180-day commercial paper?

To determine discount rate for 180-day commercial paper, use the formula: =DISC(A2, B2, C2, D2, 2). Commercial Paper Discount Rate This formula returns: Varies based on input data.

Can Excel DISC Function handle explanation in Excel?

Yes, Excel DISC Function can handle explanation. For example: =DISC(DATE(2025,1,15), DATE(2025,4,15), 98.5, 100, 1) will calculate the discount rate for a 91-day treasury bill. This returns: 0.0608 or 6.08%

Excel DISC Function

The DISC function calculates the discount rate for a security based on its settlement date, maturity date, price, redemption value, and day count basis.

Excel

Google Sheets

financial

intermediate

Syntax Preview

Excel

Google Sheets

=DISC(settlement, maturity, pr, redemption, [basis])

Quick Answer

TL;DR

Function DISC function calculates the discount rate for securities like Treasury bills. Syntax: =DISC(settlement, maturity, pr, redemption, [basis]). For example, =DISC(DATE(2025,1,15), DATE(2025,4,15), 98.5, 100, 1) returns 6.08% for a 91-day T-bill at $98.50 per $100 face value. Choose the correct basis (0-4) based on security type.

For example, =DISC(DATE(2025,1,15), DATE(2025,4,15), 98.5, 100, 1) returns 6.08% for a 91-day T-bill at $98.50 per $100 face value

What is DISC Function

Syntax and Parameters

## What is DISC Function

The DISC function is a financial function available in both Microsoft Excel and Google Sheets that calculates the discount rate for a security. This function is essential for financial analysts and investment professionals who work with discount securities, particularly Treasury bills, commercial paper, and other short-term money market instruments.

Unlike interest-bearing securities that pay periodic coupons, discount securities are sold at a price below their face value and redeemed at par upon maturity. The difference between the purchase price and redemption value represents the investor's return, expressed as a discount rate. The DISC function quantifies this return on an annualized basis, enabling meaningful comparisons across securities with different terms and prices.

The mathematical formula behind DISC is: Discount Rate = ((Redemption - Price) / Redemption) × (Basis Days in Year / Days to Maturity). The function automatically handles the day count calculation based on the selected basis convention, accounting for the actual number of days between settlement and maturity.

It's important to understand that discount rate differs from yield. The discount rate calculates return as a percentage of the redemption value, while yield calculates return as a percentage of the purchase price. For securities trading at a discount, the yield will always be higher than the discount rate because it's calculated on a smaller base (the purchase price).

The DISC function should be used when analyzing securities that are quoted on a discount basis, such as Treasury bills. For interest-bearing securities, functions like YIELD or INTRATE are more appropriate. The function has been available across multiple Excel versions (2010 onwards) and is fully compatible with Google Sheets, making it a reliable tool for cross-platform financial modeling.

## Syntax and Parameters

The DISC function requires four mandatory parameters and one optional parameter:

SETTLEMENT (required): The settlement date is when the security is purchased or traded. This must be a valid date on or after the base date of the spreadsheet system. Use the DATE function (e.g., DATE(2025,1,15)) or cell references containing dates to ensure proper date formatting. Text dates like "1/15/2025" may work but can be unreliable across different regional settings.

MATURITY (required): The maturity date when the security expires and is redeemed. This must be a date after the settlement date. The difference between maturity and settlement determines the holding period, which directly affects the annualized discount rate calculation.

PR (required): The security's price per $100 face value. This is typically a value less than 100 for discount securities. For example, a price of 98.5 means the security costs $98.50 per $100 of face value. This parameter must be a positive number.

REDEMPTION (required): The redemption value per $100 face value. For most discount securities, this is 100, representing par value. However, some securities may have different redemption values, and this parameter allows for that flexibility.

BASIS (optional): The day count basis convention used to calculate the number of days. This parameter dramatically affects the result and should be chosen based on market convention:
- 0 or omitted: US (NASD) 30/360 - assumes 30 days per month and 360 days per year
- 1: Actual/actual - uses actual days in month and year
- 2: Actual/360 - uses actual days but assumes 360-day year (common for money market instruments)
- 3: Actual/365 - uses actual days but assumes 365-day year
- 4: European 30/360 - similar to 0 but with different end-of-month rules

Common mistakes include using text instead of date serial numbers, entering settlement dates that occur after maturity dates, using basis values outside the 0-4 range, or mixing up price and redemption parameters.

Practical Examples

Common Errors and Solutions

#NUM! Error

DISC returns #NUM! error

Cause:

Invalid dates or settlement >= maturity

Solution:

Verify settlement date is before maturity date

Prevention:

Use data validation or IF statements to check date order

Example:

#VALUE! Error

DISC returns #VALUE! error

Cause:

Non-numeric price or redemption values

Solution:

Ensure all numeric parameters are valid numbers

Prevention:

Use ISNUMBER function to validate inputs before calculation

Example:

#NUM! Error - Invalid Basis

DISC returns #NUM! for invalid basis

Cause:

Invalid basis parameter (not 0-4)

Solution:

Use basis values 0, 1, 2, 3, or 4 only

Prevention:

Create dropdown list for basis parameter with valid values only

Example:

Best Practices and Pro Tips

Choose Correct Day Count Basis

Different financial instruments follow specific market conventions. US Treasury bills use basis 1 (actual/actual), commercial paper uses basis 2 (actual/360), and corporate bonds use basis 0 (30/360). Using the wrong basis produces incorrect rates.

Use DATE Function for Reliable Inputs

Always use DATE(year, month, day) instead of text dates to avoid regional setting issues. DATE(2025,1,15) is unambiguous across all locales.

Understand Discount Rate vs Yield

DISC calculates rate as percentage of redemption value, while yield uses purchase price. For discount securities, yield is always higher. Use appropriate function based on your analysis needs.

Validate Input Ranges

Implement data validation to prevent errors: settlement and maturity should be reasonable dates, prices typically 90-100 for discount securities, redemption usually 100, basis must be 0-4.

Consider Annualization Impact

DISC automatically annualizes rates. A 30-day security with 5% annualized rate has different actual return than 360-day security. Always consider actual holding period when comparing securities.

DISC vs Related Functions

## DISC vs Related Functions

Understanding when to use DISC versus related financial functions is crucial for accurate analysis.

DISC vs YIELD: The DISC function calculates the discount rate for securities sold at a discount and redeemed at par, expressing return as a percentage of redemption value. YIELD calculates the yield to maturity for securities that pay periodic interest, expressing return as a percentage of the price paid. Use DISC for discount instruments like Treasury bills that pay no coupon, and YIELD for coupon-bearing bonds. The key difference is that DISC is for zero-coupon discount securities, while YIELD handles securities with regular interest payments.

DISC vs INTRATE: Both functions work with securities purchased at a discount, but they calculate returns differently. INTRATE calculates the interest rate for a fully invested security (interest-bearing), while DISC calculates the discount rate for securities sold at a discount. INTRATE expresses return as a percentage of the purchase price (similar to yield), whereas DISC expresses it as a percentage of redemption value. For most money market instruments, INTRATE is more commonly used, while DISC is preferred for Treasury bills and similar government securities.

DISC vs PRICEDISC: These functions perform inverse calculations. DISC calculates the discount rate when you know the price, while PRICEDISC calculates the price when you know the discount rate. If you're evaluating an existing security to determine its rate of return, use DISC. If you're pricing a security given a required rate of return, use PRICEDISC. These functions are complementary tools in a complete bond pricing toolkit.

Choosing the right function depends on your security type and what you're trying to calculate. For Treasury bill analysis, use DISC, PRICEDISC, and YIELDDISC as a suite. For coupon bonds, use PRICE, YIELD, and related coupon functions. For money market instruments, INTRATE and RECEIVED are often more appropriate than DISC.