Skip to content

Instantly share code, notes, and snippets.

@carpii
Created August 14, 2013 22:03
Show Gist options
  • Save carpii/6236117 to your computer and use it in GitHub Desktop.
Save carpii/6236117 to your computer and use it in GitHub Desktop.
Example result set from your data I haven't tested any of the results heavily, even the compound interest, but it will be trivial to fix if it is wrong
id effectiveDate daysSinceLastPayment type amount totalPrincipal totalFees totalPayments totalInterest balance
----------- ------------------------------------------------------ -------------------- -------------------- -------------- -------------- -------------- -------------- -------------- --------------
1 2012-11-07 22:58:15.000 NULL PRINCIPAL .00000 .00000 .00000 .00000 .00000 .00000
2 2012-11-08 22:58:15.000 NULL PRINCIPAL 150.00000 150.00000 .00000 .00000 .00000 150.00000
3 2012-11-09 22:58:15.000 NULL PRINCIPAL 100.00000 250.00000 .00000 .00000 .00000 250.00000
4 2012-11-10 22:58:15.000 NULL FEE 35.00000 250.00000 35.00000 .00000 .00000 285.00000
5 2012-11-11 22:58:15.000 0 PAYMENT 59.40000 225.60000 .00000 59.40000 .00000 225.60000
6 2012-11-12 22:58:15.000 1 PRINCIPAL 200.00000 425.60000 .00000 59.40000 11.28000 436.88000
7 2012-11-13 22:58:15.000 2 FEE 35.00000 425.60000 35.00000 59.40000 53.84000 514.44000
8 2012-11-14 22:58:15.000 0 PAYMENT 78.57000 425.60000 .00000 137.97000 10.27000 435.87000
9 2012-11-15 22:58:15.000 1 PRINCIPAL 250.00000 675.60000 .00000 137.97000 31.55000 707.15000
10 2012-11-16 22:58:15.000 2 FEE 35.00000 675.60000 35.00000 137.97000 99.11000 809.71000
11 2012-11-17 22:58:15.000 0 PAYMENT 92.54000 675.60000 .00000 230.51000 41.57000 717.17000
12 2012-11-18 22:58:15.000 1 FEE 35.00000 675.60000 35.00000 230.51000 75.35000 785.95000
13 2012-11-19 22:58:15.000 0 PAYMENT 90.42000 675.60000 .00000 320.93000 19.93000 695.53000
14 2012-11-20 22:58:15.000 1 FEE 35.00000 675.60000 35.00000 320.93000 53.71000 764.31000
15 2012-11-21 22:58:15.000 2 PRINCIPAL 50.00000 725.60000 35.00000 320.93000 121.27000 881.87000
16 2012-11-22 22:58:15.000 3 PRINCIPAL 200.00000 925.60000 35.00000 320.93000 230.11000 1190.71000
17 2012-11-23 22:58:15.000 0 PAYMENT 87.49000 925.60000 .00000 408.42000 177.62000 1103.22000
18 2012-11-24 22:58:15.000 1 FEE 35.00000 925.60000 35.00000 408.42000 223.90000 1184.50000
19 2012-11-25 22:58:15.000 0 PAYMENT 80.78000 925.60000 .00000 489.20000 178.12000 1103.72000
20 2012-11-26 22:58:15.000 1 FEE 35.00000 925.60000 35.00000 489.20000 224.40000 1185.00000
21 2012-11-27 22:58:15.000 2 PRINCIPAL 200.00000 1125.60000 35.00000 489.20000 316.96000 1477.56000
22 2012-11-28 22:58:15.000 0 PAYMENT 80.26000 1125.60000 .00000 569.46000 271.70000 1397.30000
23 2012-11-29 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 569.46000 327.98000 1488.58000
24 2012-11-30 22:58:15.000 0 PAYMENT 88.11000 1125.60000 .00000 657.57000 274.87000 1400.47000
25 2012-12-01 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 657.57000 331.15000 1491.75000
26 2012-12-02 22:58:15.000 0 PAYMENT 89.80000 1125.60000 .00000 747.37000 276.35000 1401.95000
27 2012-12-03 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 747.37000 332.63000 1493.23000
28 2012-12-04 22:58:15.000 0 PAYMENT 80.14000 1125.60000 .00000 827.51000 287.49000 1413.09000
29 2012-12-05 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 827.51000 343.77000 1504.37000
30 2012-12-06 22:58:15.000 0 PAYMENT 88.11000 1125.60000 .00000 915.62000 290.66000 1416.26000
31 2012-12-07 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 915.62000 346.94000 1507.54000
32 2012-12-08 22:58:15.000 0 PAYMENT 87.23000 1125.60000 .00000 1002.85000 294.71000 1420.31000
33 2012-12-09 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1002.85000 350.99000 1511.59000
34 2012-12-10 22:58:15.000 0 PAYMENT 86.16000 1125.60000 .00000 1089.01000 299.83000 1425.43000
35 2012-12-11 22:58:15.000 0 PAYMENT 86.16000 1125.60000 .00000 1175.17000 213.67000 1339.27000
36 2012-12-12 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1175.17000 269.95000 1430.55000
37 2012-12-13 22:58:15.000 0 PAYMENT 85.11000 1125.60000 .00000 1260.28000 219.84000 1345.44000
38 2012-12-14 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1260.28000 276.12000 1436.72000
39 2012-12-15 22:58:15.000 0 PAYMENT 84.08000 1125.60000 .00000 1344.36000 227.04000 1352.64000
40 2012-12-16 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1344.36000 283.32000 1443.92000
41 2012-12-17 22:58:15.000 0 PAYMENT 83.07000 1125.60000 .00000 1427.43000 235.25000 1360.85000
42 2012-12-18 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1427.43000 291.53000 1452.13000
43 2012-12-19 22:58:15.000 0 PAYMENT 82.08000 1125.60000 .00000 1509.51000 244.45000 1370.05000
44 2012-12-20 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1509.51000 300.73000 1461.33000
45 2012-12-21 22:58:15.000 0 PAYMENT 81.11000 1125.60000 .00000 1590.62000 254.62000 1380.22000
46 2012-12-22 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1590.62000 310.90000 1471.50000
47 2012-12-23 22:58:15.000 0 PAYMENT 80.17000 1125.60000 .00000 1670.79000 265.73000 1391.33000
48 2012-12-24 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1670.79000 322.01000 1482.61000
49 2012-12-25 22:58:15.000 0 PAYMENT 79.24000 1125.60000 .00000 1750.03000 277.77000 1403.37000
50 2012-12-26 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1750.03000 334.05000 1494.65000
51 2012-12-27 22:58:15.000 0 PAYMENT 78.33000 1125.60000 .00000 1828.36000 290.72000 1416.32000
52 2012-12-28 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 1828.36000 347.00000 1507.60000
53 2012-12-29 22:58:15.000 0 PAYMENT 74.52000 1125.60000 .00000 1902.88000 307.48000 1433.08000
54 2012-12-30 22:58:15.000 1 FEE 25.00000 1125.60000 25.00000 1902.88000 363.76000 1514.36000
55 2012-12-31 22:58:15.000 0 PAYMENT 99.52000 1125.60000 .00000 2002.40000 289.24000 1414.84000
56 2013-01-01 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 2002.40000 345.52000 1506.12000
57 2013-01-02 22:58:15.000 0 PAYMENT 104.70000 1125.60000 .00000 2107.10000 275.82000 1401.42000
58 2013-01-03 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 2107.10000 332.10000 1492.70000
59 2013-01-04 22:58:15.000 0 PAYMENT 75.10000 1125.60000 .00000 2182.20000 292.00000 1417.60000
60 2013-01-05 22:58:15.000 1 FEE 35.00000 1125.60000 35.00000 2182.20000 348.28000 1508.88000
61 2013-01-06 22:58:15.000 2 PRINCIPAL 300.00000 1425.60000 35.00000 2182.20000 460.84000 1921.44000
62 2013-01-07 22:58:15.000 0 PAYMENT 73.45000 1425.60000 .00000 2255.65000 422.39000 1847.99000
63 2013-01-08 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2255.65000 493.67000 1954.27000
64 2013-01-09 22:58:15.000 0 PAYMENT 84.05000 1425.60000 .00000 2339.70000 444.62000 1870.22000
65 2013-01-10 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2339.70000 515.90000 1976.50000
66 2013-01-11 22:58:15.000 2 FEE 35.00000 1425.60000 70.00000 2339.70000 658.46000 2154.06000
67 2013-01-12 22:58:15.000 0 PAYMENT 86.22000 1425.60000 .00000 2425.92000 642.24000 2067.84000
68 2013-01-13 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2425.92000 713.52000 2174.12000
69 2013-01-14 22:58:15.000 0 PAYMENT 81.85000 1425.60000 .00000 2507.77000 666.67000 2092.27000
70 2013-01-15 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2507.77000 737.95000 2198.55000
71 2013-01-16 22:58:15.000 0 PAYMENT 87.39000 1425.60000 .00000 2595.16000 685.56000 2111.16000
72 2013-01-17 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2595.16000 756.84000 2217.44000
73 2013-01-18 22:58:15.000 0 PAYMENT 83.05000 1425.60000 .00000 2678.21000 708.79000 2134.39000
74 2013-01-19 22:58:15.000 1 FEE 35.00000 1425.60000 35.00000 2678.21000 780.07000 2240.67000
75 2013-01-20 22:58:15.000 2 PRINCIPAL 100.00000 1525.60000 35.00000 2678.21000 922.63000 2483.23000
76 2013-01-21 22:58:15.000 0 PAYMENT 82.00000 1525.60000 .00000 2760.21000 875.63000 2401.23000
77 2013-01-22 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 2760.21000 951.91000 2512.51000
78 2013-01-23 22:58:15.000 0 PAYMENT 84.99000 1525.60000 .00000 2845.20000 901.92000 2427.52000
79 2013-01-24 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 2845.20000 978.20000 2538.80000
80 2013-01-25 22:58:15.000 0 PAYMENT 84.77000 1525.60000 .00000 2929.97000 928.43000 2454.03000
81 2013-01-26 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 2929.97000 1004.71000 2565.31000
82 2013-01-27 22:58:15.000 0 PAYMENT 83.69000 1525.60000 .00000 3013.66000 956.02000 2481.62000
83 2013-01-28 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 3013.66000 1032.30000 2592.90000
84 2013-01-29 22:58:15.000 0 PAYMENT 79.49000 1525.60000 .00000 3093.15000 987.81000 2513.41000
85 2013-01-30 22:58:15.000 1 FEE 25.00000 1525.60000 25.00000 3093.15000 1064.09000 2614.69000
86 2013-01-31 22:58:15.000 2 FEE 35.00000 1525.60000 60.00000 3093.15000 1216.65000 2802.25000
87 2013-02-01 22:58:15.000 3 FEE 30.00000 1525.60000 90.00000 3093.15000 1445.49000 3061.09000
88 2013-02-02 22:58:15.000 0 PAYMENT 219.75000 1525.60000 .00000 3312.90000 1315.74000 2841.34000
89 2013-02-03 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 3312.90000 1392.02000 2952.62000
90 2013-02-04 22:58:15.000 2 FEE 35.00000 1525.60000 70.00000 3312.90000 1544.58000 3140.18000
91 2013-02-05 22:58:15.000 0 PAYMENT 81.02000 1525.60000 .00000 3393.92000 1533.56000 3059.16000
92 2013-02-06 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 3393.92000 1609.84000 3170.44000
93 2013-02-07 22:58:15.000 0 PAYMENT 79.51000 1525.60000 .00000 3473.43000 1565.33000 3090.93000
94 2013-02-08 22:58:15.000 1 FEE 35.00000 1525.60000 35.00000 3473.43000 1641.61000 3202.21000
95 2013-02-09 22:58:15.000 2 PRINCIPAL 150.00000 1675.60000 35.00000 3473.43000 1794.17000 3504.77000
96 2013-02-10 22:58:15.000 0 PAYMENT 75.66000 1675.60000 .00000 3549.09000 1753.51000 3429.11000
97 2013-02-11 22:58:15.000 1 FEE 35.00000 1675.60000 35.00000 3549.09000 1837.29000 3547.89000
98 2013-02-12 22:58:15.000 0 PAYMENT 86.63000 1675.60000 .00000 3635.72000 1785.66000 3461.26000
99 2013-02-13 22:58:15.000 1 FEE 35.00000 1675.60000 35.00000 3635.72000 1869.44000 3580.04000
100 2013-02-14 22:58:15.000 0 PAYMENT 80.61000 1675.60000 .00000 3716.33000 1823.83000 3499.43000
101 2013-02-15 22:58:15.000 1 FEE 35.00000 1675.60000 35.00000 3716.33000 1907.61000 3618.21000
102 2013-02-16 22:58:15.000 0 PAYMENT 85.80000 1675.60000 .00000 3802.13000 1856.81000 3532.41000
103 2013-02-17 22:58:15.000 1 FEE 35.00000 1675.60000 35.00000 3802.13000 1940.59000 3651.19000
104 2013-02-18 22:58:15.000 0 PAYMENT 81.62000 1675.60000 .00000 3883.75000 1893.97000 3569.57000
105 2013-02-19 22:58:15.000 1 FEE 25.00000 1675.60000 25.00000 3883.75000 1977.75000 3678.35000
106 2013-02-20 22:58:15.000 2 FEE 35.00000 1675.60000 60.00000 3883.75000 2145.31000 3880.91000
107 2013-02-21 22:58:15.000 3 FEE 30.00000 1675.60000 90.00000 3883.75000 2396.65000 4162.25000
108 2013-02-22 22:58:15.000 0 PAYMENT 217.75000 1675.60000 .00000 4101.50000 2268.90000 3944.50000
109 2013-02-23 22:58:15.000 1 FEE 25.00000 1675.60000 25.00000 4101.50000 2352.68000 4053.28000
110 2013-02-24 22:58:15.000 2 FEE 35.00000 1675.60000 60.00000 4101.50000 2520.24000 4255.84000
111 2013-02-25 22:58:15.000 3 FEE 30.00000 1675.60000 90.00000 4101.50000 2771.58000 4537.18000
112 2013-02-26 22:58:15.000 4 FEE 40.00000 1675.60000 130.00000 4101.50000 3106.70000 4912.30000
113 2013-02-27 22:58:15.000 0 PAYMENT 93.87000 1675.60000 36.13000 4195.37000 3106.70000 4818.43000
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment