When you are architecting a complex financial model dealing with discounted securities—bonds or treasury bills that do not pay periodic interest but are instead sold at a mathematically calculated discount to their absolute face value—standard pricing formulas will fail. To force the Microsoft Excel engine to execute the correct chronological calculus and determine the exact purchase price per $100 face value of this specific instrument, you must deploy the specialized PRICEDISC function.
Understanding the PRICEDISC Architecture
The PRICEDISC (Price of a Discounted Security) function is a rigid, specialized algorithmic engine. It calculates the absolute current price of a security that pays no coupons. It ingests the chronological parameters (settlement and maturity dates), the annual discount rate, and the redemption value. It then executes a geometric subtraction, factoring in the time value of money, to output the exact purchase price.
The syntax requires exactly four mandatory arguments and one optional modifier: =PRICEDISC(settlement, maturity, discount, redemption, [basis])
- settlement: The absolute chronological date the discounted security is purchased.
- maturity: The absolute chronological date the security expires (reaches its face value).
- discount: The annual discount rate of the security (expressed as a decimal or percentage).
- redemption: The absolute redemption value of the security per $100 face value (this is almost always exactly $100).
- [basis]: An optional integer defining the specific day-count basis algorithm (e.g., 0 for US 30/360, 2 for Actual/360).
Executing the Price Extraction
Imagine your firm purchases a Treasury bill. The settlement date is March 1, 2024 (Cell A1). The maturity date is August 28, 2024 (Cell A2). The annual discount rate is 4.25% (Cell A3). The redemption value is strictly $100 (Cell A4). The contract specifies an Actual/360 day-count basis (Code 2).
To execute the precise extraction, click an empty cell and type:
=PRICEDISC(A1, A2, A3, A4, 2)The exact millisecond you press Enter, the Excel engine executes the calculus. It calculates the exact geometric distance (180 days) between the settlement and maturity using the Actual/360 matrix. It applies the 4.25% annual discount proportionally to that exact timeframe and subtracts the resulting integer from the $100 redemption value. It outputs the highly precise absolute integer: 97.875.
This provides mathematical proof that you must pay exactly $97.875 for every $100 of face value you wish to acquire.