PMT Function in Power Query

[Anonymous]

PMT function exists in DAX but I need to do this calculation in Power Query. 

Does anyone have a workaround for this?

[edhans]

You can either do the math manually, or write a custom function. Here is a function that does it:

(PV, r, n) =>
let
    Payment = (r * PV) / (1 - Number.Power(1 + r, -n))
in
    Payment

• Create a new blank query.
• Open the Advanced Editor
• Erase everything.
• Paste in the above formula
• Rename the query (probably Query1) to fnPMT (or whatever you want to call it, I just like custom functions to start with fn*

Now just use it like normal.

I added a custom column and typed = fnPMT([Present Value], [Rate], [Term]) and it did the work.

I used the base formula from here @Anonymous - you may need to divide your rate by the periods in a year depending on how your data is laid out. In that case it would be fnPMT([Present Value], [Rate]/12, [Term])

[Anonymous]

The difference between DAX's PMT calculation and this is that this does not consider Future VAlue (FV) in the formula. 

[edhans]

What this helpful @Anonymous ? Any questions?

[Greg_Deckler]

@Anonymous You can find the formula for PMT here: P-Q Excel to DAX Translation - Microsoft Power BI Community Should be able to translate it to M since it is all base arithmatic.