Skip to content

Instantly share code, notes, and snippets.

@ImkeF

ImkeF/xls.FV.pq Secret

Created January 1, 2023 19:06
Show Gist options
  • Save ImkeF/9c10f010ea26b6f13609669282808b08 to your computer and use it in GitHub Desktop.
Save ImkeF/9c10f010ea26b6f13609669282808b08 to your computer and use it in GitHub Desktop.
Calculates the future value of an investment based on a constant interest rate
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment