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:
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, Pmt as number, optional Pv_ as number, optional Type_ as number) => | |
| let | |
| // Source for general algorithm: https://community.powerbi.com/t5/Community-Blog/F-G-Excel-to-DAX-Translation/ba-p/1061026 | |
| Type = if Type_ is null then 0 else Type_, | |
| Pv = if Pv_ = null then 0 else Pv_, | |
| FV = | |
| if Rate = 0 | |
| then -1 * ( (Pmt * Nper) + Pv) | |
| else -1 * ( | |
| ( Pv * Number.Power( 1 + Rate, Nper) ) + | |
| Pmt * ( 1 + Rate * Type) * | |
| (( Number.Power(1 + Rate, Nper) -1 ) / Rate ) ) | |
| in | |
| FV, | |
| documentation = [ | |
| Documentation.Name = " Xls.FV ", | |
| Documentation.Description = " Calculates the future value of an investment based on a constant interest rate ", | |
| Documentation.LongDescription = " Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment. ", | |
| Documentation.Category = " Xls.Financial ", | |
| Documentation.Source = " www.TheBIcountant.com . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " let | |
| Rate = 0.06/12, | |
| nPer = 10, | |
| Pmt = -200, | |
| Pv = -500, | |
| Type = 1, | |
| FunctionCall = XlsFV(Rate, nPer, Pmt, Pv, Type) | |
| in | |
| FunctionCall ", | |
| Result = " 2581,40337406013 | |
| "]}] | |
| 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 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 😉
Pingback: Calculating Future Value for Power Query – Curated SQL