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.
