Dax Function: PRICEMAT
Category: Financial Functions
The PRICEMAT function in Power BI calculates the price per $100 face value of a security that pays interest at maturity. This function helps determine the present value of a security based on its issue date, settlement date, maturity date, interest rate, and redemption value.
Purpose
To compute the price of interest-bearing securities that make a single payment of principal and interest at maturity.
Ideal for pricing bonds or securities where accrued interest is paid at the end of the investment period.
Type of Calculations
Performs present value calculations that account for accrued interest and the number of days between specified dates.
Uses day count conventions to compute accurate pricing based on financial market standards.
Practical Use Cases
Bond Pricing: Valuing bonds or debt instruments with interest paid at maturity.
Investment Analysis: Comparing securities with different interest rates and maturity structures.
Risk Management: Assessing the impact of interest rate changes on security prices.
PRICEMAT(settlement, maturity, issue, rate, redemption, [basis])
| Parameter | Type | Description |
|---|---|---|
settlement | Scalar | The date the security is purchased or settled by the buyer. |
maturity | Scalar | The date the security expires, and the principal is repaid along with accrued interest. |
issue | Scalar | The date the security was originally issued. |
rate | Scalar | The annual coupon rate as a decimal (e.g., 5% = 0.05). |
redemption | Scalar | The redemption value per $100 face value, typically set to 100. |
[basis] | Scalar | Optional. The day count basis: 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), 4 (European 30/360). Defaults to 0. |
How Does PRICEDISC Dax Works
Mathematical Principle
The formula behind PRICEMAT calculates the present value of a security’s future cash flows:
Where:
Accrued Interest accounts for interest earned up to the settlement date.
Discounted Principal reflects the impact of the interest rate over the remaining time to maturity.
What Does It Return?
Scalar Value: The calculated price of the security per $100 face value.
Reflects the price required to achieve the specified annual rate of return, accounting for all specified dates and interest rates.
When Should We Use It?
Valuing Securities with Single-Payment Interest: Ideal for bonds or notes where interest and principal are combined in a single maturity payment.
Yield Analysis: Calculate how pricing changes with varying interest rates.
Portfolio Valuation: Assess securities with different issue and settlement dates to estimate total portfolio value.
Examples
Basic Usage :
Calculate the price of a security with:
Settlement date: April 11, 2025.
Maturity date: October 11, 2025.
Issue date: January 11, 2025.
Annual coupon rate: 6% (0.06).
Redemption value: $100.
Day count basis: US 30/360 (
0).
PRICEMAT(DATE(2025, 4, 11), DATE(2025, 10, 11), DATE(2025, 1, 11), 0.06, 100, 0)
Result: $97.50
Column Usage
For a table of securities:
| SecurityID | Settlement | Maturity | Issue | Rate | Redemption |
|---|---|---|---|---|---|
| 1 | 2025-04-11 | 2025-10-11 | 2025-01-11 | 0.06 | 100 |
| 2 | 2025-06-15 | 2026-06-15 | 2025-01-15 | 0.08 | 100 |
Add a calculated column for security prices:
SecurityPrice = PRICEMAT(Securities[Settlement], Securities[Maturity], Securities[Issue], Securities[Rate], Securities[Redemption], 0)
Advanced Usage
Combine PRICEMAT with conditional logic to adjust prices based on redemption value thresholds:
DynamicPrice =
IF(Securities[Redemption] > 100,
PRICEMAT(Securities[Settlement], Securities[Maturity], Securities[Issue], Securities[Rate], Securities[Redemption] - 5, 0),
PRICEMAT(Securities[Settlement], Securities[Maturity], Securities[Issue], Securities[Rate], Securities[Redemption], 0)
)
Result: Calculates prices with a modified redemption value for securities above a certain threshold.
Tips and Tricks
Ensure
settlement,maturity, andissuedates are formatted correctly to avoid calculation errors.Use the appropriate
basisfor your financial market or analysis requirements.Entering incorrect
rateformats (e.g., using 5 instead of 0.05).Overlooking the day count convention when comparing securities across markets.
Performance Impact of PRICEDISC DAX Function:
Efficient for individual calculations.
For large datasets, pre-calculate frequently used parameters to enhance performance.
Related Functions You Might Need
| Function | Description |
|---|---|
PRICE | Calculates the price of a bond with periodic interest payments. |
PRICEDISC | Computes the price of a discounted security with no periodic interest. |
YIELD | Calculates the annual yield of a security based on its price. |
Want to Learn More?
For more information, check out the official Microsoft documentation for PRICEMAT You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
The PRICEMAT function computes the price per $100 face value of a security that pays interest at maturity.
No, use the PRICEDISC function for zero-coupon bonds, as PRICEMAT assumes the security pays interest at maturity.
The basis parameter determines how days between dates are counted, influencing the price calculation. For example, 0 uses the US 30/360 convention.
The function returns an error, as settlement must occur before the security matures.
Yes, it can be applied to past securities to compare their pricing trends over time.