MS Excel: PRICEDISC function to determine price with discount rate

In fixed-income analysis, not all securities pay periodic interest. Instruments like Treasury bills, commercial paper, and zero-coupon bonds are issued at a discount and redeemed at full face value. To determine their price based on a given discount rate, Excel provides the PRICEDISC function.

This article explores the PRICEDISC function from a professional finance perspective, including its purpose, structure, usage examples, and best practices.

The PRICEDISC function calculates the price per $100 face value of a discount security, based on:

• Settlement and maturity dates

• Discount rate

• Redemption value

• Day-count basis

Unlike PRICE, which applies to coupon-paying bonds, PRICEDISC is specifically tailored for non-interest-bearing instruments.

🔹 Syntax

excel

CopyEdit

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

Parameter Definitions:

Argument	Description
settlement	The date the security is purchased (must be before maturity).
maturity	The date the security matures (i.e., when the face value is paid).
discount	The annualized discount rate (not yield) as a decimal (e.g., 5% = 0.05).
redemption	Redemption value per $100 face value (typically 100).
basis	[Optional] Day-count convention (0–4). Default is 0 (US 30/360).

🧾 Example: Pricing a Treasury Bill

Suppose you purchase a T-bill with:

• Settlement Date: April 1, 2025

• Maturity Date: October 1, 2025

• Discount Rate: 6%

• Redemption Value: $100

• Basis: Actual/Actual (1)

excel

CopyEdit

=PRICEDISC(DATE(2025,4,1), DATE(2025,10,1), 0.06, 100, 1)

✅ Result: $97.06This means the investor pays $97.06 and receives $100 at maturity.

💡 When to Use PRICEDISC

PRICEDISC is ideal for analyzing:

• Short-term government instruments (e.g., 91-day or 182-day T-bills)

• Commercial paper

• Banker's acceptances

• Zero-coupon bonds (if traded on a discount basis)

It allows traders, treasury analysts, and investment professionals to:

• Evaluate fair value of non-interest-bearing securities

• Compare instruments with different terms and maturities

• Integrate discount-based securities into financial models

🛠️ Advanced Considerations

🔁 Reverse Calculation:

To go from price to discount rate, use the DISCRATE function.

excel

CopyEdit

=DISCRATE(settlement, maturity, price, redemption, [basis])

This is useful in yield curve modeling or pricing sheets.

⚠️ Handling Date Errors:

Ensure that:

• Dates are valid and recognized by Excel (use DATE() or date-formatted cells)

• settlement < maturity

Otherwise, you'll receive a #NUM! or #VALUE! error.

✅ Summary Table

Feature	Description
Function Name	PRICEDISC
Purpose	Price of a discount (zero-coupon) instrument
Inputs Required	Settlement, maturity, discount rate, redemption
Optional Input	Basis (day-count convention)
Ideal Use Cases	T-bills, CP, zero-coupon bonds

📊 Final Thoughts

The PRICEDISC function is essential for professionals who manage or analyze discounted short-term investments. It simplifies valuation and improves the accuracy of portfolio models and fixed-income analytics.

Used alongside functions like DISCRATE, YIELD, and PRICE, it forms a foundational toolkit for fixed-income modeling in Excel.

Pro Tip: Always align the day-count basis with your market (e.g., Actual/360 for money markets, Actual/Actual for T-bills) to ensure precision.