Daily DAX : Day 184 COUPPCD

 The COUPPCD function in Power BI DAX (Data Analysis Expressions) is used to calculate the previous coupon payment date before the settlement date for a security (typically a bond) with periodic interest payments. It’s part of the financial functions in DAX, designed to help with bond-related calculations.

Syntax

COUPPCD(settlement, maturity, frequency, [basis])

Parameters

    settlement: The date when the bond is purchased or settled (must be a valid date).

    maturity: The date when the bond expires (must be a valid date).

    frequency: The number of coupon payments per year:

        1 = Annual

        2 = Semi-annual

        4 = Quarterly

    basis (optional): The day-count convention for calculating interest. Defaults to 0 if omitted:

        0 = US (NASD) 30/360

        1 = Actual/actual

        2 = Actual/360

        3 = Actual/365

        4 = European 30/360

Return Value

The function returns the date of the previous coupon payment before the settlement date.

Use Case

COUPPCD is primarily used in financial analysis for bonds to determine the last coupon payment date relative to the settlement date. This is critical for:

    Calculating accrued interest between the last coupon payment and the settlement date.

    Analyzing bond cash flows and scheduling coupon payments.

    Supporting bond pricing models by identifying the timing of interest payments.

Example

Suppose you’re analyzing a bond with:

    Settlement date: April 14, 2025

    Maturity date: December 31, 2030

    Coupon frequency: Semi-annual (2)

    Day-count basis: US 30/360 (0)

You can use COUPPCD to find the last coupon payment date before April 14, 2025.

dax

PreviousCouponDate = COUPPCD(DATE(2025, 4, 14), DATE(2030, 12, 31), 2, 0)

Result: The function might return December 31, 2024, assuming semi-annual payments align with June 30 and December 31. This tells you the bond paid its last coupon on December 31, 2024, before the settlement date.

Notes

    Dates must be valid and entered using the DATE function or a date column.

    Settlement date must be before maturity date, or the function returns an error.

    The function assumes regular coupon schedules and doesn’t account for irregular first or last coupons.

    Use with other DAX financial functions like COUPNCD (next coupon date) or ACCRINT (accrued interest) for comprehensive bond analysis.

Practical Application

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

    Calculate accrued interest for a portfolio of bonds.

    Visualize coupon payment schedules.

    Compare bond performance based on settlement and coupon timing.