Excel PRICEMAT Function
Calculate the price of securities that pay interest at maturity. Learn PRICEMAT syntax, examples, and how to value Treasury bills and bonds effectively.
Excel
Google SheetsExcelGoogle Sheets=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])Quick Answer
PRICEMAT function PRICEMAT function calculates the price per $100 face value of a security that pays interest at maturity. Essential for valuing Treasury bills, commercial paper, and bonds that pay all interest when they mature rather than periodically.
=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])- Syntax: `=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])` Example: `=PRICEMAT(DATE(2025,3,15), DATE(2025,12,31), DATE(2025,1,1), 0.045, 0.04, 0)` returns 103.70, calculating the price of a Treasury bill with a 4.5% coupon rate and 4% yield trading at a premium.
Practical Examples
Basic Treasury Bill Pricing
Calculate the price of a Treasury bill that pays interest at maturity
Corporate Bond at Maturity
Value a short-term corporate bond that pays all interest at maturity
Comparing Different Yield Scenarios
Analyze how changing yields affect security prices
European Day Count Basis
Calculate price using European 30/360 day count convention
Investment Comparison with Data Table
Create a sensitivity analysis for multiple securities
Common Errors and Solutions
PRICEMAT returns #NUM! error for date validation
Settlement date is on or after maturity date, or dates are invalid
Ensure settlement < maturity and all dates are valid Excel dates. The chronological order must be: issue date < settlement date < maturity date.
Use data validation to ensure proper date order before calculation
PRICEMAT returns #NUM! error for negative rates
Rate or yld (yield) is negative
Both rate and yield must be non-negative values (expressed as decimals, e.g., 0.05 for 5%). Check that you're using decimal format rather than percentage format.
Validate that rate and yield parameters are >= 0
PRICEMAT returns #NUM! error for invalid basis
Basis parameter is outside the range 0-4
Use valid basis values: 0 (US 30/360), 1 (Actual/actual), 2 (Actual/360), 3 (Actual/365), or 4 (European 30/360). Omit the parameter to use the default basis of 0.
Create data validation for basis input (0-4)
PRICEMAT returns #VALUE! error
Dates entered as text instead of Excel date values
Use DATE() function or ensure dates are formatted as Excel date serial numbers. Convert text dates using DATEVALUE() or reformat cells as Date type.
Always use DATE(year, month, day) format for date inputs or reference cells formatted as dates
Best Practices and Pro Tips
Use Decimal Format for Rates
Always enter rates and yields as decimals (0.05 for 5%) rather than percentages (5). This is the standard convention for Excel financial functions.
Consistent Day Count Basis
Maintain consistency in day count basis across related calculations. US Treasury securities typically use basis 0 (US 30/360), while corporate bonds may use basis 1 (Actual/actual).
Understanding Price Output
PRICEMAT returns price per $100 face value. Multiply the result by (face value / 100) to get the actual price. For example, for a $10,000 face value security, multiply PRICEMAT result by 100.
Date Order Matters
The chronological order must be: issue date < settlement date < maturity date. Violating this order will result in #NUM! error.
Use with Data Tables
Combine PRICEMAT with Excel's Data Table feature to create sensitivity analyses showing how prices change with different yield scenarios.
Need Help with Excel PRICEMAT 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.
ExcelSheetsThe ACCRINTM function calculates accrued interest for securities that pay interest at maturity, essential for bond valuation and analysis.
ExcelSheetsCalculate bond prices with odd first periods. Learn ODDFPRICE syntax, examples, and securities valuation in Excel and Google Sheets.
ExcelSheetsCalculate the price of discounted securities. Learn PRICEDISC syntax, examples, and how to value Treasury bills and discount bonds effectively.
ExcelSheets