How to Use the DISC and PRICEDISC Functions in Microsoft Excel for Discounted Securities

In fixed-income markets, money market funds, and corporate treasury management, financial professionals frequently trade short-term debt instruments such as Treasury bills (T-bills), commercial paper, and bankers’ acceptances. Unlike standard coupon-bearing bonds that distribute periodic interest payments, these instruments are sold at a discount to their face value and mature at par (full face value). Microsoft Excel provides two specialized financial functions to model these instruments accurately: DISC (which calculates the annualized discount rate) and PRICEDISC (which determines the purchase price per £100/€100/$100 face value).

Understanding Discounted Debt Securities

Short-term money market instruments trade on a discount basis rather than a standard yield to maturity:

• Zero Coupon Structure: The investor receives no interim interest payments. Return is earned entirely through capital appreciation as the security matures from its discounted purchase price up to its face value.
• Price Basis: Bond prices are quoted on a normalized scale per 100 currency units of face value (redemption value).
• Day Count Conventions: Money market transactions depend heavily on standardized day count conventions (such as US 30/360 or Actual/360).

Part 1: The DISC Function

The DISC function returns the discount rate for a security based on its settlement date, maturity date, purchase price, and face value redemption price.

Syntax

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

Parameter Definitions

• settlement (Required): The security’s settlement date (the date the buyer acquires the instrument, formatted as an Excel date).
• maturity (Required): The security’s maturity date when it expires and repays face value.
• pr (Required): The security’s price per 100 face value.
• redemption (Required): The security’s redemption value per 100 face value (typically 100).
• basis (Optional): The day count convention indicator:
  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360 (Standard for US Treasury Bills and European money markets)
  • 3: Actual/365
  • 4: European 30/360

Example Calculation with DISC

Assume an investor purchases a Treasury bill with the following parameters:

• Settlement Date: 15 February 2026 (Cell A2)
• Maturity Date: 15 August 2026 (Cell B2)
• Purchase Price per 100: 97.50 (Cell C2)
• Redemption Value: 100.00 (Cell D2)
• Day Count Basis: 2 (Actual/360) (Cell E2)

To determine the annualized discount rate:

=DISC(A2, B2, C2, D2, E2)

Format the result as a percentage. The formula outputs 4.97%, indicating the annualized discount rate based on elapsed days.

Part 2: The PRICEDISC Function

The PRICEDISC function calculates the purchase price per 100 face value of a discounted security when the target discount rate is known.

Syntax

=PRICEDISC(settlement, maturity, discount, redemption, [basis])

Parameter Definitions

• settlement (Required): The transaction settlement date.
• maturity (Required): The maturity date.
• discount (Required): The security’s annual discount rate (entered as a decimal or percentage, e.g. 0.052 or 5.2%).
• redemption (Required): The redemption value per 100 face value (typically 100).
• basis (Optional): The day count convention flag (matching the conventions outlined above).

Example Calculation with PRICEDISC

A corporate treasurer wants to purchase commercial paper maturing in 90 days with a quoted discount rate of 4.80%:

• Settlement Date: 01 March 2026 (Cell A5)
• Maturity Date: 30 May 2026 (Cell B5)
• Discount Rate: 4.80% (Cell C5)
• Redemption Value: 100 (Cell D5)
• Basis: 2 (Actual/360) (Cell E5)

To determine the purchase price per 100 face value:

=PRICEDISC(A5, B5, C5, D5, E5)

Format the cell as Currency. Excel outputs 98.80. If the treasurer intends to purchase £1,000,000 in face value, the required capital allocation is calculated as 1,000,000 * (98.80 / 100) = £988,000.

Mathematical Difference Between Discount Rate and Investment Yield

A frequent mistake in corporate treasury models is confusing the Discount Rate (calculated by DISC) with the Investment Yield (calculated by YIELDDISC):

• Discount Rate: Measures the discount amount relative to the future face value at maturity.
• Investment Yield: Measures the return earned relative to the actual capital invested (the lower purchase price). Because the investment base is smaller than the face value, the actual yield will always exceed the quoted discount rate.

Common Errors and Operational Rules

To prevent calculation errors in financial workbooks:

• Date Hierarchy: The maturity date must be strictly later than the settlement date. If settlement >= maturity, Excel returns a #NUM! error.
• Date Formats: Always reference true date serial numbers using the DATE(year, month, day) function or valid cell links rather than typing text dates, which can produce #VALUE! errors depending on regional Windows date settings.
• Basis Bounds: The basis argument must be an integer between 0 and 4. Any other value results in a #NUM! error.