Dax Function: ODDLYIELD

Category: Financial Functions

The ODDLYIELD function in Power BI calculates the yield of a bond with an odd (irregular) last interest period. The yield is the effective interest rate earned by the bondholder, taking into account the unique structure of the final interest period.

Purpose

  • Provides the annualized yield for bonds with irregular last periods.

  • Adjusts for irregularities in bond cash flows for more accurate yield evaluation.

Type of Calculations

  • Computes the effective yield based on the bond’s cash flows and timing.

  • Adjusts calculations to account for the non-standard duration or payment structure of the last interest period.

Practical Use Cases

  1. Bond Analysis: Evaluate the yield for bonds nearing maturity with non-standard final periods.

  2. Investment Decisions: Compare yields of different bonds, including those with irregularities.

  3. Portfolio Optimization: Determine accurate returns on investment for bonds with unique payment schedules.


ODDLYIELD(settlement, maturity, last_interest, rate, price, redemption, frequency, [basis])
ParameterTypeDescription
settlementScalarThe bond’s settlement date. The date the buyer purchases the bond. Must be a valid date.
maturityScalarThe bond’s maturity date. The date the principal is repaid. Must be a valid date.
last_interestScalarThe last interest payment date before maturity. Must be a valid date.
rateScalarThe bond’s annual coupon rate. Expressed as a decimal (e.g., 5% = 0.05).
priceScalarThe bond’s price per $100 face value.
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 ODDLYIELD Dax

The ODDLYIELD function calculates the bond yield by solving for the rate rr in the bond pricing formula:

Where:

  • P: Bond price.

  • C: Coupon payment.

  • F: Redemption value.

  • r: Yield (to be determined).

  • n: Number of periods.

  • Adjustments are made for the irregular last-period cash flow.

What Does It Return?

  • Scalar Value: Returns the annualized yield of the bond as a numeric value.

  • The result is expressed as a decimal, representing the effective rate of return.

When Should We Use It?

  • Irregular Last-Period Bonds: For bonds with non-standard final periods.

  • Yield Comparison: Analyze the returns on bonds with and without irregular periods.

  • Fixed-Income Analysis: Use for detailed yield calculations in financial reporting.

Examples

Basic Usage :

Calculate the yield for a bond with:

  • Settlement Date: 2025-01-01

  • Maturity Date: 2030-01-01

  • Last Interest Date: 2029-06-01

  • Coupon Rate: 5% (0.05)

  • Price: 102

  • Redemption: 100

  • Frequency: 2 (Semi-annual)


ODDLYIELD(DATE(2025, 1, 1), DATE(2030, 1, 1), DATE(2029, 6, 1), 0.05, 102, 100, 2)

Result: 0.046 (4.6% yield approximately).

Column Usage

Compute bond yields for multiple bonds in a table:

BondIDSettlementMaturityLastInterestRatePriceRedemptionFrequency
12025-01-012030-01-012029-06-010.051021002
22025-02-012035-02-012034-08-010.061051001

Add a calculated column:


Yield = ODDLYIELD(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Price], Bonds[Redemption], Bonds[Frequency])

Advanced Usage

Combine ODDLYIELD with conditional logic for scenario analysis:


AdjustedYield =
IF(
Bonds[Redemption] > 100,
ODDLYIELD(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Price], Bonds[Redemption], Bonds[Frequency]) * 1.02,
ODDLYIELD(Bonds[Settlement], Bonds[Maturity], Bonds[LastInterest], Bonds[Rate], Bonds[Price], Bonds[Redemption], Bonds[Frequency])
)

Applies a 2% premium for bonds redeemed above par.

Tips and Tricks

  • Use consistent day-count conventions for better comparability.

  • Validate all date inputs to avoid errors.

  • Invalid Basis: Ensure the basis matches the bond terms to avoid inaccuracies.

  • Non-Standard Frequencies: Check that frequency values align with market norms.

Performance Impact of ODDLYIELD DAX Function:

  • Efficient for individual bond yield calculations.

  • For large datasets, consider preprocessing static inputs like rate and frequency to improve performance.

Related Functions You Might Need

FunctionDescription
ODDLPRICECalculates the price of a bond with an odd last period.
YIELDEvaluates the yield of a bond with standard payment intervals.
ODDFYIELDComputes 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 ODDLYIELD 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 ODDLYIELD function calculate?

It calculates the annualized yield for a bond with an irregular last interest period.

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

No, it’s specifically designed for bonds with non-standard last-period payments.

3. What happens if the basis parameter is omitted?

The function defaults to a 30/360 day count basis.

4. How is ODDLYIELD different from YIELD?

ODDLYIELD adjusts for irregular last-period cash flows, while YIELD assumes standard intervals.

5. Can ODDLYIELD handle bonds priced above par?

Yes, it accounts for bonds at premium or discount.

Previous article
Dax Function: ODDLPRICE
Next article
Dax Function: PDURATION