- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Excel Rate function to build in Power BI

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

curtisk

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
07:19 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-25-2018
01:50 AM

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

Proud to be a Datanaut!

8 REPLIES 8

Stachu

Super User

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
07:51 AM

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

curtisk

Regular Visitor

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
08:02 AM

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.

Greg_Deckler

Super User

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
08:17 AM

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.

Proud to be a Datanaut!

curtisk

Regular Visitor

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
08:52 AM

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

Super User

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2018
08:59 PM

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.

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

Proud to be a Datanaut!

curtisk

Regular Visitor

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-24-2018
09:01 AM

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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-25-2018
01:50 AM

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

Proud to be a Datanaut!

curtisk

Regular Visitor

Re: Excel Rate function to build in Power BI

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-25-2018
07:10 AM

Thanks very much for the explanation.