cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
curtisk Regular Visitor
Regular Visitor

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 for visualization.

I am hoping someone can suggest a way to minic this calcualtion so that I can skip this process when data is refreshed.

In my dataset, I have payment and term, I need to calcualte the loan interest.

 

Any help is much appreciated.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Excel Rate function to build in Power BI

Hi @curtisk

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




8 REPLIES 8
Super User
Super User

Re: Excel Rate function to build in Power BI

curtisk Regular Visitor
Regular Visitor

Re: Excel Rate function to build in Power BI

Thanks for reply, but XIRR function won't work in my dataset. 

My records are row basis, each row is distinct record with loan total, payment length, payment amount, I need to calculate the interest rate based on those three varaibles.

 

Super User
Super User

Re: Excel Rate function to build in Power BI

The Excel RATE function is an iterative calculation so in all likelihood you won't get there via DAX unless you use nested VAR statements and that's just going to be ugly. You could potentially get there via M (Power Query) code using recursion. I do not understand how the RATE function manually calculates. If I had that formula, I could probably whip up a solution.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


curtisk Regular Visitor
Regular Visitor

Re: Excel Rate function to build in Power BI

Thanks for the reply.

 

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

Capture1.PNG

 

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.

Super User
Super User

Re: Excel Rate function to build in Power BI

@curtisk & @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:

image.png

 

 

 

 

Hopefully that's what you were looking for?

 

Regards,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




curtisk Regular Visitor
Regular Visitor

Re: Excel Rate function to build in Power BI

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.

Super User
Super User

Re: Excel Rate function to build in Power BI

Hi @curtisk

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




curtisk Regular Visitor
Regular Visitor

Re: Excel Rate function to build in Power BI

Thanks very much for the explanation.