: 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

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

---

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.

---

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 = 063.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]

adjusteddates = adjust /@ nonshifteddates;

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

{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.

---