While Excel and DAX have native functions to calculate the future value of a payment series, Power Query lacks it still. So it’s time to create our own function for the Future Value (FV) for Power Query:
Excel’s function for the Present Value (FV) for Power Query
Excel’s FV function returns the future value of an investment based on a constant interest rate. Like for the PV-function, I could use the calculation logic from Greg Deckler:
https://gist.github.com/ImkeF/9c10f010ea26b6f13609669282808b08
Function Arguments
To keep it really convenient, the arguments for this function are identical with the Excel ones:
- Rate Required. The interest rate per period.
- Nper Required. The total number of payment periods in an annuity.
- Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
- Pv Optional. The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
- Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
| Set type equal to | If payments are due |
|---|---|
| 0 | At the end of the period |
| 1 | At the beginning of the period |
Please check out the following video to see how to apply this function in your solution:
Enjoy and stay queryious 😉
The choice to keep the arguments identical to Excel is the detail that landed for me. You’re not just recreating a missing function in Power Query; you’re removing friction at the exact point people usually hesitate.
Haha, I definitely needed a break from sketching country outlines on Country Draw to read about finance formulas, but it is wild how much work you have to do to replicate simple Excel functions in Power Query. The fact that it just doesn’t exist natively feels like a massive gap in the tooling.
I was pleasantly surprised to see someone tackle the missing Future Value function for Power Query, and using Greg Deckler’s calculation logic for the PV equivalent was a smart shortcut that saves a lot of trial and error. It’s a nice mental shift away from the visual precision work I do daily on PSL Rating, where scoring facial symmetry on a 1-8 scale requires a completely different kind of attention to detail.
I’m always impressed by how much logic you have to write just to mirror a simple Excel function in Power Query M. It’s a nice brain break from my usual work building transparent PNGs with hidden color layers at Tap and hold, where I focus more on visual interactions than financial formulas.