Dax Function: ODDLPRICE
Category: Financial Functions
The ODDLPRICE function in Power BI calculates the price of a bond with an odd (irregular) last interest period. This function evaluates the price per $100 face value, taking into account the settlement date, maturity date, coupon rate, yield, and other bond-specific parameters.
Purpose
Computes the price of bonds that have an irregular final payment period.
Adjusts for the irregularities in cash flows during the bond’s last interest period.
Type of Calculations
Calculates bond pricing based on time-value-of-money principles.
Incorporates adjustments for irregular last-period cash flows in the bond’s lifecycle.
Practical Use Cases
Bond Analysis: Determine the price of bonds with non-standard final interest periods.
Investment Valuation: Analyze the market price of bonds during the last period.
Portfolio Adjustments: Ensure accurate pricing representation for bonds nearing maturity.
ODDLPRICE(settlement, maturity, last_interest, rate, yield, redemption, frequency, [basis])
| Parameter | Type | Description |
|---|---|---|
settlement | Scalar | The bond’s settlement date. The date when the bond is purchased by the buyer. Must be a valid date. |
maturity | Scalar | The bond’s maturity date. The date when the principal is repaid. Must be a valid date. |
last_interest | Scalar | The last interest payment date before maturity. Must be a valid date. |
rate | Scalar | The bond’s annual coupon rate. Expressed as a decimal (e.g., 5% = 0.05). |
yield | Scalar | The bond’s annual yield. Expressed as a decimal (e.g., 6% = 0.06). |
redemption | Scalar | The bond’s redemption value per $100 face value. For example, 100 = par, 105 = premium. |
frequency | Scalar | The number of coupon payments per year: 1 = annual, 2 = semi-annual, 4 = quarterly. |
[basis] | Scalar | (Optional) The day count basis: 0 = 30/360 (default), 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = 30E/360. |
How Does ODDLPRICE Dax Function Works
Mathematical Principle
The ODDLPRICE function calculates the present value of future cash flows, including:
Coupon Payments: Discounted periodic interest payments until maturity.
Principal Redemption: Discounted redemption value at maturity.
Odd Final Period: Adjusts for irregular cash flows in the last period using the provided parameters.
What Does It Return?
Scalar Value: Returns the bond price as a numeric value, representing the price per $100 face value.
The result accounts for the irregular last-period cash flows.
When Should We Use It?
Irregular Last-Period Bonds: For bonds with a non-standard final interest payment.
Price Comparisons: Evaluate price differences between standard and irregular bonds.
Fixed-Income Portfolio: Adjust pricing models for bonds nearing maturity with irregular schedules.
Examples
Basic Usage :
Calculate the price of a bond with:
Settlement Date:
2025-01-01Maturity Date:
2030-01-01Last Interest Date:
2029-06-01Coupon Rate: 5% (0.05)
Yield: 4.5% (0.045)
Redemption: 100
Frequency: 2 (Semi-annual)
ODDLPRICE(DATE(2025, 1, 1), DATE(2030, 1, 1), DATE(2029, 6, 1), 0.05, 0.045, 100, 2)
Result: 101.75 (approximately).
Column Usage
Compute bond prices for multiple bonds in a table:
| BondID | Settlement | Maturity | LastInterest | Rate | Yield | Redemption | Frequency |
|---|---|---|---|---|---|---|---|
| 1 | 2025-01-01 | 2030-01-01 | 2029-06-01 | 0.05 | 0.045 | 100 | 2 |
| 2 | 2025-02-01 | 2035-02-01 | 2034-08-01 | 0.06 | 0.05 | 100 | 1 |
Add a calculated column:
Price = ODDLPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Yield], Bonds[Redemption], Bonds[Frequency])
Advanced Usage
Combine ODDLPRICE with conditional logic for advanced analysis:
PriceAdjusted =
IF(
Bonds[Frequency] = 2,
ODDLPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Yield], Bonds[Redemption], Bonds[Frequency]) * 1.01,
ODDLPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Yield], Bonds[Redemption], Bonds[Frequency])
)
Adds a 1% premium for semi-annual bonds.
Tips and Tricks
Validate all date inputs to avoid calculation errors.
Ensure
rateandyieldare expressed as decimals.Basis Selection: Using inconsistent day-count conventions can lead to incorrect results.
Invalid Dates: Ensure settlement and maturity dates are logical and valid.
Performance Impact of ODDLPRICE DAX Function:
Efficient for individual or small sets of bonds.
Optimize large datasets by precomputing frequently used parameters.
Related Functions You Might Need
| Function | Description |
|---|---|
ODDLPRICE | Calculates the price of a bond with an odd last period. |
PRICE | Evaluates the price of a bond with standard payment intervals. |
ODDFYIELD | Computes the yield of a bond with an odd first interest period. |
Want to Learn More?
For more information, check out the official Microsoft documentation for ODDLPRICE 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.
It calculates the price of a bond with an irregular final interest period.
While you can use it, it’s specifically designed for bonds with irregular last-period payments. Use PRICE for regular bonds.
The function defaults to a 30/360 day count basis.
ODDLPRICE adjusts for irregular last-period cash flows, while PRICE assumes standard intervals.
Yes, the function supports pricing bonds at premium or discount.