# How do I set up the formulas to properly split monthly loan payments to principal and interest?

This has been a small annoyance for months now: the formulas GNUCash produced via loan repayment wizard don't match up with my lender's amortization tables. The formulas use pmt() functions, like this payment formula:

``````pmt( 0.02250 / 12.00 : i : 174.00 : 12063.94 : 0 : 0 )
``````

Which yields a payment of 81.30.

But I think my loans are using something more complicated that varies by days in month/pay period, judging by how DirectLoan is accounting the payments:

``````Month   Pmt        Principal    Interest

Jan'11  \$81.38  \$60.06  \$21.32
Dec'10  \$81.38  \$61.33  \$20.05
Nov'10  \$81.38  \$59.13  \$22.25
Oct'10  \$81.38  \$60.41  \$20.97
Sep'10  \$81.38  \$61.00  \$20.38
Aug'10  \$81.38  \$58.07  \$23.31
``````

Anyone know what formulas I should be using for a standard US student consolidation loan, and how to represent them in GNUCash?

The proper calculation likely has something to do with the simple daily interest formula. Not sure if/how to determine "days since last payment".

• It seems doubtful that DirectLoan is using the simple daily interest formula as you have posted. The reason being, as shown in my answer, that you need a daily interest rate of 0.00585% to match DirectLoan's interest figure but you need a rate of 0.00618% to amortise the loan in 174 months. The figures really don't match up: You cannot amortise the loan in 174 months with the interest rate implied in DirectLoan's table. Dec 30, 2016 at 10:07

If your rate is variable, then it's hard to predict with a formula. I'd just make sure you enter each part separately.

If you have a loan for which the rate is fixed, or changes in a predetermined manner, then it's possible to predict principal/interest split.

• Consolidated student loans are fixed interest rate. Mine is 2.25 percent. Jun 12, 2011 at 5:43
• Does it depend on the day of the month that the payment is posted? It doesn't look like it's based on number of days in the month. Jun 12, 2011 at 5:45
• It depends on both, according to the simple interest formula posted above. I pay on a specific business day, and the number of days between payments varies based on how many days remained in the previous month and whether payment was shifted for landing on a weekend. Dec 5, 2011 at 16:37

Does the `12063.94` represent your current payoff amount or does it represent the balance from your statement?

The current payoff amount is often a number you get if you log into your account online and it represents what it would take today to pay off the debt. That number increases over the course of a month as you accrue that month's interest until your statement date.

According to my calculations, `12063.94` would be your payoff amount had you logged in about halfway between your statement dates with 174 months remaining.

Here are two methods, the first I would say is more standard.

Method 1. For equal months, assuming nominal 2.25% APR compounded monthly

With

``````s is the initial loan principal
r is the monthly interest rate
n is the number of months
d is the regular monthly payment
``````

by standard loan formula

``````s = 12063.94
r = 0.02250/12
n = 174
d = (r (1 + r)^n s)/((1 + r)^n - 1) = 81.3212
``````

The interest and principal repayment in month `n` are

``````i[n] = d + (r s - d) (1 + r)^(n - 1)
pr[n] = (d - r s) (r + 1)^(n - 1)
``````

E.g. at the end of month `n = 1` the interest and principal repayment are

``````i[1] = d + (r s - d) (1 + r)^(n - 1) = r s = 22.6199
pr[1] = (d - r s) (r + 1)^(n - 1) = d - r s = 58.7013

pr[1] + i[1] = 81.3212 = d
``````

Plotting over all 174 months

Method 2. Applying the simple daily interest formula as posted by the OP

``````Jul 28 2010 to Aug 28 2010 is 31 days
Aug 28 2010 to Sep 28 2010 is 31 days

s = \$12063.94
i = daily interest rate
``````

Try

``````31 i s = 23.31            ∴ i = 0.0000623292
31 i (s - 58.07) = 20.38  ∴ i = 0.0000547582
``````

The rates are too different. Try adjusting for dates falling on a weekend.

``````Jul 28 2010 is a Wednesday
Aug 28 2010 is a Saturday
Sep 28 2010 is a Tuesday
``````

So extending the first repayment date to Monday Aug 30.

``````33 i s = 23.31            ∴ i = 0.0000585517
29 i (s - 58.07) = 20.38  ∴ i = 0.0000585346
``````

Much better agreement on the rate, so continuing ...

``````Tue Sep 28 2010 to Thu Oct 28 2010 is 30 days

30 i (s - 58.07 - 61) = 20.97  ∴ i = 0.0000585188

Nov 28 2010 is a Sunday so adjusting:
Thu Oct 28 2010 to Mon Nov 29 2010 is 32 days

32 i (s - 58.07 - 61 - 60.41) = 22.25  ∴ i = 0.000058506

Mon Nov 29 2010 to Tue Dec 28 2010 is 29 days

29 i (s - 58.07 - 61 - 60.41 - 59.13) = 20.05  ∴ i = 0.000058466

Tue Dec 28 2010 to Fri Jan 28 2011 is 31 days

31 i (s - 58.07 - 61 - 60.41 - 59.13 - 61.33) = 21.32  ∴ i = 0.0000584616
``````

So the rates are all fairly similar which indicates the method is correct.

``````avg = (0.0000585517 +
0.0000585346 +
0.0000585188 +
0.000058506 +
0.000058466 +
0.0000584616) / 6 = 0.0000585064
``````

It's not clear how that connects to the stated APR of 2.25%.

``````0.0000585064 * 365.25 = 2.13695 %
``````

or, alternatively, how many days per month does the rate imply?

``````0.0225 / 12 = 0.001875 per month

0.001875 / 0.0000585064 = 32.0478 days
``````

There doesn't seem to be a way to connect the daily rate of 0.00585% to 2.25% APR.

Taking the average daily rate one might expect to be able to reconstruct a decent amortisation table. Actually, to construct one that amortises to a zero balance a daily rate of 0.006186095% was required.

This actually comes a lot closer to an APR of 2.25%.

``````0.00006186095 * 365 = 2.25792 %
``````

Here is the Mathematica amortisation program used and its output.

First building tables of the adjusted dates and the number of days between them.

``````nonshifteddates = NestList[Take[DateList[# + {0, 1, 0}], 3] &, {2010, 7, 28}, 174];

adjust[date_] := Module[{dayname, newdate = date},
dayname = DateString[date, "DayName"];
If[dayname == "Saturday", newdate = Take[DateList[date + {0, 0, 2}], 3]];
If[dayname == "Sunday", newdate = Take[DateList[date + {0, 0, 1}], 3]];
newdate]

dayslist = QuantityMagnitude@DateDifference[#1, #2, "Day"] & @@@
``````

{33, 29, 30, 32, 29, 31, 31, 28, 31, 32, 29, 30, 32, 30, 30, 31, 30, 33, 29, 29, 33, 28, 31, 32, 29, 31, 31, 30, 30, 31, 31, 28, 32, 29, 31, 31, 30, 33, 28, 31, 32, 29, 31, 28, 31, 30, 33, 28, 31, 32, 29, 31, 31, 30, 33, 28, 29, 30, 32, 29, 31, 31, 30, 33, 28, 31, 32, 28, 31, 32, 29, 30, 32, 30, 30, 31, 30, 33, 29, 28, 31, 31, 30, 30, 31, 31, 32, 29, 30, 32, 30, 28, 33, 28, 31, 32, 29, 31, 31, 30, 30, 31, 31, 28, 32, 29, 31, 31, 30, 33, 28, 31, 32, 29, 31, 31, 29, 30, 32, 29, 31, 31, 30, 33, 28, 31, 32, 28, 30, 30, 31, 30, 33, 29, 30, 32, 29, 31, 31, 28, 31, 32, 29, 30, 32, 30, 30, 31, 30, 33, 29, 28, 31, 31, 30, 30, 31, 31, 32, 29, 30, 32, 30, 29, 32, 29, 31, 31, 30, 33, 28, 31, 32, 29}

Next, the input values.

``````balance = 12063.94;
payment = 81.38;
rate = 0.00006186095;
``````

Now the main program.

``````output = {};

calc[n_] := Module[{days, interest, principal, newbalance},
days = dayslist[[n]];
interest = Round[days*rate*balance, 0.01];
principal = payment - interest;
newbalance = Chop[balance - principal];
AppendTo[output,
{DateString[adjusteddates[[n]], {"Day", " ", "MonthNameShort", " '", "YearShort"}],
DateString[adjusteddates[[n + 1]], {"Day", " ", "MonthNameShort", " '", "YearShort"}],
balance, days, principal, interest, newbalance}];
balance = newbalance]

Array[calc, 174];
``````

Finally, here is the output.

``````TableForm@Prepend[output,
{"From Date", "To Date", "Amount", "Days", "Principal", "Interest", "Balance"}]

From Date       To Date         Amount       Days    Principal  Interest   Balance
28 Jul '10      30 Aug '10      12063.9      33      56.75      24.63      12007.2
30 Aug '10      28 Sep '10      12007.2      29      59.84      21.54      11947.4
28 Sep '10      28 Oct '10      11947.4      30      59.21      22.17      11888.1
28 Oct '10      29 Nov '10      11888.1      32      57.85      23.53      11830.3
29 Nov '10      28 Dec '10      11830.3      29      60.16      21.22      11770.1
28 Dec '10      28 Jan '11      11770.1      31      58.81      22.57      11711.3
28 Jan '11      28 Feb '11      11711.3      31      58.92      22.46      11652.4
28 Feb '11      28 Mar '11      11652.4      28      61.2       20.18      11591.2
28 Mar '11      28 Apr '11      11591.2      31      59.15      22.23      11532.1
28 Apr '11      30 May '11      11532.1      32      58.55      22.83      11473.5
30 May '11      28 Jun '11      11473.5      29      60.8       20.58      11412.7
28 Jun '11      28 Jul '11      11412.7      30      60.2       21.18      11352.5
28 Jul '11      29 Aug '11      11352.5      32      58.91      22.47      11293.6
29 Aug '11      28 Sep '11      11293.6      30      60.42      20.96      11233.2
28 Sep '11      28 Oct '11      11233.2      30      60.53      20.85      11172.6
28 Oct '11      28 Nov '11      11172.6      31      59.95      21.43      11112.7
28 Nov '11      28 Dec '11      11112.7      30      60.76      20.62      11051.9
28 Dec '11      30 Jan '12      11051.9      33      58.82      22.56      10993.1
30 Jan '12      28 Feb '12      10993.1      29      61.66      19.72      10931.5
28 Feb '12      28 Mar '12      10931.5      29      61.77      19.61      10869.7
28 Mar '12      30 Apr '12      10869.7      33      59.19      22.19      10810.5
30 Apr '12      28 May '12      10810.5      28      62.66      18.72      10747.8
28 May '12      28 Jun '12      10747.8      31      60.77      20.61      10687.1
28 Jun '12      30 Jul '12      10687.1      32      60.22      21.16      10626.8
30 Jul '12      28 Aug '12      10626.8      29      62.32      19.06      10564.5
28 Aug '12      28 Sep '12      10564.5      31      61.12      20.26      10503.4
28 Sep '12      29 Oct '12      10503.4      31      61.24      20.14      10442.2
29 Oct '12      28 Nov '12      10442.2      30      62.        19.38      10380.2
28 Nov '12      28 Dec '12      10380.2      30      62.12      19.26      10318.
28 Dec '12      28 Jan '13      10318.       31      61.59      19.79      10256.4
28 Jan '13      28 Feb '13      10256.4      31      61.71      19.67      10194.7
28 Feb '13      28 Mar '13      10194.7      28      63.72      17.66      10131.
28 Mar '13      29 Apr '13      10131.       32      61.33      20.05      10069.7
29 Apr '13      28 May '13      10069.7      29      63.32      18.06      10006.4
28 May '13      28 Jun '13      10006.4      31      62.19      19.19      9944.18
28 Jun '13      29 Jul '13      9944.18      31      62.31      19.07      9881.87
29 Jul '13      28 Aug '13      9881.87      30      63.04      18.34      9818.83
28 Aug '13      30 Sep '13      9818.83      33      61.34      20.04      9757.49
30 Sep '13      28 Oct '13      9757.49      28      64.48      16.9       9693.01
28 Oct '13      28 Nov '13      9693.01      31      62.79      18.59      9630.22
28 Nov '13      30 Dec '13      9630.22      32      62.32      19.06      9567.9
30 Dec '13      28 Jan '14      9567.9       29      64.22      17.16      9503.68
28 Jan '14      28 Feb '14      9503.68      31      63.15      18.23      9440.53
28 Feb '14      28 Mar '14      9440.53      28      65.03      16.35      9375.5
28 Mar '14      28 Apr '14      9375.5       31      63.4       17.98      9312.1
28 Apr '14      28 May '14      9312.1       30      64.1       17.28      9248.
28 May '14      30 Jun '14      9248.        33      62.5       18.88      9185.5
30 Jun '14      28 Jul '14      9185.5       28      65.47      15.91      9120.03
28 Jul '14      28 Aug '14      9120.03      31      63.89      17.49      9056.14
28 Aug '14      29 Sep '14      9056.14      32      63.45      17.93      8992.69
29 Sep '14      28 Oct '14      8992.69      29      65.25      16.13      8927.44
28 Oct '14      28 Nov '14      8927.44      31      64.26      17.12      8863.18
28 Nov '14      29 Dec '14      8863.18      31      64.38      17.        8798.8
29 Dec '14      28 Jan '15      8798.8       30      65.05      16.33      8733.75
28 Jan '15      02 Mar '15      8733.75      33      63.55      17.83      8670.2
02 Mar '15      30 Mar '15      8670.2       28      66.36      15.02      8603.84
30 Mar '15      28 Apr '15      8603.84      29      65.94      15.44      8537.9
28 Apr '15      28 May '15      8537.9       30      65.54      15.84      8472.36
28 May '15      29 Jun '15      8472.36      32      64.61      16.77      8407.75
29 Jun '15      28 Jul '15      8407.75      29      66.3       15.08      8341.45
28 Jul '15      28 Aug '15      8341.45      31      65.38      16.        8276.07
28 Aug '15      28 Sep '15      8276.07      31      65.51      15.87      8210.56
28 Sep '15      28 Oct '15      8210.56      30      66.14      15.24      8144.42
28 Oct '15      30 Nov '15      8144.42      33      64.75      16.63      8079.67
30 Nov '15      28 Dec '15      8079.67      28      67.39      13.99      8012.28
28 Dec '15      28 Jan '16      8012.28      31      66.01      15.37      7946.27
28 Jan '16      29 Feb '16      7946.27      32      65.65      15.73      7880.62
29 Feb '16      28 Mar '16      7880.62      28      67.73      13.65      7812.89
28 Mar '16      28 Apr '16      7812.89      31      66.4       14.98      7746.49
28 Apr '16      30 May '16      7746.49      32      66.05      15.33      7680.44
30 May '16      28 Jun '16      7680.44      29      67.6       13.78      7612.84
28 Jun '16      28 Jul '16      7612.84      30      67.25      14.13      7545.59
28 Jul '16      29 Aug '16      7545.59      32      66.44      14.94      7479.15
29 Aug '16      28 Sep '16      7479.15      30      67.5       13.88      7411.65
28 Sep '16      28 Oct '16      7411.65      30      67.63      13.75      7344.02
28 Oct '16      28 Nov '16      7344.02      31      67.3       14.08      7276.72
28 Nov '16      28 Dec '16      7276.72      30      67.88      13.5       7208.84
28 Dec '16      30 Jan '17      7208.84      33      66.66      14.72      7142.18
30 Jan '17      28 Feb '17      7142.18      29      68.57      12.81      7073.61
28 Feb '17      28 Mar '17      7073.61      28      69.13      12.25      7004.48
28 Mar '17      28 Apr '17      7004.48      31      67.95      13.43      6936.53
28 Apr '17      29 May '17      6936.53      31      68.08      13.3       6868.45
29 May '17      28 Jun '17      6868.45      30      68.63      12.75      6799.82
28 Jun '17      28 Jul '17      6799.82      30      68.76      12.62      6731.06
28 Jul '17      28 Aug '17      6731.06      31      68.47      12.91      6662.59
28 Aug '17      28 Sep '17      6662.59      31      68.6       12.78      6593.99
28 Sep '17      30 Oct '17      6593.99      32      68.33      13.05      6525.66
30 Oct '17      28 Nov '17      6525.66      29      69.67      11.71      6455.99
28 Nov '17      28 Dec '17      6455.99      30      69.4       11.98      6386.59
28 Dec '17      29 Jan '18      6386.59      32      68.74      12.64      6317.85
29 Jan '18      28 Feb '18      6317.85      30      69.66      11.72      6248.19
28 Feb '18      28 Mar '18      6248.19      28      70.56      10.82      6177.63
28 Mar '18      30 Apr '18      6177.63      33      68.77      12.61      6108.86
30 Apr '18      28 May '18      6108.86      28      70.8       10.58      6038.06
28 May '18      28 Jun '18      6038.06      31      69.8       11.58      5968.26
28 Jun '18      30 Jul '18      5968.26      32      69.57      11.81      5898.69
30 Jul '18      28 Aug '18      5898.69      29      70.8       10.58      5827.89
28 Aug '18      28 Sep '18      5827.89      31      70.2       11.18      5757.69
28 Sep '18      29 Oct '18      5757.69      31      70.34      11.04      5687.35
29 Oct '18      28 Nov '18      5687.35      30      70.83      10.55      5616.52
28 Nov '18      28 Dec '18      5616.52      30      70.96      10.42      5545.56
28 Dec '18      28 Jan '19      5545.56      31      70.75      10.63      5474.81
28 Jan '19      28 Feb '19      5474.81      31      70.88      10.5       5403.93
28 Feb '19      28 Mar '19      5403.93      28      72.02      9.36       5331.91
28 Mar '19      29 Apr '19      5331.91      32      70.83      10.55      5261.08
29 Apr '19      28 May '19      5261.08      29      71.94      9.44       5189.14
28 May '19      28 Jun '19      5189.14      31      71.43      9.95       5117.71
28 Jun '19      29 Jul '19      5117.71      31      71.57      9.81       5046.14
29 Jul '19      28 Aug '19      5046.14      30      72.02      9.36       4974.12
28 Aug '19      30 Sep '19      4974.12      33      71.23      10.15      4902.89
30 Sep '19      28 Oct '19      4902.89      28      72.89      8.49       4830.
28 Oct '19      28 Nov '19      4830.        31      72.12      9.26       4757.88
28 Nov '19      30 Dec '19      4757.88      32      71.96      9.42       4685.92
30 Dec '19      28 Jan '20      4685.92      29      72.97      8.41       4612.95
28 Jan '20      28 Feb '20      4612.95      31      72.53      8.85       4540.42
28 Feb '20      30 Mar '20      4540.42      31      72.67      8.71       4467.75
30 Mar '20      28 Apr '20      4467.75      29      73.37      8.01       4394.38
28 Apr '20      28 May '20      4394.38      30      73.22      8.16       4321.16
28 May '20      29 Jun '20      4321.16      32      72.83      8.55       4248.33
29 Jun '20      28 Jul '20      4248.33      29      73.76      7.62       4174.57
28 Jul '20      28 Aug '20      4174.57      31      73.37      8.01       4101.2
28 Aug '20      28 Sep '20      4101.2       31      73.52      7.86       4027.68
28 Sep '20      28 Oct '20      4027.68      30      73.91      7.47       3953.77
28 Oct '20      30 Nov '20      3953.77      33      73.31      8.07       3880.46
30 Nov '20      28 Dec '20      3880.46      28      74.66      6.72       3805.8
28 Dec '20      28 Jan '21      3805.8       31      74.08      7.3        3731.72
28 Jan '21      01 Mar '21      3731.72      32      73.99      7.39       3657.73
01 Mar '21      29 Mar '21      3657.73      28      75.04      6.34       3582.69
29 Mar '21      28 Apr '21      3582.69      30      74.73      6.65       3507.96
28 Apr '21      28 May '21      3507.96      30      74.87      6.51       3433.09
28 May '21      28 Jun '21      3433.09      31      74.8       6.58       3358.29
28 Jun '21      28 Jul '21      3358.29      30      75.15      6.23       3283.14
28 Jul '21      30 Aug '21      3283.14      33      74.68      6.7        3208.46
30 Aug '21      28 Sep '21      3208.46      29      75.62      5.76       3132.84
28 Sep '21      28 Oct '21      3132.84      30      75.57      5.81       3057.27
28 Oct '21      29 Nov '21      3057.27      32      75.33      6.05       2981.94
29 Nov '21      28 Dec '21      2981.94      29      76.03      5.35       2905.91
28 Dec '21      28 Jan '22      2905.91      31      75.81      5.57       2830.1
28 Jan '22      28 Feb '22      2830.1       31      75.95      5.43       2754.15
28 Feb '22      28 Mar '22      2754.15      28      76.61      4.77       2677.54
28 Mar '22      28 Apr '22      2677.54      31      76.25      5.13       2601.29
28 Apr '22      30 May '22      2601.29      32      76.23      5.15       2525.06
30 May '22      28 Jun '22      2525.06      29      76.85      4.53       2448.21
28 Jun '22      28 Jul '22      2448.21      30      76.84      4.54       2371.37
28 Jul '22      29 Aug '22      2371.37      32      76.69      4.69       2294.68
29 Aug '22      28 Sep '22      2294.68      30      77.12      4.26       2217.56
28 Sep '22      28 Oct '22      2217.56      30      77.26      4.12       2140.3
28 Oct '22      28 Nov '22      2140.3       31      77.28      4.1        2063.02
28 Nov '22      28 Dec '22      2063.02      30      77.55      3.83       1985.47
28 Dec '22      30 Jan '23      1985.47      33      77.33      4.05       1908.14
30 Jan '23      28 Feb '23      1908.14      29      77.96      3.42       1830.18
28 Feb '23      28 Mar '23      1830.18      28      78.21      3.17       1751.97
28 Mar '23      28 Apr '23      1751.97      31      78.02      3.36       1673.95
28 Apr '23      29 May '23      1673.95      31      78.17      3.21       1595.78
29 May '23      28 Jun '23      1595.78      30      78.42      2.96       1517.36
28 Jun '23      28 Jul '23      1517.36      30      78.56      2.82       1438.8
28 Jul '23      28 Aug '23      1438.8       31      78.62      2.76       1360.18
28 Aug '23      28 Sep '23      1360.18      31      78.77      2.61       1281.41
28 Sep '23      30 Oct '23      1281.41      32      78.84      2.54       1202.57
30 Oct '23      28 Nov '23      1202.57      29      79.22      2.16       1123.35
28 Nov '23      28 Dec '23      1123.35      30      79.3       2.08       1044.05
28 Dec '23      29 Jan '24      1044.05      32      79.31      2.07       964.74
29 Jan '24      28 Feb '24      964.74       30      79.59      1.79       885.15
28 Feb '24      28 Mar '24      885.15       29      79.79      1.59       805.36
28 Mar '24      29 Apr '24      805.36       32      79.79      1.59       725.57
29 Apr '24      28 May '24      725.57       29      80.08      1.3        645.49
28 May '24      28 Jun '24      645.49       31      80.14      1.24       565.35
28 Jun '24      29 Jul '24      565.35       31      80.3       1.08       485.05
29 Jul '24      28 Aug '24      485.05       30      80.48      0.9        404.57
28 Aug '24      30 Sep '24      404.57       33      80.55      0.83       324.02
30 Sep '24      28 Oct '24      324.02       28      80.82      0.56       243.2
28 Oct '24      28 Nov '24      243.2        31      80.91      0.47       162.29
28 Nov '24      30 Dec '24      162.29       32      81.06      0.32       81.23
30 Dec '24      28 Jan '25      81.23        29      81.23      0.15       0
``````

This still does not match the figures in the DirectLoan table, but it may cast some light on how they are not calculating their figures.

It seems very doubtful that DirectLoan is using the simple daily interest formula since a daily interest rate of 0.00585% is required to match DirectLoan's interest figures, but a rate of 0.00619% is required to amortise the loan in 174 months. The figures really don't match up: You cannot amortise the loan in 174 months with the interest rate implied in DirectLoan's table.

• Are you familiar with the simple daily interest calculation? Dec 25, 2016 at 21:47
• I read your link. If the daily interest rate is `i = 0.0225/365.25` and August '10 is the first repayment period the interest accrued, `\$23.31 = days * 12063.94 * i` therefore `days = 31.3661`. Since this isn't a round number perhaps August '10 is not the first period. What do you think? Dec 25, 2016 at 22:20
• You make an interesting point about discrepancies, but the chief question I need answered is how to calculate the days variable in GNUcash for future scheduled transactions -- my due date is on the 28th, which means a variable number of days between payments! Dec 26, 2016 at 23:32
• @jldugger Avoiding weekends produces a consistent daily rate, as shown in my edit. Dec 27, 2016 at 12:45