PMT is a function in excel for the periodic payment for a loan.
In excel, The Syntax is PMT(rate, nper, pv, [fv], [type])
- Rate Required. The interest rate for the loan.
- Nper Required. The total number of payments for the loan.
- Pv Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
Yet, in DAX, there is no built in function yet for PMT. To calculate this we need to reapply for its mathmatic logic.
So, you can use
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
CUMPRINC is the accumuated principle amount for a loan
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)
Leave a Reply