LoanAmortTable ( OpeningBalance ; PmtPerTerm ; Terms ; Rate ; PmtAdv ; StartTermNo ; ColumnSeperator )
Amortization table
Average rating: 4.2 (19 votes) Log in to vote
|
Lee Dolereit Sofco http://www.sofco.com.au |
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
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, 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, 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 ) ) ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.