Daily DAX : Day 161 DURATION

The DURATION function in DAX returns the Macauley duration for a security with an assumed par value of $100. The Macauley duration is a measure of a bond’s price sensitivity to changes in yield, expressed as the weighted average time (in years) until the bond’s cash flows are received. It’s a key metric in fixed-income analysis for assessing interest rate risk.

Syntax:

DURATION(<settlement>, <maturity>, <coupon>, <yld>, <frequency>, [<basis>])

    <settlement>: The bond’s settlement date (when the bond is purchased).

    <maturity>: The bond’s maturity date (when the bond expires).

    <coupon>: The bond’s annual coupon rate (as a decimal, e.g., 0.05 for 5%).

    <yld>: The bond’s annual yield (as a decimal, e.g., 0.06 for 6%).

    <frequency>: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

    <basis> (optional): The day count convention. Defaults to 0 (US 30/360) if omitted. Options:

        0: US (NASD) 30/360

        1: Actual/actual

        2: Actual/360

        3: Actual/365

        4: European 30/360

Return Value:

A decimal number representing the Macauley duration in years.

How It Works

The Macauley duration is calculated as:

    The weighted average of the time to each cash flow (coupons and principal), where the weights are the present values of those cash flows, discounted at the yield.

    It assumes a par value of $100, meaning the result is scaled to that face value, regardless of the bond’s actual price.

Example:

Suppose you have a bond with:

    Settlement date: March 22, 2025 (today)

    Maturity date: March 22, 2030 (5 years from now)

    Coupon rate: 5% (0.05)

    Yield: 6% (0.06)

    Frequency: 2 (semiannual payments)

    Basis: 0 (US 30/360)

In DAX, you could define this as a measure or calculated column. Let’s create a calculated column in a table called Bonds:

DAX

MacauleyDuration = DURATION(

    DATE(2025, 3, 22),  // Settlement

    DATE(2030, 3, 22),  // Maturity

    0.05,               // Coupon

    0.06,               // Yield

    2,                  // Frequency

    0                   // Basis

)

Result: Approximately 4.62 years. This means the bond’s cash flows are, on average, received 4.62 years from the settlement date, weighted by their present value.

Use Case

    Interest Rate Risk Assessment:

        Portfolio managers use Macauley duration to gauge how much a bond’s price will change if interest rates shift. A higher duration indicates greater sensitivity.

        Example: If yields rise by 1%, a bond with a duration of 4.62 would see its price drop by roughly 4.62% (assuming a linear approximation).

    Portfolio Immunization:

        In Power BI, you could calculate the duration for a portfolio of bonds to match the duration of liabilities, minimizing interest rate risk. For instance:

        DAX

        PortfolioDuration = AVERAGE(Bonds[MacauleyDuration])

        This could be visualized in a report to monitor risk exposure.

    Bond Comparison:

        Compare durations across bonds with different maturities, coupons, or yields to inform investment decisions. A table visual in Power BI could display settlement, maturity, coupon, yield, and the calculated duration side by side.

Practical Notes

    Dates in DAX: Dates are stored as serial numbers (days since December 30, 1899). Use the DATE function for precision, as in the example above.

    Limitations: This function isn’t supported in DirectQuery mode for calculated columns or row-level security rules, so it’s best used in Import mode.

    Related Function: There’s also MDURATION, which calculates the modified Macauley duration (adjusted for yield changes), but you asked specifically about DURATION.

Scenario in Power BI

Imagine a Bonds table with columns for Settlement, Maturity, Coupon, Yield, and Frequency. You add a calculated column with the DURATION formula above. Then, in a report, you create a table visual showing each bond’s details and its duration, perhaps adding a slicer for Yield to see how duration changes dynamically.