# How to reconcile loan statements when balance doesn't add up

I'm helping a friend enter her student loan payments into GnuCash and I've come across something I've never encountered before. Apparently Discover Student Loans are calculated with Daily Simple Interest and their statements reflect the balance on the statement date, which isn't necessarily related to the payment made. For example:

``````Statement Date  Payment  Principal  Interest  Statement Bal   |  GnuCash Bal
2018-07-16      \$200.00    \$154.60    \$45.40     \$13,381.12   |   \$13,381.12
2018-08-16      \$200.00    \$153.29    \$46.71     \$13,229.26   |   \$13,227.83
2018-09-15      \$200.00    \$150.75    \$49.25     \$13,075.35   |   \$13,077.08
Note: above interest rate = 4.25%, below rate = 4.5%
2018-10-16      \$200.00    \$153.45    \$46.55     \$12,923.84   |   \$12,923.63
2018-11-15      \$200.00    \$153.83    \$46.17     \$12,771.47   |   \$12,769.80
2018-12-16      \$200.00    \$149.66    \$50.34     \$12,620.13   |   \$12,620.14
``````

The first entry is the opening balance for the loan in GnuCash and equals the statement balance. After that, notice the GnuCash balance is the expected balance after reducing the previous balance by the principal amount. However, the statement is only showing the actual balance as of the statement date, which could be more or less than the amount based on the principal payment made.

Note: Some of the comments starting going down the path of "Why is this happening?" I called Discover and asked them and I was told this is a common question and that the statement balance can be off by a few dollars. The CS rep didn't know of a solution but suggested I look at the transactions online to see if there is more information. I did that but unfortunately the payment history doesn't display the balance at that time either. I've accepted that the statement balance is off and that's just the way it is.

Besides this being annoying, I'm not sure how to reconcile any statement properly. Any suggestions on how to reconcile, preferably cleanly?

Answer Summary: even though the statement balance is what's "off", the accepted answer suggests treating the statement balance as correct and adjusting the interest to match. This may be the best approach for sanity purposes if you are reconciling. The other good answer accomplishes the same end result all in a single transaction by adjusting both the principal and interest values to what they should have been according to the statement balance.

• Is she on an income-based repayment plan? I can't see why the principal payment would be going down and the interest amount going up each month. Commented Jan 5, 2019 at 21:04
• @mkennedy No, it's just a normal term loan. She's overpaying each month by approx \$50. I assume the interest accrued each month varies depending on the number of days in the month, but even then that doesn't fully explain the variance. I'll add some more data.
– TTT
Commented Jan 5, 2019 at 21:15
• Oh. Look at other student loans questions. They may be prepaying the future payments rather than applying the overpaments to principle. Commented Jan 5, 2019 at 21:18
• Isn't this just a case of the interest varying depending on what day of the month the payment is made? For example the December interest could be higher because maybe there were 33 days since the last payment and in October there were only 28. Either way I don't see how we could help without each of the statements. Commented Jan 5, 2019 at 22:26
• I understand, but you have to figure out what's happening before you can reconcile the two. Commented Jan 5, 2019 at 22:30

There are two moving parts, month-to-month:

• interest on the loan: increases the balance; and
• payment: decreases the balance.

Consider entering these separately.

If you set up the loan account via Gnucash's Educational Loan account hierarchy, you have 2 accounts:

• an Education Loan Interest account (Expenses) - let's call this "Interest Accrued"; and
• an Education Loan account (Liabilities) - let's call this "Student Loan".

Presumably you also have a bank account (Assets) where the loan repayments are drawn from.

• Based on the statement balance, calculate the actual interest accrued and record it in the "Student Loan" account. (Note in GnuCash you can use arithmetic operations directly in the amount field.) If desired, you could also record the stated interest amount in the description field. Date it according to the statement. The "Transfer" entry should be the "Interest Accrued" account. Put the new interest into the "Increase" column. The "Interest Accrued" account will automatically be increased by the corresponding amount.

• Record repayment in the bank (Assets) account. Date it according to when the payment was made. The "Transfer" entry should be the "Student Loan" account. Record the amount paid as a "Withdrawal". The "Student Loan" account will automatically be decreased by the corresponding amount.

At this point, your "Student Loan" account should show the balance indicated in the statement; your "Interest Accrued" account should show the total interest accrued on that loan; and your bank account should be decreased by the amount of the payment made.

Disclaimer: I am not an accountant. The above is just a structure that makes sense to me.

• This makes sense, and is how some line of credits are done. Each month the entire payment reduces principal, and interest is added. The only minor snag is in this case the statement doesn't actually show the accrued interest; only the interest portion of the payment, which isn't necessarily the same as the interest accrued as of the statement date. So to do what you suggest, I could take the statement balance, add the payment amount, and subtract the previous statement balance to know the amount of interest to add back in. Sort of cheating but it definitely works.
– TTT
Commented Jan 6, 2019 at 17:53
• @TTT Mea culpa - I should have punched in some numbers. Yes, reverse-engineer the accrued interest so that the Gnucash balance mirrors the statement balance. The interest component of the payment seems to be operationally irrelevant; you can keep a record of it the "Description" field if you like. By the way, when I fired up Gnucash today, the Tip of the Day was that you can do arithmetic operations in the amounts field :). That makes it slightly more convenient. For example, in the "Increase" column of the Student Loan entry, typing something like `13229.26-13381.21+200` produces `48.05`. Commented Jan 7, 2019 at 0:19
• I ended up using this approach. Great tips too. I incorporated them into your answer.
– TTT
Commented Jan 9, 2019 at 16:33
• @TTT Thanks! Good to know that the tips were useful. :) Commented Jan 9, 2019 at 16:35

If we accept the `Statement Bal` as true, then we can reverse engineer the amount of principal the `Statement Bal` implies must have been paid. For example (using Python), given this data:

``````In [94]: df = pd.read_csv('data', sep='\s{2,}'); df
Out[94]:
Statement Date  Payment  Statement Bal  GnuCash Bal
0     2018-07-16    200.0       13381.12     13381.12
1     2018-08-16    200.0       13229.26     13227.83
2     2018-09-15    200.0       13075.35     13077.08
3     2018-10-16    200.0       12923.84     12923.63
4     2018-11-15    200.0       12771.47     12769.80
5     2018-12-16    200.0       12620.13     12620.14
``````

The `Implied Principal` (below) is the difference between successive values of `Statement Bal`. For example, 151.86 in the `Implied Principal` column equals 13381.12 - 13229.26, and 13229.26 - 13075.35 equals 153.91, and so on.

``````In [97]: df['Implied Principal'] = -df['Statement Bal'].diff(); df
Out[97]:
Statement Date  Payment  Implied Principal  Statement Bal  GnuCash Bal
0     2018-07-16    200.0                NaN       13381.12     13381.12
1     2018-08-16    200.0             151.86       13229.26     13227.83
2     2018-09-15    200.0             153.91       13075.35     13077.08
3     2018-10-16    200.0             151.51       12923.84     12923.63
4     2018-11-15    200.0             152.37       12771.47     12769.80
5     2018-12-16    200.0             151.34       12620.13     12620.14
``````

Then the `Implied Interest` would be the difference between the `Payment` and the `Implied Principal`:

``````In [99]: df['Implied Interest'] = df['Payment']-df['Implied Principal']; df
Out[99]:
Statement Date  Payment  Implied Principal  Implied Interest Statement Bal  GnuCash Bal
0     2018-07-16    200.0                NaN               NaN      13381.12     13381.12
1     2018-08-16    200.0             151.86             48.14      13229.26     13227.83
2     2018-09-15    200.0             153.91             46.09      13075.35     13077.08
3     2018-10-16    200.0             151.51             48.49      12923.84     12923.63
4     2018-11-15    200.0             152.37             47.63      12771.47     12769.80
5     2018-12-16    200.0             151.34             48.66      12620.13     12620.14
``````

Now if we were to redefine `Principal` and `Interest` using their implied counterparts, then -- because of the way `Implied Principal` and ```Implied Interest``` were constructed -- `GnuCash Bal` would equal `Statement Bal`.

• The main issue I have with this is I don't want to actually change anything in the statement, since the statement is (probably) correct. (It's just providing values that don't match up with each other.)
– TTT
Commented Jan 6, 2019 at 16:03
• It seems to me the `Principal`, `Interest` and `Statement Bal` columns can not all 3 be correct if we take them to have their usual meanings. To accept the `Statement Bal` (to me) implies we must reject the `Principal` and `Interest` columns as somehow not applicable in the same context as `Statement Bal`. To reconcile all 3 would require digging into how these values were calculated. A call to Discover's customer service might be enlightening. Commented Jan 6, 2019 at 16:21
• Presumably all the numbers on the statement are correct but the timing is off. Principal and interest shown is at the time of the payment, but statement balance is at the time of the statement (maybe more interest has accrued since the payment was made). Your idea is a slightly more complicated variant of the other answer which proposes to reduce the principal by the payment amount and only adjust the interest based on the statement balance.
– TTT
Commented Jan 6, 2019 at 18:06
• After thinking about this more, I now realize that your answer is basically the same as the accepted answer, except that you accomplish it in a single transaction line instead of two. (Which I do like.) The only advantage the other has over this is that only one value is being explicitly adjusted from the statement instead of two. But the result is in fact the same. Thx.
– TTT
Commented Jan 10, 2019 at 18:55

It appears that you do not have the actual interest transactions listed in the statement.

I believe the column labelled as "Interest" is the amount of the repayment apportioned to pay off interest, which will increase over time as the principal decreases. (The column labeled Principal is the amount of the repayment apportioned to pay down the Principal.)

As the actual interest charges seem to be missing from the above data, this would explain why the balances don't match what you are calculating.

• Is there any additional data in the statement?
• Are you able to calculate the interest charged yourself (based on the annual rate / 365 * number of days in the month)?
• Or reverse out the interest calculation based on portion of interest paid + difference between statement balance and calculated balance.

The most probable answer is that it is not being calculated as a simple interest loan. Without reverse engineering the line items, I would have to guess what is being used. The two most probable culprits are that the loans are being treated as a scheduled balance loan, so the date you pay doesn't actually matter though principal reductions do. The second possible reason is that they have 30 day months. There is also the possibility that the loan system drops a "bill" and that all payments are spread by the billing drop and do not depend on the actual days for payment but do depend on it for accrual. In that case, accrued, but unpaid interest, or excess collected interest, will be adjusted on the next bill.