Brian Dunning's FileMaker Custom Functions

LoanAmortTable ( OpeningBalance ; PmtPerTerm ; Terms ; Rate ; PmtAdv ; StartTermNo ; ColumnSeperator )

Amortization table

  Average rating: 4.2 (19 votes) Log in to vote

Lee Dolereit   Lee Dolereit
Sofco
http://www.sofco.com.au

Share on Facebook Share on Twitter

  Sample input:
LoanAmortTable ( 10000; 229; 60; 13.7975/100/12; 1; 0; " ")
  Sample output:
0 $ 10,000.00 $ 229.00 $ 229.00 $ 0.00 $ 9,771.00
1 $ 9,771.00 $ 229.00 $ 116.65 $ 112.35 $ 9,654.35
2 $ 9,654.35 $ 229.00 $ 118.00 $ 111.00 $ 9,536.35
3 $ 9,536.35 $ 229.00 $ 119.35 $ 109.65 $ 9,417.00
4 $ 9,417.00 $ 229.00 $ 120.72 $ 108.28 $ 9,296.28
5 $ 9,296.28 $ 229.00 $ 122.11 $ 106.89 $ 9,174.17
6 $ 9,174.17 $ 229.00 $ 123.52 $ 105.48 $ 9,050.65
7 $ 9,050.65 $ 229.00 $ 124.94 $ 104.06 $ 8,925.71
8 $ 8,925.71 $ 229.00 $ 126.37 $ 102.63 $ 8,799.34
9 $ 8,799.34 $ 229.00 $ 127.83 $ 101.17 $ 8,671.51
10 $ 8,671.51 $ 229.00 $ 129.30 $ 99.70 $ 8,542.21
11 $ 8,542.21 $ 229.00 $ 130.78 $ 98.22 $ 8,411.43
12 $ 8,411.43 $ 229.00 $ 132.29 $ 96.71 $ 8,279.14
13 $ 8,279.14 $ 229.00 $ 133.81 $ 95.19 $ 8,145.33
14 $ 8,145.33 $ 229.00 $ 135.35 $ 93.65 $ 8,009.98
15 $ 8,009.98 $ 229.00 $ 136.90 $ 92.10 $ 7,873.08
16 $ 7,873.08 $ 229.00 $ 138.48 $ 90.52 $ 7,734.60
17 $ 7,734.60 $ 229.00 $ 140.07 $ 88.93 $ 7,594.53
18 $ 7,594.53 $ 229.00 $ 141.68 $ 87.32 $ 7,452.85
19 $ 7,452.85 $ 229.00 $ 143.31 $ 85.69 $ 7,309.54
20 $ 7,309.54 $ 229.00 $ 144.96 $ 84.04 $ 7,164.58
21 $ 7,164.58 $ 229.00 $ 146.62 $ 82.38 $ 7,017.96
22 $ 7,017.96 $ 229.00 $ 148.31 $ 80.69 $ 6,869.65
23 $ 6,869.65 $ 229.00 $ 150.01 $ 78.99 $ 6,719.64
24 $ 6,719.64 $ 229.00 $ 151.74 $ 77.26 $ 6,567.90
25 $ 6,567.90 $ 229.00 $ 153.48 $ 75.52 $ 6,414.42
26 $ 6,414.42 $ 229.00 $ 155.25 $ 73.75 $ 6,259.17
27 $ 6,259.17 $ 229.00 $ 157.03 $ 71.97 $ 6,102.14
28 $ 6,102.14 $ 229.00 $ 158.84 $ 70.16 $ 5,943.30
29 $ 5,943.30 $ 229.00 $ 160.66 $ 68.34 $ 5,782.64
30 $ 5,782.64 $ 229.00 $ 162.51 $ 66.49 $ 5,620.13
31 $ 5,620.13 $ 229.00 $ 164.38 $ 64.62 $ 5,455.75
32 $ 5,455.75 $ 229.00 $ 166.27 $ 62.73 $ 5,289.48
33 $ 5,289.48 $ 229.00 $ 168.18 $ 60.82 $ 5,121.30
34 $ 5,121.30 $ 229.00 $ 170.12 $ 58.88 $ 4,951.18
35 $ 4,951.18 $ 229.00 $ 172.07 $ 56.93 $ 4,779.11
36 $ 4,779.11 $ 229.00 $ 174.05 $ 54.95 $ 4,605.06
37 $ 4,605.06 $ 229.00 $ 176.05 $ 52.95 $ 4,429.01
38 $ 4,429.01 $ 229.00 $ 178.08 $ 50.92 $ 4,250.93
39 $ 4,250.93 $ 229.00 $ 180.12 $ 48.88 $ 4,070.81
40 $ 4,070.81 $ 229.00 $ 182.19 $ 46.81 $ 3,888.62
41 $ 3,888.62 $ 229.00 $ 184.29 $ 44.71 $ 3,704.33
42 $ 3,704.33 $ 229.00 $ 186.41 $ 42.59 $ 3,517.92
43 $ 3,517.92 $ 229.00 $ 188.55 $ 40.45 $ 3,329.37
44 $ 3,329.37 $ 229.00 $ 190.72 $ 38.28 $ 3,138.65
45 $ 3,138.65 $ 229.00 $ 192.91 $ 36.09 $ 2,945.74
46 $ 2,945.74 $ 229.00 $ 195.13 $ 33.87 $ 2,750.61
47 $ 2,750.61 $ 229.00 $ 197.37 $ 31.63 $ 2,553.24
48 $ 2,553.24 $ 229.00 $ 199.64 $ 29.36 $ 2,353.60
49 $ 2,353.60 $ 229.00 $ 201.94 $ 27.06 $ 2,151.66
50 $ 2,151.66 $ 229.00 $ 204.26 $ 24.74 $ 1,947.40
51 $ 1,947.40 $ 229.00 $ 206.61 $ 22.39 $ 1,740.79
52 $ 1,740.79 $ 229.00 $ 208.98 $ 20.02 $ 1,531.81
53 $ 1,531.81 $ 229.00 $ 211.39 $ 17.61 $ 1,320.42
54 $ 1,320.42 $ 229.00 $ 213.82 $ 15.18 $ 1,106.60
55 $ 1,106.60 $ 229.00 $ 216.28 $ 12.72 $ 890.32
56 $ 890.32 $ 229.00 $ 218.76 $ 10.24 $ 671.56
57 $ 671.56 $ 229.00 $ 221.28 $ 7.72 $ 450.28
58 $ 450.28 $ 229.00 $ 223.82 $ 5.18 $ 226.46
59 $ 226.46 $ 229.06 $ 226.46 $ 2.60 $ 0.00

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

This recursive function will output a full amortization table and correctly calculate the first payment allocation if the loan is geared as a 'payment in advance' loan. The output column seperator can be any string you like, but for most purposes, a tab character makes it most human 'readable'. Also if using a tab character, the output can be copied and pasted directly into an Excel spreadsheet.

 

Comments

tom   tom, ShiftPoint
Oct 31, 2019
Lee
I like what you are doing here, unfortunately FileMaker says that there are too many parameters in script, am I doing something wrong?
 
tom   tom, ShiftPoint
Nov 4, 2019
I made some simplications to the above for my purposes
1) Removed option for a zero start date, that is just the amount less 1 loan payment, so a smaller loan
2) Removed variable for columns, and removed refernce to 2nd CF for formatting data
3) Interest is always truncated so any fractional cents falls to the principle, and preventing actual interest collected to higher than stated rate

/*
LoanAmortization ( OpeningBalance ; PmtDue ; Rate )
This CF will output a full amortization table based on the input parameters
Make sure the calculation result is Text, not a number.

PARAMETERS :
OpeningBalance - The original principal / amount of the loan
PmtDue - The regular payment amount
Rate - the interest rate based on payment frequency and day calculated, example weekly: yearly rate/365 * 7, Monthly: yearly rate/12.


Filemaker field ! (Don't forget to use tab stops on your output field, it looks nicer)
*/

Let (
[
$StartTermNo = $StartTermNo +1;
CollectedAmount = If( OpeningBalance < PmtDue ; OpeningBalance + Truncate ( OpeningBalance * Rate ; 2 ) ; PmtDue ) ;
Interest = Truncate ( OpeningBalance * Rate ; 2 ) ;
Principal = Round( CollectedAmount ; 2) - Interest;
NewBalance = If( OpeningBalance - Principal < 0 ; 0 ; If( $StartTermNo = 0 ; 0 ; OpeningBalance - Principal ) ) ;
$TotalInterest = $TotalInterest + Interest ;
$TotalPayments = $TotalPayments + CollectedAmount ;
$TotalPrincipal = $TotalPrincipal + Principal
];

$StartTermNo & "|" & "$" & OpeningBalance & "|" & "$" & CollectedAmount & "|" & "$" & Principal & "|" & "$" & Interest & "|" & "$" & NewBalance & ΒΆ &
If( NewBalance > 0 ; LoanAmortization ( NewBalance ; PmtDue ; Rate ) )

)
 

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 16 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: