Daily DAX : Day 252 MDURATION

 The MDURATION function in Power BI's DAX (Data Analysis Expressions) language calculates the modified duration of a security with an assumed par value of $100. Modified duration measures the price sensitivity of a bond or fixed-income security to changes in interest rates, adjusting the Macaulay duration for the bond's yield to maturity. It’s commonly used in financial analysis to assess the risk of bond investments.

Syntax

dax

MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, [Basis])

Parameters

    Settlement: The date the security is purchased or settled (must be a valid date).

    Maturity: The date the security matures (must be a valid date).

    Coupon: The annual coupon rate of the security (as a decimal, e.g., 0.05 for 5%).

    Yield: The annual yield to maturity of the security (as a decimal, e.g., 0.06 for 6%).

    Frequency: The number of coupon payments per year:

        1 = Annual

        2 = Semi-annual

        4 = Quarterly

    Basis (optional): The day-count convention to use for calculations (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 modified duration in years, expressed as a numeric value.

Formula for Modified Duration

Modified duration is derived from Macaulay duration and adjusts for the yield:

Modified Duration = Macaulay Duration / (1 + Yield/Frequency)

Where Macaulay duration is the weighted average time to receive the bond’s cash flows.

Use Case

The MDURATION function is primarily used in financial analysis to:

    Assess Interest Rate Risk: Modified duration estimates the percentage change in a bond’s price for a 1% change in interest rates. For example, if a bond has a modified duration of 5, a 1% increase in interest rates would decrease the bond’s price by approximately 5%.

    Portfolio Management: Helps portfolio managers evaluate how sensitive their bond investments are to interest rate fluctuations.

    Risk Management: Used by financial analysts to hedge against interest rate changes or to compare bonds with different maturities and yields.

Example

Suppose you have a bond with:

    Settlement date: January 1, 2023

    Maturity date: January 1, 2033

    Coupon rate: 5% (0.05)

    Yield to maturity: 6% (0.06)

    Frequency: Semi-annual (2)

    Basis: US 30/360 (0)

DAX Formula:

dax

ModifiedDuration = MDURATION(DATE(2023,1,1), DATE(2033,1,1), 0.05, 0.06, 2, 0)

Result: The function might return approximately 7.45 years, indicating that for a 1% increase in yield, the bond’s price would decrease by about 7.45%.

Notes

    Ensure Settlement and Maturity are valid dates, and Settlement is before Maturity.

    Coupon and Yield must be positive or zero.

    Frequency must be 1, 2, or 4.

    Basis must be between 0 and 4.

    The function assumes a $100 par value for the security.

    Use in Power BI measures or calculated columns to analyze bond data dynamically.

Practical Application in Power BI

In a Power BI report, you might use MDURATION to:

    Create a measure to calculate the modified duration for a table of bonds with different settlement dates, maturities, and yields.

    Visualize how duration changes across a portfolio to assess overall interest rate risk.

    Combine with other DAX functions (e.g., filtering by bond type or maturity) to build interactive financial dashboards.