DISC function

The DISC function in Excel is used to calculate the discounted price of a security or investment, given the face value, the interest rate, and the periods involved. It helps in determining the discounted price of a financial instrument like a bond or note when it is sold at a discount to its face value.

Syntax

DISC(settlement, maturity, price, redemption, basis)

Parameters

• settlement: The settlement date of the security or investment, i.e., the date after issuance when the security is traded to the buyer. This date must be entered as a valid Excel date.
• maturity: The maturity date of the security, i.e., the date when the security matures or is redeemed. This date must also be entered as a valid Excel date.
• price: The price of the security, expressed as a percentage of the face value (100). For example, if the security is sold for 95% of its face value, enter 95.
• redemption: The redemption value of the security, usually the face value (100) of the instrument.
• basis (optional): The day count basis to use for the calculation. This defines how days are counted in the formula:
  • 0 or omitted: US (NASD) 30/360 (360 days per year, 30 days per month).
  • 1: Actual/actual (actual number of days in the period).
  • 2: Actual/360.
  • 3: Actual/365.
  • 4: European 30/360.

How It Works

The DISC function calculates the discounted price of a security based on the difference between the face value and the actual price paid, adjusted for the number of days from settlement to maturity. It is typically used for instruments such as bonds or treasury bills that are issued at a discount to their face value.

The general formula for calculating the discount is:

$$\text{Discount} = \frac{ \text{Redemption} – \text{Price} }{\text{Redemption}} \times \frac{\text{Days in the Period}}{360}$$

Example

Suppose you have the following details for a security:

• Settlement Date: January 1, 2025
• Maturity Date: July 1, 2025
• Price: 95 (i.e., 95% of the face value)
• Redemption Value: 100 (the face value)
• Basis: 0 (using the US (NASD) 30/360 day count basis)

You would use the following formula to calculate the discount rate:

=DISC("2025-01-01", "2025-07-01", 95, 100, 0)

Explanation of the Example:

• "2025-01-01": The settlement date when the security is traded.
• "2025-07-01": The maturity date when the security matures.
• 95: The price of the security (95% of the face value).
• 100: The redemption value (face value) of the security.
• 0: The day count basis (US (NASD) 30/360).

Important Notes

• Settlement and Maturity Dates: The settlement and maturity dates must be entered as valid Excel date values.
• Price as Percentage: The price parameter is usually expressed as a percentage of the face value (e.g., a price of 95 means the security is priced at 95% of its face value).
• Basis: The basis parameter determines the method of counting the days. The default 0 corresponds to the US (NASD) 30/360 method.

Summary

The DISC function calculates the discount price of a security, given its settlement date, maturity date, price, and redemption value. It is primarily used in the context of securities, such as bonds and treasury bills, which are sold at a discount to their face value.