Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Excel Rate function to build in Power BI

Hello, I am trying to streamline the process if I can create a calculation similar to Excel Rate function. Currently, I have to do the calculation in Excel first, then I import the data to power bi...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi Anonymous

     

    Interesting - yes I think you can modify this to do what you're looking for.

     

    First off, both the RATE function in Excel and my earlier DAX code return a per period interest rate, that is the interest rate per period that will result in the loan being paid of in the specified number of periods.

     

    From your description, you would like an annualized version of this rate, assuming each period is a month.

     

    As a starting point, if we treat a month as 365/12 days, then this formula should give you the answer you want
    (I replaced 365 in the original formula with the PeriodLengthDays variable which is 365/12):

    Rate =
VAR PeriodLengthDays = 365 / 12
VAR Cashflow =
    SELECTCOLUMNS (
        GENERATESERIES (
            1,
            Loans[Payment Length] * PeriodLengthDays + 1,
            PeriodLengthDays
        ),
        "Date", [Value],
        "Cashflow", IF ( [Value] = 1, - Loans[Loan Total], Loans[Payment Amount] )
    )
RETURN
    XIRR ( Cashflow, [Cashflow], [Date] )

    Let's call the rate returned by this formula R_annual, an annual rate.

    Then the rate in each period (i.e. month) is

    R_period = (1+R_annual)^(1/12) - 1

     

    That is, if you compounded interest at the rate R_period over 12 periods, you would get an overall rate of R_annual

     

    If you wanted to get this rate in Excel, you could use the formula

    R_annual = (1+RATE(Payment Length, Payment Amount, Loan Total))^12 - 1

     

    If you need something slightly different, I'm sure you could tweak the above formula.

     

    Regards,

    Owen

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks for the reply.

 

Basically, I am looking for i in the formula, the others are given.

 

Webpage reference: http://www.frickcpa.com/tvom/TVOM_PV_Annuity.asp

There is no equation for in terms of i as the reult usually need to reference from a table.

OwenAuger's avatar
OwenAuger
Super User
8 years ago

Anonymous & Greg_Deckler

 

This is an interesting one - you can actually use XIRR to perform the same job as the Excel RATE function by preparing the function arguments appropriately.

 

See sample pbix here.

 

Let's suppose you have a table called Loans with columns:

  • Loan Total
  • Payment Length
  • Payment Amount

 

Then create a calculated column in DAX as follows:

Rate = 
VAR Cashflow =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, Loans[Payment Length] * 365 + 1, 365 ),
        "Date", [Value],
        "Cashflow", IF ( [Value] = 1, - Loans[Loan Total], Loans[Payment Amount] )
    )
RETURN
    XIRR ( Cashflow, [Cashflow], [Date] )

 

This code creates a series of dates that are 365 days apart (i.e. time intervals of 1.0), with a cashflow of -Loans[Loan Total] on the first date (time 0), and Loans[Payment Amount] on the subsequent yearly dates.

 

This gives the same result as the Excel RATE function:

 

 

 

 

Hopefully that's what you were looking for?

 

Regards,

Owen :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks for the suggestion.

    Looks like my interest rates is 1/12 of it supposed to be, because my payment length is "no. of months".

    What should I change in the formula GENERATESERIES to accomdate if length of payment is "month"?

     

    Thanks again.

    • OwenAuger's avatar
      OwenAuger
      Super User
      8 years ago

      Hi Anonymous

       

      Interesting - yes I think you can modify this to do what you're looking for.

       

      First off, both the RATE function in Excel and my earlier DAX code return a per period interest rate, that is the interest rate per period that will result in the loan being paid of in the specified number of periods.

       

      From your description, you would like an annualized version of this rate, assuming each period is a month.

       

      As a starting point, if we treat a month as 365/12 days, then this formula should give you the answer you want
      (I replaced 365 in the original formula with the PeriodLengthDays variable which is 365/12):

      Rate =
VAR PeriodLengthDays = 365 / 12
VAR Cashflow =
    SELECTCOLUMNS (
        GENERATESERIES (
            1,
            Loans[Payment Length] * PeriodLengthDays + 1,
            PeriodLengthDays
        ),
        "Date", [Value],
        "Cashflow", IF ( [Value] = 1, - Loans[Loan Total], Loans[Payment Amount] )
    )
RETURN
    XIRR ( Cashflow, [Cashflow], [Date] )

      Let's call the rate returned by this formula R_annual, an annual rate.

      Then the rate in each period (i.e. month) is

      R_period = (1+R_annual)^(1/12) - 1

       

      That is, if you compounded interest at the rate R_period over 12 periods, you would get an overall rate of R_annual

       

      If you wanted to get this rate in Excel, you could use the formula

      R_annual = (1+RATE(Payment Length, Payment Amount, Loan Total))^12 - 1

       

      If you need something slightly different, I'm sure you could tweak the above formula.

       

      Regards,

      Owen

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thanks very much for the explanation.