Excel PRICEDISC Function
Calculate the price of discounted securities. Learn PRICEDISC syntax, examples, and how to value Treasury bills and discount bonds effectively.
Excel
Google SheetsExcelGoogle Sheets=PRICEDISC(settlement, maturity, discount, redemption, [basis])Quick Answer
PRICEDISC function PRICEDISC function calculates the price per $100 face value of a discounted security that pays no interest and is redeemed at maturity. It uses the formula: Price = Redemption - (Discount × Redemption × (Days to Maturity / Year Basis)). Essential for valuing Treasury bills, commercial paper, and short-term discount instruments.
=PRICEDISC(settlement, maturity, discount, redemption, [basis])- Syntax: `=PRICEDISC(settlement, maturity, discount, redemption, [basis])` Example: `=PRICEDISC(DATE(2025,1,15), DATE(2025,4,15), 0.04, 100, 1)` returns $99.01, calculating the price of a 90-day Treasury bill with a 4% discount rate
Practical Examples
Basic Treasury Bill Pricing
Calculate the price of a 90-day Treasury bill with a 4% discount rate
Commercial Paper Valuation
Value commercial paper with 270-day maturity and 3.5% discount
European Money Market Instrument
Price a European short-term security using European 30/360 basis
Discount Note with Variable Redemption
Calculate price for a discount note with redemption value of $98
Sensitivity Analysis with Different Bases
Compare prices using different day count conventions
Common Errors and Solutions
PRICEDISC returns #VALUE! error
Settlement or maturity dates are invalid or entered as text
Use DATE() function or proper date values. Ensure dates are recognized by Excel as date serial numbers.
Always use DATE(year, month, day) format for date inputs or reference cells formatted as dates
PRICEDISC returns #NUM! error for date validation
Settlement date is greater than or equal to maturity date, or discount rate is negative
Verify maturity date comes after settlement date and discount rate is positive (e.g., 0.05 for 5%)
Validate date order and ensure discount is entered as decimal (not percentage)
PRICEDISC returns #NUM! error for invalid parameters
Invalid basis parameter (not 0-4) or redemption value is zero or negative
Use basis values 0, 1, 2, 3, or 4 only. Redemption must be positive.
Create data validation for basis input (0-4) and redemption value (>0)
PRICEDISC returns unexpected values
Discount rate entered as percentage instead of decimal
Convert percentage to decimal: divide by 100 (5% becomes 0.05) or use 5%
Format discount rate cells as decimal or use formula =C2/100 if source is percentage
Best Practices and Pro Tips
Use Consistent Day Count Basis
Always use the appropriate day count basis for your security type. US Treasury bills typically use basis 1 (actual/actual), while commercial paper uses basis 0 (30/360). Using the wrong basis can result in pricing errors.
Verify Date Order
Settlement date must always precede maturity date. Use conditional formatting or data validation to highlight date errors before they cause #NUM! errors in calculations.
Discount vs Yield
PRICEDISC uses discount rate, not yield. These are different concepts in fixed income pricing. Do not confuse discount rate with yield to maturity (YTM). For yield-based pricing, use PRICE function instead.
Redemption Value Flexibility
While typically $100, the redemption parameter can be adjusted for securities with different face values or to calculate prices per different denominations (e.g., per $1000 face value).
Combine with YEARFRAC for Manual Verification
Verify PRICEDISC results using: Price = Redemption - (Redemption × Discount × YEARFRAC(settlement, maturity, basis)). This helps validate your inputs and understand the calculation.
Need Help with Excel PRICEDISC Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The ACCRINT function calculates accrued interest for a security that pays periodic interest, enabling accurate financial reporting and bond valuation.
ExcelSheetsConvert fractional dollar prices to decimals with DOLLARDE. Essential for bond trading, stock quotes, and financial analysis.
ExcelSheetsConvert decimal dollar prices to fractional notation with DOLLARFR. Essential for bond pricing, securities trading, and financial reporting systems.
ExcelSheetsThe ACCRINTM function calculates accrued interest for securities that pay interest at maturity, essential for bond valuation and analysis.
ExcelSheets