Payment function (PMT) for Power Query

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):


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))

view raw

Xls.PMT.pq

hosted with ❤ by GitHub

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 😉

Comments (2) Write a comment

  1. Pingback: Payment Function in Power Query – Curated SQL

  2. 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.

    Reply

Leave a Reply