Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Macaulay Duration

Hello,

 

Is there a function in Power BI that will calculate the Macaulay Duration.  - The weighted average term to maturity of the cash flows from a bond?

 

In SAS its called: DURATION (times, flows, ytm);

 

thanks

Sally

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    To the best of my knowledge there is not. So I invented one.

     

    Macaulay Duration = 
    VAR __InterestRate = .06
    VAR __PeriodNumber = 2
    VAR __Table = 'Table'
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__Discount", 1 / (1 + __InterestRate/__PeriodNumber)^[Index]
            ),
            "__PV Cash Flow",[Index] * [Value] * [Discount],
            "__Current Bond Price",[Value] / (1 + __InterestRate/__PeriodNumber)^[Index]
        )
RETURN
    SUMX(__Table1,[__PV Cash Flow]) / SUMX(__Table1,[__Current Bond Price])

     

    Based on the information found here: https://www.investopedia.com/terms/m/macaulayduration.asp

     

    PBIX is attached. And here I thought that the Macaulay Duration was just 1 night...

     

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    To the best of my knowledge there is not. So I invented one.

     

    Macaulay Duration = 
    VAR __InterestRate = .06
    VAR __PeriodNumber = 2
    VAR __Table = 'Table'
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __Table,
                "__Discount", 1 / (1 + __InterestRate/__PeriodNumber)^[Index]
            ),
            "__PV Cash Flow",[Index] * [Value] * [Discount],
            "__Current Bond Price",[Value] / (1 + __InterestRate/__PeriodNumber)^[Index]
        )
RETURN
    SUMX(__Table1,[__PV Cash Flow]) / SUMX(__Table1,[__Current Bond Price])

     

    Based on the information found here: https://www.investopedia.com/terms/m/macaulayduration.asp

     

    PBIX is attached. And here I thought that the Macaulay Duration was just 1 night...