Dax Function: ODDFPRICE

Category: Financial Functions

The ODDFPRICE function in Power BI is a financial function that calculates the price of a bond with an odd (irregular) first period, considering the bond’s yield, coupon rate, settlement date, and maturity date.

Purpose

  • Determines the price investors would pay for a bond when the first interest payment period is irregular.

  • Accounts for unusual first periods that may differ in length from standard intervals.

Type of Calculations

  • Calculates bond pricing for non-standard cash flows.

  • Adjusts for the irregularity in the first period while considering other bond parameters like yield and frequency.

Practical Use Cases

  1. Bond Trading: Evaluate bond prices with non-standard first payment periods for trading or investment.

  2. Portfolio Valuation: Accurately determine bond values in portfolios containing irregular cash-flow bonds.

  3. What-If Scenarios: Simulate the impact of varying coupon rates or yields on irregular-period bonds.


ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yield, redemption, frequency, [basis])
ParameterTypeDescription
settlementScalarThe bond’s settlement date. This is the date after issuance when the bond is traded to the buyer. Must be a valid date.
maturityScalarThe bond’s maturity date. The date when the principal is due. Must be a valid date.
issueScalarThe bond’s issue date. The date when the bond was first issued. Must be a valid date.
first_couponScalarThe first coupon date. The date when the first interest payment is made. Must be a valid date.
rateScalarThe bond’s annual coupon rate. Expressed as a decimal (e.g., 5% = 0.05).
yieldScalarThe bond’s annual yield. Expressed as a decimal (e.g., 4% = 0.04).
redemptionScalarThe bond’s redemption value per $100 face value. For example, 100 = par, 105 = premium.
frequencyScalarThe 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 ODDFPRICE Dax Function Works

Mathematical Formula

The ODDFPRICE function computes the bond price by:

  1. Adjusting the discounting formula to account for an irregular first period.

  2. Factoring in the bond’s coupon payments, redemption value, and yield.

  3. Incorporating the day count convention specified by the basis parameter.

The formula includes two main parts:

  • Discounting of the irregular first-period cash flows.

  • Regular discounting for subsequent periods.

What Does It Return?

  • Scalar Value: Returns the bond price as a numeric value.

  • The value represents the price per $100 face value of the bond, accounting for the irregular first period.

When Should We Use It?

  • Irregular Bonds: For bonds issued with a first coupon period that differs from regular intervals.

  • Portfolio Diversification: Valuing non-standard bonds in a diversified portfolio.

  • Pricing Irregular Bonds: Ensuring accurate pricing in bond trading platforms.

Examples

Basic Usage :

Calculate the price of a bond with the following parameters:

  • Settlement Date: 2025-01-01

  • Maturity Date: 2030-01-01

  • Issue Date: 2024-06-01

  • First Coupon Date: 2024-12-01

  • Coupon Rate: 6% (0.06)

  • Yield: 5% (0.05)

  • Redemption Value: 100

  • Frequency: 2 (Semi-annual)


ODDFPRICE(DATE(2025, 1, 1), DATE(2030, 1, 1), DATE(2024, 6, 1), DATE(2024, 12, 1), 0.06, 0.05, 100, 2)

Result: 102.50 (approximate bond price).

Column Usage

Evaluate multiple bonds with varying parameters:

BondIDSettlementMaturityIssueFirstCouponRateYieldRedemptionFrequency
12025-01-012030-01-012024-06-012024-12-010.060.051002
22025-02-012035-02-012024-09-012025-03-010.070.061001

Add a calculated column:


BondPrice = ODDFPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Issue], Bonds[FirstCoupon], Bonds[Rate], Bonds[Yield], Bonds[Redemption], Bonds[Frequency])

Advanced Usage

Combine ODDFPRICE with other financial DAX functions for scenario analysis:


PriceDifference =
ODDFPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Issue], Bonds[FirstCoupon], Bonds[Rate], Bonds[Yield1], Bonds[Redemption], Bonds[Frequency]) -
ODDFPRICE(Bonds[Settlement], Bonds[Maturity], Bonds[Issue], Bonds[FirstCoupon], Bonds[Rate], Bonds[Yield2], Bonds[Redemption], Bonds[Frequency])

Compares bond prices under two different yield scenarios.

Tips and Tricks

  • Always use consistent date formats.

  • Ensure the day count basis aligns with the bond’s terms.

  • Invalid Dates: Ensure all date parameters are valid and correctly formatted.

  • Rate and Yield Units: Express these as decimals (e.g., 5% = 0.05).

Performance Impact of ODDFPRICE DAX Function:

  • Efficient for small to moderate datasets.

  • For large-scale bond datasets, optimize calculations by pre-aggregating data.

Related Functions You Might Need

FunctionDescription
ODDFYIELDCalculates the yield of a bond with an odd first period.
PRICEComputes the price of a bond with regular periods.
YIELDDetermines the yield to maturity for a bond with standard payment periods.

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

The ODDFPRICE function calculates the price of a bond with an irregular first period based on specified parameters like settlement, maturity, coupon rate, and yield.

2. Can I use ODDFPRICE for bonds with regular periods?

No, ODDFPRICE is specifically designed for bonds with irregular first periods. Use the PRICE function for regular-period bonds.

3. What happens if I omit the basis parameter?

The function defaults to a 30/360 day count basis if the basis parameter is omitted.

4. How do I handle semi-annual coupons with ODDFPRICE?

Set the frequency parameter to 2 for semi-annual coupon payments.

5. What’s the difference between ODDFPRICE and PRICE?

ODDFPRICE accounts for an irregular first period, while PRICE is used for bonds with standard periodic payments.

Previous article
Dax Function: NPER
Next article
Dax Function: ODDFYIELD