APV ( payment ; rate ; type ; nper ; futureval )
Advanced Present Value
Average rating: 4.5 (35 votes) Log in to vote
|
Lee Dolereit Sharp ABT http://www.sharpabt.com.au |
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, 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, Australia Jul 26, 2011 |
|
| Thanks Rob , just cleared that up. | ||
|
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, 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, 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, Australia Jul 27, 2011 |
|
| For transposition purposes , the first one. | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.