Daily DAX : Day 246 ODDLYIELD

 The ODDLYIELD function in Power BI DAX (Data Analysis Expressions) calculates the yield of a security with an odd (irregular) last period. This is typically used for bonds or other fixed-income securities that have a non-standard final coupon period, meaning the last interest payment period is shorter or longer than the regular coupon periods.

Syntax

dax

ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])

Parameters

    settlement: The date when the security is purchased.

    maturity: The date when the security matures.

    last_interest: The date of the last interest payment before the settlement.

    rate: The security's annual coupon rate (interest rate).

    pr: The security's price per $100 face value.

    redemption: The security's redemption value per $100 face value (usually 100).

    frequency: The number of coupon payments per year (1 = annual, 2 = semi-annual, 4 = quarterly).

    [basis]: (Optional) The day count basis for calculating interest. Defaults to 0 (US 30/360). Other options:

        1: Actual/Actual

        2: Actual/360

        3: Actual/365

        4: European 30/360

Return Value

The function returns the yield of the security as a decimal (e.g., 0.05 for 5%).

Use Case

The ODDLYIELD function is used in financial analysis to determine the yield of bonds with irregular last coupon periods. This is common in bonds where the final interest payment doesn't align with the standard coupon schedule due to specific issuance or maturity dates. Analysts use this to:

    Compare the yield of bonds with odd periods to standard bonds.

    Assess investment returns for portfolio management.

    Price bonds accurately in financial models.

Example

Suppose you have a bond with:

    Settlement date: January 15, 2025

    Maturity date: June 30, 2025

    Last interest date: December 31, 2024

    Coupon rate: 6% (0.06)

    Price: $99.50 per $100 face value

    Redemption value: $100

    Frequency: Semi-annual (2)

    Basis: US 30/360 (0)

In Power BI, you can calculate the yield as follows:

dax

Yield = ODDLYIELD(

    DATE(2025, 1, 15), 

    DATE(2025, 6, 30), 

    DATE(2024, 12, 31), 

    0.06, 

    99.5, 

    100, 

    2, 

    0

)

This might return a yield like 0.065 (6.5%), which reflects the annualized return considering the odd last period.

Notes

    Ensure all dates are valid and in the correct order (last_interest < settlement < maturity).

    The function assumes the security pays interest periodically and has an irregular final period.

    If parameters are invalid (e.g., negative price or rate), it returns an error.

Practical Application

In a Power BI report, you might use ODDLYIELD in a financial dashboard to:

    Display yields for a portfolio of bonds with varying coupon schedules.

    Compare yields across bonds to identify the most attractive investments.

    Integrate with other DAX functions to calculate metrics like total return or duration.