PRICEDISC function

The PRICEDISC function in Excel calculates the price of a discounted security based on its face value, discount rate, and time to maturity. This function is typically used for pricing short-term investments or securities that are issued at a discount and do not pay periodic interest, such as Treasury bills.

Syntax

PRICEDISC(settlement, maturity, rate, pr, [basis])

Parameters

1. settlement: The bond’s settlement date, which is the date after issuance when the bond is traded to the buyer. This must be a valid date in Excel.
2. maturity: The bond’s maturity date, which is the date when the bond will expire, and the issuer will pay the face value to the bondholder. This must also be a valid date in Excel.
3. rate: The annual discount rate of the security expressed as a decimal. For example, for a 5% discount, use 0.05.
4. pr: The price of the security per $100 face value. This is generally the price at which the security is sold or issued.
5. [basis] (optional): The day count basis to use in the calculation. This is a numerical value that determines how the day count is computed:
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

   The default is 0 (US 30/360).

How It Works

The PRICEDISC function calculates the price of a discounted security using the formula:

$$\text{Price} = \frac{\text{Face Value}}{(1 + \text{Discount Rate} \times \frac{\text{Days to Maturity}}{360})}$$

Where:

• Face Value is typically $100 for securities priced at a discount.
• Discount Rate is the annual discount rate (as a decimal).
• Days to Maturity is the number of days from the settlement date to the maturity date.

Example

Let’s say you have a discounted security with the following characteristics:

• Settlement Date: 2025-01-15
• Maturity Date: 2026-01-15
• Discount Rate: 4% (or 0.04)
• Price (per $100 face value): $98.50
• Day count basis: 0 (US 30/360)

To calculate the price of this security, you would use the following formula:

=PRICEDISC("2025-01-15", "2026-01-15", 0.04, 98.5)

This formula will return the price of the discounted security based on the given information.

Important Notes

• The settlement and maturity dates must be valid Excel dates.
• The rate should be expressed as a decimal (e.g., for a 4% rate, use 0.04).
• The pr is the price of the security per $100 face value.
• The [basis] is optional, and if not provided, it defaults to 0 (US 30/360).

Summary

The PRICEDISC function is used to calculate the price of a discounted security, such as a Treasury bill, based on its discount rate, settlement date, and maturity date. By understanding the inputs, investors can use this function to assess the price of these types of securities.