Dax Function: PRICE

Category: Financial Functions

The PRICE function in Power BI calculates the price per $100 face value of a security that pays periodic interest, based on settlement and maturity dates, interest rate, yield, redemption value, and the number of days in the coupon period.

Purpose

  • To evaluate the market price of a bond or similar financial security.

  • Assists in investment decisions by determining whether a bond is priced above or below its intrinsic value.

Type of Calculations

  • Computes the price of a bond by considering its periodic interest payments, time to maturity, and yield to maturity.

  • Based on standard financial principles for bond pricing.

Practical Use Cases

  1. Bond Valuation: Analyze the market price of bonds relative to their intrinsic value.

  2. Investment Decisions: Assess whether a bond is overvalued or undervalued.

  3. Portfolio Analysis: Calculate bond prices to evaluate their contribution to portfolio performance.


PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
ParameterTypeDescription
settlementScalarThe settlement date, or the date the bond is traded to the buyer.
maturityScalarThe maturity date, or the date the bond expires and the face value is repaid.
rateScalarThe annual coupon rate of the bond.
yldScalarThe annual yield of the bond.
redemptionScalarThe bond’s redemption value per $100 face value (usually 100).
frequencyScalarNumber of coupon payments per year: 1 (annual), 2 (semi-annual), 4 (quarterly).
[basis]ScalarOptional. 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 PRICE Dax Works

Mathematical Principle

The bond price is the present value of its future cash flows, discounted by the yield:

  1. Coupon Payments: Periodic interest payments calculated as:

  2. Discounted Cash Flows:

    • Each coupon payment is discounted at the bond’s yield to maturity.

  3. Redemption Value: Final principal payment at maturity is also discounted to its present value.

The formula combines these to calculate the bond’s price.

What Does It Return?

  • Scalar Value: The calculated price of the bond per $100 face value.

  • Returns a positive numeric value representing the bond price.

When Should We Use It?

  1. Bond Pricing Models: Use in models that compare bond prices across different market conditions.

  2. Yield Analysis: Estimate the price sensitivity of bonds to changes in yield.

  3. Financial Reports: Generate bond valuation reports for financial or investment dashboards.

Examples

Basic Usage :

Calculate the price of a bond with a 5% annual coupon rate, a 3% yield, $100 redemption value, semi-annual payments, and a maturity 5 years from settlement.


PRICE(DATE(2025, 4, 11), DATE(2030, 4, 11), 0.05, 0.03, 100, 2, 0)

Result: $107.85

Column Usage

Suppose you have a table of bonds with different parameters:

BondIDSettlementMaturityRateYieldRedemptionFrequency
12025-04-112030-04-110.050.031002
22025-04-112035-04-110.040.0351001

Add a calculated column for bond prices:


BondPrice = PRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Rate], Bonds[Yield], Bonds[Redemption], Bonds[Frequency], 0)

Advanced Usage

Combine PRICE with other DAX functions to calculate bond prices under different yield scenarios:


ScenarioPrice =
IF(Bonds[Yield] > 0.04,
PRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Rate], 0.05, Bonds[Redemption], Bonds[Frequency], 0),
PRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Rate], 0.03, Bonds[Redemption], Bonds[Frequency], 0)
)

Result: Outputs bond prices based on a yield threshold.

Tips and Tricks

  • Use consistent date formats for settlement and maturity.

  • Ensure that frequency matches the bond’s payment schedule.

  • Incorrectly using basis can lead to inaccurate price calculations.

  • Ignoring the day count convention of the bond’s market.

Performance Impact of PRICE DAX Function:

  • Efficient for scalar calculations.

  • When working with large datasets, optimize by precomputing common parameters.

Related Functions You Might Need

FunctionDescription
YIELDCalculates the yield of a bond based on its price and other parameters.
DURATIONCalculates the Macaulay duration of a bond, useful for interest rate sensitivity analysis.
COUPDAYBSReturns the number of days from the beginning of the coupon period to the settlement date.

Want to Learn More?
For more information, check out the official Microsoft documentation for PRICE 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.

1. What does the PRICE function calculate?

The PRICE function calculates the market price of a bond based on coupon rate, yield, and other parameters.

2. Can I use the PRICE function for zero-coupon bonds?

Yes, set the rate parameter to 0 for zero-coupon bonds.

3. What is the role of the frequency parameter?

The frequency specifies the number of coupon payments per year (e.g., annual, semi-annual, or quarterly).

4. How do I handle the day count basis?

Use the optional basis parameter to match the bond’s market convention for day counts.

5. What happens if settlement is after maturity?

The function will return an error as this scenario is invalid.

Previous article
Dax Function: PPMT
Next article
Dax Function: PRICEDISC