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:


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 😉

Comment (1) Write a comment

  1. Pingback: Calculating Future Value for Power Query – Curated SQL

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz