While Excel and DAX have native functions to calculate the payment amount for a loan, Power Query lacks it still. As this is a very useful function, it’s time to create our own Payment function (PMT) for Power Query:
Excel’s payment function (PMT) for Power Query
Excel’s PMT function returns the payment for a loan based on constant payments and a constant interest rate. Using some clever math, it can be deducted via one formula (see rows 9-11):
https://gist.github.com/ImkeF/5d4ac5db08df63c695fad416f7af9526
Function Arguments
To keep it really convenient, the arguments for this function are identical with the Excel ones:
-
- 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.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
- Type Optional. The number 0 (zero) or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period 1 At the beginning of the period
Remarks
- The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.
If using custom functions is new to you, please check out the following video to see how to apply them in your solution:
Enjoy and stay queryious 😉
Dear Imke
The function is very cool. Just like you have a function to calculate PMT, can you guide if we can prepare a query to calculate interest based on debit and credit transactions’ list with dates of transactions. I need to calculate interest on daily bases – based on Debits / Credits, and add a row at the end of each month for total monthly interest. Overall I need dummy ledger like statement. I intend to do this in Power Query because if I can successfully do it for one case, I intend to use that for all cases. I had posted this as a query in another forum about 4-5 months back, but I am yet to get a solution.
OK! This so reminds me of my undergrad college finance classes where we actually had to use that formula in addition to look-up tables in the back of our finance book to “approximate” the intrest and principal payments on a loans, future value, and present value of cash flows. Very cool formula Imke and thanks for the trip down memory lane!