How to use the DISC() function in Excel

This function calculates the discount rate (anticipative interest rate) for a security based on its cash value, redemption value, and time to maturity (simple interest yield).

Syntax

DISC(Settlement; Maturity; Price; Repayment; [Basis])

Arguments

• Settlement (required)
  The date when the security is purchased.
  • Must be a valid date; time values are truncated.
• Maturity (required)
  The date when the security matures (redemption date).
  • Must be a valid date; time values are truncated.
• Price (required)
  The purchase price per $100 face value of the security.
  • Must be a positive number.
• Repayment (required)
  The redemption value per $100 face value at maturity.
  • Must be a positive number.
• Basis (optional)
  The day-count convention used for interest calculation.
  • If omitted, defaults to 0 (US (NASD) 30/360).

Error Handling

• If invalid dates or non-numeric values are provided, #VALUE! is returned.
• If invalid numbers (e.g., negative prices) are entered, #NUMBER! is returned.

Background

The anticipative interest method calculates yield upfront (discounting from the future value), unlike traditional interest calculations (yield in arrears).

• Common in short-term securities (e.g., treasury bills, commercial paper).
• The formula for the discount rate is derived from:

Discount Rate=Repayment−PriceRepayment×Days in YearDays to MaturityDiscount Rate=RepaymentRepayment−Price​×Days to MaturityDays in Year​

• Relationship to RECEIVED():
  DISC() and RECEIVED() are inverse functions, allowing conversion between anticipative and arrears interest rates.

Examples

1. Bill of Exchange Discounting
   • Scenario: A $5,000 bill of exchange with 2 months to maturity is discounted at $4,958.33.
   • Formula:

=DISC(« 10-May-2010 », « 10-Jul-2010 », 4958.33, 5000, 4)

1. • Result: 5% discount rate.

1. Treasury Bond Yield
   • Scenario: A German treasury bond (face value €500) priced at €497.75 with 1-year maturity.
   • Formula:

=DISC(« 30-Aug-2010 », « 22-Aug-2011 », 497.75, 500, 4)

1. • Result: 0.46% annual discount rate.

Notes

• For tax or regulatory compliance, verify day-count conventions (Basis).
• Use YIELDDISC() or RECEIVED() for equivalent yield calculations.