Excel Rate function to build in Power BI

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