Brian Dunning's FileMaker Custom Functions

APV ( payment ; rate ; type ; nper ; futureval )

Advanced Present Value

  Average rating: 4.5 (35 votes) Log in to vote

Lee Dolereit   Lee Dolereit
Sharp ABT
http://www.sharpabt.com.au

Share on Facebook Share on Twitter

  Sample input:
APV (709.04 ; 0.005933 ;1 ;49 ; 0 )
  Sample output:
30249.9633351023703506

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Filemaker's PV function does not have the facility to calculate Future Value on payments made in advance. This function provides the same calcuation used in the Exel / Open Office calc PV function.

payment = repayment amount made on each term

rate = Interest rate ( use 0.10 if 10% , and divide this by 12 if the interest is calculated monthly)

type = 1 for payment made in advance , 0 for arrears

nper = terms remaining / number of payments to be made

futureval = the future value of the loan at the end of the terms.

This is a transposed formula from the master formula found on Filemaker's knowledgebase website for solving financial equations beyond the scope of Filemaker's standard financial functions.


 

Comments

Robert Schaub   Robert Schaub, East Hampton, CT
Jul 26, 2011
adding the RouundPrecision parameter you can set it to 2 and get 30249.96 as a result. Also you should use better naming practice so novices suchhh as my self can better understand.

here is the updated function.



//APV ( payment ; rate ; type ; nper ; futureval ; RoundPercision)


/*
APV

==>> Copy and Paste Parameters in Order <<==

payment
rate
type
nper
futureval
RoundPercision


*/

Round(
Let(

[

y=rate;

z=nper;

a=payment;

b=type;

c=futureval

];





(((a*(b*y+1) * ((y+1)^z-1)) / y) + c)/(y+1) ^ z

);RoundPercision)



/* Advanced Present Value
Lee Dolereit, Sharp ABT

http://www.sharpabt.com.au " & */
 
Lee   Lee, Australia
Jul 26, 2011
Thanks Rob , just cleared that up.
 
comment   comment, VR
Jul 26, 2011
I am going to disagree here: rounding is a prerogative of the user. If you want a rounded result, use the Round() function. None of the native financial functions has built-in rounding - neither in Filemaker nor in Excel. That's just good practice.

Speaking of good practice, defining variables only to rename the parameters is not.
 
Lee   Lee, Australia
Jul 26, 2011
The reason why I have renamed the variables is so that the formula becomes more "readable" , for those who wish to transpose it for another purpose.
 
comment   comment, VR
Jul 27, 2011
Which one do you find more readable?


(((a*(b*y+1) * ((y+1)^z-1)) / y) + c)/(y+1) ^ z


(((payment*(type*rate+1) * ((rate+1)^periods-1)) / rate)+FV) / (rate+1)^periods
 
Lee   Lee, Australia
Jul 27, 2011
For transposition purposes , the first one.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.