MDURATION

How to Use the Power BI DAX function MDURATION

What is Bond Duration?

Before diving into the MDURATION function, let’s review bond duration. Bond duration measures how sensitive a bond is to changes in interest rates. It takes into account the bond’s maturity, coupon rate, and yield to maturity. The longer the duration, the more sensitive the bond is to interest rate changes.

There are three types of bond duration:

1. Macaulay duration: Weights the present value of each cash flow by the time until it is received and calculates the average.

2. Modified duration: Measures the percentage change in bond price for a given change in interest rates.

3. Effective duration: Measures the sensitivity of a bond’s price to changes in its yield, including changes in option-adjusted spreads.

For the purposes of this article, we will focus on modified duration.

How to Use the MDURATION Function

The MDURATION function in Power BI calculates the modified duration of a security with periodic interest payments. It takes four arguments: settlement, maturity, coupon, and yield.

Arguments

1. Settlement: The security’s settlement date, represented as a date in Excel date format.

2. Maturity: The security’s maturity date, represented as a date in Excel date format.

3. Coupon: The security’s annual coupon rate.

4. Yield: The security’s annual yield to maturity.

Syntax


Example

Let’s say we have a bond with a $1,000 face value, a maturity date of March 1, 2025, a coupon rate of 5%, and a yield to maturity of 4%. The settlement date is January 1, 2021.

To calculate the bond’s modified duration in Power BI, we can use the following formula:


The result is 3.81.

Using MDURATION in Power BI

Now that we know how to use the MDURATION function, let's look at how we can use it in Power BI.

1. Open Power BI and create a new report.

2. Select the Modeling tab and click on New Measure.

3. Name the measure "Modified Duration" and use the MDURATION function to calculate the modified duration.

For example, if we have a table named "Bonds" with columns for settlement, maturity, coupon, and yield, we can create a measure using the MDURATION function like this:


Modified Duration =

MDURATION(

MIN(Bonds[Settlement]),

MAX(Bonds[Maturity]),

MIN(Bonds[Coupon]),

MIN(Bonds[Yield])

)


4. Drag and drop the Bonds table onto the report canvas.

5. Add the Modified Duration measure to a visual to display the bond durations for each security.

The MDURATION function in Power BI is a powerful tool for calculating the modified duration of securities with periodic interest payments. By using this function, you can easily calculate bond durations without the need for manual calculations in Excel. Use the steps outlined in this article to incorporate the MDURATION function into your Power BI reports and gain greater insights into your fixed income investments.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

15-17 July 2024
16-18 September 2024
11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)