Excel Rate function to build in Power BI

[Anonymous]

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.

[Greg_Deckler]

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.

[Anonymous]

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]

@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]

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 · Accepted solution]

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]

Thanks very much for the explanation.

[Anonymous]

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.

[Stachu]

maybe XIRR will do?
https://msdn.microsoft.com/en-us/query-bi/dax/xirr-function-dax
haven't used it myself, but I found this post
http://community.powerbi.com/t5/Desktop/Solution-to-XIRR-with-Terminal-Values/td-p/26957