Brian Dunning's FileMaker Custom Functions

Amortization_CFpub ( principal ; interest ; periods ; payment ; term ; currency )

Produces the principal remaining at the end of a fixed rate amortization period.

  Average rating: 4.6 (28 votes) Log in to vote

James Hea   James Hea - Show more from this author

Share on Facebook Share on Twitter

  Sample input:
Amortization_CFpub (171000 ; 6.95 ; 300 ; 60; "BMD" )
  Sample output:
99802.58

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

/*
Amortization_CFpub
Use this function to calculate the Principal at the end of a fixed payment period in a mortgage.
This is a tail-recursion function that accepts the following parameters:
principal - This value is the opening balance of the mortgage.
interest - This is the annual interest rate entered in full numbers i.e. 6.95 for 6.95%.
periods - The function is expecting this to be in months i.e. 12 for one year 360 for 30 years.
payment - Make sure to supply the payment using the same principal, interest and periods.
term - This value is supplied externally and should not exceed the total number of periods.
Notes: The interest is divided by 12 to derive the monthly interest rate and then by 100 to derive the actual percentage.
Author: James Hea, 2010
Email: james.m.hea@hbsc.bm
*/


Let(

[
p = principal;
i = interest/12/100;
d = periods;
c = Case(
currency = "BMD"; 365/365;
currency = "USD" ; 365/360;
365/365
);
start = 1;
m = payment;
r = p - ((p - m) + p * i * c)
];

Round(

Case(
term > d ; "" ;
term > 1 ; Amortization_CFpub( p - r; i ; d ; m ; term - 1; currency) ;
p-r

) // end Case

; 2 ) // end Round

) // end let

 

Comments

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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: