The DAX for Excel formula( CUMIPMT )

Eli_ — Mon, 30 Dec 2019 21:54:36 GMT

Hello Team,

I am trying to write a dax to calculate the Cummulative Interest paid between two period, this is what I can do in Excel using the =CUMIPMT formular.

Sample Data(this is what you need to feed the excel function)

Excel Cells, Data, Description

A2, 0.09, Interest rate per annum

A3, 30, Term in years

A4, 125000, Present Value

With this formular =CUMIPMT(A2/12,A3*12,A4,13,24,0)

The Result should be = -11135.23 // THIS IS WHAT I AM TRYING TO GET IN POWER BI USING DAX

Excel Function Parameters(fx)

Rate =0.0075

Nper = 360

Pv = 125000.00

Start_Period = 13

End_Period = 24

Type =0

Note:

There is a similar but different fomular to this that give you the "Cummulative principal" paid between two period. in Excel the formular is =CUMPRINC

The formular =CUMPRINC(A2/12,A3*12,A4,13,24,0)

The Result should be = -934.1071

I am able to get the Cummulative Principal(=CUMPRINC) with dax in power bi, but not able to get the Cumulative Interest (=CUMIPMT).

To get the Cummulative Principal.

I first "Enter Data" to Create Sample Table

Header, Values

Rate =0.0075

Nper = 360

Pv = 125000.00

Start_Period = 13

End_Period = 24

Type =0

Then Write 2 Dax Calculated Columns;

1. To get the PMT(this is another function in Excel not in Dax)

PMT = (([Pv]*[Rate]))/(1-((1+[Rate]))^(-1*[Nper]))

This gives me 1005.78

2. To get the Cummulative principal

CUMPRINC =

var p = [Pv]

var i = [Rate]

var n = [Nper]

var b = [Start_Period]

var e = [End_Period]

var m = [PMT] // this the no. 1 dax

var q= 12

RETURN (((p-m/i)*(1+(i))^(e)+m/i) - ((p-m/i)*(1+(i))^(b-1)+m/i))*(-1)

This gives me 934.1071

What I need help with, is to write a 3rd dax that give me the Cumulative interest

CUMIPMT =

Thanks in advance.

Re: The DAX for Excel formula( CUMIPMT )

TomMartens — Tue, 31 Dec 2019 20:21:40 GMT

Hey Eli_ ,

maybe you can use and adpt this blog

https://www.minceddata.info/2018/02/21/using-table-iterators-to-calculate-a-future-value/

to your needs.

Regards,

Tom

topic The DAX for Excel formula( CUMIPMT ) in DAX Commands and Tips

The DAX for Excel formula( CUMIPMT )

Re: The DAX for Excel formula( CUMIPMT )