# Future Value function (FV) for Power Query

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

view raw

xls.FV.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 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 😉