Master the PRICEDISC Formula: Complete Guide to Calculating Discounted Security Prices
=PRICEDISC(settlement, maturity, discount, redemption, [basis])The PRICEDISC function is a specialized financial formula in Excel designed to calculate the price of a discounted security per $100 face value. This advanced function is essential for financial analysts, bond traders, and investment professionals who need to determine the current market price of discount instruments such as Treasury bills and commercial paper. Understanding PRICEDISC is crucial for accurate portfolio valuation and investment decision-making. Unlike traditional bonds that pay periodic interest, discount securities are sold at a price below their redemption value, with the difference representing the investor's return. The PRICEDISC formula accounts for the settlement date, maturity date, discount rate, and redemption value to provide precise pricing calculations. This formula is particularly valuable in fixed-income analysis where accuracy is paramount for risk assessment and yield calculations. Whether you're working with short-term Treasury bills or other discount instruments, mastering PRICEDISC enables you to automate complex financial calculations and maintain consistency across your investment analysis workflows. This comprehensive guide will walk you through every aspect of the formula, from basic syntax to advanced applications.
Syntax & Parameters
The PRICEDISC function follows the syntax: =PRICEDISC(settlement, maturity, discount, redemption, [basis]). The settlement parameter represents the security's settlement date, typically the date when ownership transfers. The maturity parameter specifies when the security reaches its end date and redemption value is paid. The discount parameter is the security's annual discount rate, expressed as a decimal (e.g., 0.05 for 5%). The redemption parameter represents the redemption value per $100 face value, typically 100 for most securities. The optional basis parameter defines the day count convention: 0 for US 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360. If omitted, basis defaults to 0. All date parameters must be valid Excel dates, and the discount rate must be positive. The formula calculates the price by determining the fraction of the year between settlement and maturity, then applying the discount rate to derive the present value. This calculation is fundamental for Treasury bill pricing and other short-term discount instrument valuations in financial markets.
settlementmaturitydiscountredemptionbasisPractical Examples
Treasury Bill Pricing
=PRICEDISC(DATE(2024,1,15),DATE(2024,7,15),0.035,100,0)This formula calculates the discounted price by accounting for the 181-day holding period at a 3.5% annual discount. The basis parameter is set to 0 for the standard US 30/360 day count convention used in Treasury markets.
Commercial Paper Valuation
=PRICEDISC(DATE(2024,3,1),DATE(2024,6,1),0.042,100,1)Using basis 1 (actual/actual day count) provides precise pricing for commercial paper which often uses actual day calculations. The 92-day period from settlement to maturity is calculated using actual calendar days.
Dynamic Pricing with Cell References
=PRICEDISC(A2,B2,C2,D2,0)By using cell references instead of hardcoded values, analysts can create dynamic pricing models that update automatically when input data changes. This approach scales efficiently for portfolios containing hundreds of securities.
Key Takeaways
- PRICEDISC calculates prices for discount securities per $100 face value using settlement date, maturity date, discount rate, and redemption value
- The discount rate must be expressed as a decimal (0.035 for 3.5%), and the basis parameter determines day count convention with 0 as the US standard
- Multiply PRICEDISC results by (face value/100) to convert from per-$100 pricing to actual dollar amounts for portfolio valuation
- Use cell references instead of hardcoded values to create dynamic, scalable pricing models for managing multiple securities efficiently
- Always validate results against known market prices from financial data providers to ensure formula accuracy before deploying in production environments
Pro Tips
Always verify your discount rate is expressed as a decimal (0.035 for 3.5%), not as a percentage. This is the most common data entry error with PRICEDISC.
Impact : Prevents #VALUE! errors and ensures calculations are mathematically accurate, potentially saving thousands in valuation errors.
Use named ranges for settlement, maturity, discount, and redemption parameters to create self-documenting formulas that are easier to audit and maintain across large portfolios.
Impact : Significantly improves formula readability and reduces debugging time when managing complex financial models with dozens of securities.
Create a reference table showing the impact of different basis parameters on the same security to understand which convention your data provider uses before processing large datasets.
Impact : Prevents systematic pricing errors across entire portfolios that could result from incorrect basis assumptions.
Combine PRICEDISC with TODAY() function to create dynamic pricing models that automatically update daily prices as settlement dates change: =PRICEDISC(TODAY(),B2,C2,D2,0)
Impact : Enables real-time portfolio valuation dashboards that maintain accuracy without manual daily updates.
Useful Combinations
Portfolio Weighted Average Price Calculation
=SUMPRODUCT(PRICEDISC(A2:A10,B2:B10,C2:C10,D2:D10,0),E2:E10)/SUM(E2:E10)Combine PRICEDISC with SUMPRODUCT to calculate weighted average prices across multiple discount securities. The E column contains position weights or quantities, enabling portfolio-level price analysis and performance tracking.
Price with Conditional Basis Selection
=PRICEDISC(A2,B2,C2,D2,IF(MONTH(A2)>6,1,0))Use IF statements to dynamically select the basis parameter based on settlement date or other criteria. This enables seasonal or market-specific adjustments to day count conventions within a single formula.
Price Sensitivity Analysis with Data Tables
=PRICEDISC($A$2,$B$2,C$3,$D$2,0)Combine PRICEDISC with Excel Data Tables to analyze how price changes with varying discount rates. Use absolute references for fixed parameters and relative references for the variable discount rate to create comprehensive sensitivity analyses.
Common Errors
Cause: The discount rate is entered as a percentage (e.g., 3.5) instead of a decimal (0.035), or date parameters are formatted as text rather than valid Excel date values.
Solution: Convert percentages to decimals by dividing by 100. Ensure dates are recognized as Excel date serial numbers using DATE() function or proper date formatting. Verify with ISNUMBER() and check date values with DATEVALUE().
Cause: The settlement date is equal to or after the maturity date, creating a negative or zero time period. Alternatively, the discount rate is negative or the redemption value is negative or zero.
Solution: Verify that settlement dates precede maturity dates using IF() validation. Ensure discount rates are positive values between 0 and 1. Confirm redemption values are positive numbers, typically 100 for standard securities.
Cause: Cell references in the formula point to deleted columns or rows, or the formula references cells that no longer exist after spreadsheet restructuring.
Solution: Use absolute references ($A$2) for fixed data ranges to prevent reference breaks during row/column operations. Audit formulas after major spreadsheet changes using Find & Replace to identify broken references.
Troubleshooting Checklist
- 1.Verify settlement date is earlier than maturity date; if equal or later, PRICEDISC returns #NUM! error
- 2.Confirm discount rate is a positive decimal value (0.01 to 0.99) rather than a percentage or negative number
- 3.Ensure all date parameters are recognized as Excel dates using DATE() function or proper date formatting; text dates cause #VALUE! errors
- 4.Check that redemption value is a positive number, typically 100 for standard securities; negative or zero values trigger #NUM! errors
- 5.Validate basis parameter is an integer from 0 to 4; other values cause #NUM! errors
- 6.Test formulas with known Treasury bill prices from financial data providers to verify calculation accuracy before applying to large datasets
Edge Cases
Settlement and maturity dates are very close (e.g., 1-2 days apart)
Behavior: PRICEDISC correctly calculates prices for ultra-short-term securities, but the fractional year calculation becomes highly sensitive to day count basis selection
Solution: Verify basis parameter matches your data provider's convention; even small basis differences produce noticeable price variations for short-term instruments
This is common with overnight repo transactions and same-day settlement scenarios
Discount rate is extremely low (e.g., 0.001 or 0.1%)
Behavior: PRICEDISC returns prices very close to the redemption value, reflecting minimal discount. The formula handles this mathematically correctly.
Solution: Ensure you haven't accidentally entered the discount rate as a percentage (1 instead of 0.01); verify against market data
Low discount rates occur during periods of low interest rates or for highly creditworthy issuers
Redemption value differs significantly from 100 (e.g., 95 or 110)
Behavior: PRICEDISC adjusts the price calculation proportionally, treating the redemption value as the target value rather than assuming 100
Solution: Verify redemption value matches the security specifications; non-standard redemption values are rare but possible for structured products
Most securities use 100 as redemption value; verify documentation for any exceptions
Limitations
- •PRICEDISC assumes the discount rate remains constant until maturity; it cannot model variable or adjustable rate discount securities that change rates during the holding period
- •The formula does not account for credit risk, market risk, or liquidity premiums that may affect actual market prices; it provides theoretical pricing based on stated discount rates only
- •PRICEDISC cannot handle securities with embedded options (callable, putable, or convertible features) that may affect actual prices; these require more complex valuation models
- •The function is limited to discount securities and cannot price coupon-bearing bonds or complex derivative instruments; use PRICE or PRICEMAT for those security types
Alternatives
Provides complete transparency and control over the calculation process. Allows customization for non-standard conventions or special requirements.
When: Create custom pricing models using =Redemption*(1-Discount*Days/360) for scenarios requiring specific adjustments or day count methodologies not supported by PRICEDISC.
Calculates prices for securities that pay interest at maturity. Bridges discount securities and traditional bonds for hybrid instruments.
When: Apply PRICEMAT for securities with accrued interest at maturity, providing more comprehensive pricing for instruments that combine discount and coupon features.
Compatibility
✓ Excel
Since 2007
=PRICEDISC(settlement, maturity, discount, redemption, [basis])✓Google Sheets
=PRICEDISC(settlement, maturity, discount, redemption, [basis])Google Sheets supports PRICEDISC with identical syntax. All parameters and basis options function identically to Excel versions.
✓LibreOffice
=PRICEDISC(settlement, maturity, discount, redemption, [basis])