Computing interest payments with accuracy and precision

General
1

At https://www.reddit.com/r/plaintextaccounting/comments/1i5as0z/computing_interest_payments_with_accuracy_and czerny2018 asked:

Has anyone been able to compute monthly interest payments with accuracy/precision that reconciles with a banking institution to the nearest penny? How do we calibrate our computations with banking institutions? My attempts at controlling for significant figures has failed to reconcile, as has uncalibrated floating point computations.

Loan Agreement

  • 6 year loan with monthly payments
  • Yearly interest rate of loan: 7.99%
  • Principal Balance at start of loan (on 2024-09-20): $32525.55

Payment history

Payment history was quoted as follows:

Date Principal Interest Ending Balance
2024-09-20 0.00 0.00 32525.55
2024-10-23 335.81 234.31 32189.74
2024-11-15 408.49 161.63 31781.25
2024-12-23 306.48 263.64 31474.77
2025-01-15 411.82 158.30 31062.95

I'm replying here for convenience, etc:

Thanks for writing up the details.

I think significant figures here means decimal digits ?
FWIW when troubleshooting reconciling disagreements, I usually try first calculating with all digits possible, because rounding just adds another source of complexity and disagreement.

The python calculations are too much for me to comment on, but hledger-interest is a relevant tool here, so I thought I'd give it a go.
I modelled your loan balances and payments from the Payment History table like so:

2024-09-20 loan
    liabilities:loan                        $ -32525.55 = -$ 32525.55
    assets:checking                          $ 32525.55

2024-10-23 loan payment
    assets:checking
    liabilities:loan                           $ 335.81  = $ -32189.74

2024-11-15 loan payment
    assets:checking
    liabilities:loan                           $ 408.49  = $ -31781.25

2024-12-23 loan payment
    assets:checking
    liabilities:loan                           $ 306.48  = $ -31474.77

2025-01-15 loan payment
    assets:checking
    liabilities:loan                           $ 411.82  = $ -31062.95

So far so good:

$ hledger -f a.j reg liabilities:loan
2024-09-20 loan                 liabilities:loan       $ -32525.55   $ -32525.55
2024-10-23 loan payment         liabilities:loan          $ 335.81   $ -32189.74
2024-11-15 loan payment         liabilities:loan          $ 408.49   $ -31781.25
2024-12-23 loan payment         liabilities:loan          $ 306.48   $ -31474.77
2025-01-15 loan payment         liabilities:loan          $ 411.82   $ -31062.95

Then I generated the bank's interest charges like so:

$ hledger-interest -f a.j liabilities:loan -s expenses:interest -t liabilities:loan:interest -q --annual 0.0799 --act
2024-10-23 7.99% interest for $ -32525.55 over 33 days
    liabilities:loan:interest       $ -234.32
    expenses:interest                $ 234.32

2024-11-15 7.99% interest for $ -32189.74 over 23 days
    liabilities:loan:interest       $ -161.63
    expenses:interest                $ 161.63

2024-12-23 7.99% interest for $ -31781.25 over 38 days
    liabilities:loan:interest       $ -263.65
    expenses:interest                $ 263.65

2024-12-31 7.99% interest for $ -31474.77 over 8 days
    liabilities:loan:interest        $ -54.97
    expenses:interest                 $ 54.97

2025-01-15 7.99% interest for $ -31474.77 over 15 days
    liabilities:loan:interest       $ -103.35
    expenses:interest                $ 103.35

These amounts agree with the interest in your Payment history table, except for

  • Some inconsistent rounding there (which doesn't seem to accumulate so isn't much of a problem). hledger's rounding is consistent (verified by running the report with more decimal places). In your journal, you'd probably want to record the bank's amount of course, for historical accuracy.

  • The 1/15 $158.30 interest payment is split in two parts above, because a year-end interest charge seems required (either by the ACT day count convention or by hledger-interest, I'm not sure which).

Note above I

  1. directed the payments entirely to the principal, and
  2. directed the interest to a separate subaccount, which means that hledger-interest does not compound it (does not calculate interest on the interest).

Happily these seem to be correct assumptions, since they match your data. (Surprisingly! I'm not sure I've seen a lender who pays off the principal first and doesn't compound interest.)

Related:

2 Likes
2

Interesting. My understanding was that the total payment would be equal to principal payment + interest. However, the interest payment is an expense that draws from the checking account, and does not increase the amount owed. Should not the liabilities:loan account simply reflect the output shown from hledger -f a.j reg liabilities:loan? Those were all principal payments.

Also, the hledger-interest tool seems to work well for computing interest based on various intervals. However I have been modeling these payments with one transaction. For example, assume a $600.00 interest payment is owed for the following car payment. The bill total is $1000.00:

2025-01-21 Car Payment
    account:checking            $-1000.00
    liabilities:loan              $400.00    ; Principal payment
    expenses:interest             $600.00    ; Interest payment

Is this not a requirement per the double entry method?

3

Is this not a requirement per the double entry method?

I dont this that this is a requirement in any way.

However I have been modeling these payments with one transaction.

Which works well when you know the breakdown berween interest and and principal payment. However, hledger-interest covers a use-case when you dont, which in my personal experience is actually the more common situation.

For instance, my mortgage provider might be giving me only an annual statements with total amount of interest and principal paid (usually at the "anniversary" of the mortgage agreement, which does not line up with either start of calendar year or start of fiscal year), and at the same time my current account transactions include monthly mortgage payment (and they are not, of course, split between principal and interest).

In this situation, i can (automatically, while importing the bank csv) direct the current account payments to liabilities:mortgage, and then later on us e hledger-interest to (automatically) back out the interest part of those payments into expenses, exactly as Simon has shown previously.

4

Thank you, I can understand how you would arrive at the correct balance for the liability at the end of the year. However, if you were thinking about selling the house, etc., you might want the breakdown within each monthly transaction, so that you knew how much was payed down.

5

Yes, and this is exactly what hledger-interest could be used for, by computing the amont of interest for all of my monthly repayment transactions.

I mentioned annual summary from mortgage provider simply to mention that for me the only external source that gives me breakdown between interest and principal payments is in fact rather useless, and hledger-interest saves the day, working it out from my bank payments, as Simon has shown

6

@csgagnon I didn't reply to this because I haven't fully understood it. I will say it's still possible that I have misunderstood or mistated how exactly your real world loan is working, so adjust my remarks to match reality if needed. But since the numbers match, I feel the day count convention is correct and the general approach is a valid one.

7

The ACT method gives results agreeing with the bank to a penny.

It is important to remember 2024 was a leap year with 366 days, not 365. The bank office czerny spoke to gave a pretty good description of the calculation except for saying "365" instead of "number of days in the year". Oops.