How to calculate PMT and CUMPRINC in DAX

DAXVersionPMT = ((Pv*Rate))/(1-((1+Rate))^(-1*Nper))))*(-1) 
// Remind: if you are trying to calculate monthly payment but rate is annual rate, then you need to convert to monthly rate by devide to 12

In excel, the syntax is CUMPRINC(rate, nper, pv, start_period, end_period, type)

The CUMPRINC function syntax has the following arguments:

• Rate    Required. The interest rate.
• Nper    Required. The total number of payment periods.
• Pv    Required. The present value.
• Start_period    Required. The first period in the calculation. Payment periods are numbered beginning with 1.
• End_period    Required. The last period in the calculation.

• Type    Required. The timing of the payment.

In DAX right now, there is no built in function fo rCUMPRINC yet.

So, you can use the follow DAX

Accumulated Principle = var p=[PV]

var i=[Rate]

var n=[Nper]

var b=[Start_period]

var e=[End_period]

var m=[DAXVersionPMT]*(-1)

var q=12 // if rate is annual rate and you calculated you PMT as monthly period, then you need to convert to monthly rate by devide to 12, if the rate is for monthly rate, then you just need to use q=1

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

Thanks,

Your friend,Annie