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):
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| (Rate as number, Nper as number, Pv as number, optional Fv_ as number, optional Type_ as number) => | |
| let | |
| // Source for the algorithm: https://www.experts-exchange.com/articles/1948/A-Guide-to-the-PMT-FV-IPMT-and-PPMT-Functions.html | |
| Type = if Type_ is null then 0 else Type_, | |
| Fv = if Fv_ = null then 0 else Fv_, | |
| PMT = | |
| Rate / ( Number.Power ( 1 + Rate, Nper ) -1) | |
| * – (Pv * Number.Power( 1+ Rate, Nper ) + Fv) | |
| / ( 1 + Rate * Type ) | |
| in | |
| PMT , | |
| documentation = [ | |
| Documentation.Name = " Xls.PMT ", | |
| Documentation.Description = " Returns the payment for a loan based on constant payments and a constant interest rate. ", | |
| Documentation.LongDescription = " Returns the payment for a loan based on constant payments and a constant interest rate. ", | |
| Documentation.Category = " Xls.Financial ", | |
| Documentation.Source = " www.TheBIcountant.com . https://wp.me/p6lgsG-2vX . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " let | |
| Rate = 0.08, | |
| Nper = 10, | |
| Pv = 10000, | |
| Fv_ = 0, | |
| Type_ = 0, | |
| FunctionCall = | |
| fnPMT(Rate, Nper, Pv, Fv_, Type_ ) | |
| in | |
| FunctionCall ", | |
| Result = " -1490,29488697075 | |
| "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
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 😉
Pingback: Payment Function in Power Query – Curated SQL
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!