Skip to content

Instantly share code, notes, and snippets.

@ImkeF

ImkeF/Xls.PMT.pq Secret

Last active January 1, 2023 20:51
Show Gist options
  • Save ImkeF/5d4ac5db08df63c695fad416f7af9526 to your computer and use it in GitHub Desktop.
Save ImkeF/5d4ac5db08df63c695fad416f7af9526 to your computer and use it in GitHub Desktop.
Returns the payment for a loan based on constant payments and a constant interest rate.
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment