Setting a car loan

Edward Doolittle edward.doolittle at gmail.com
Sat Feb 21 14:50:08 EST 2015


I had a similar issue last year. I requested a Statement of Account for my
car loan from my bank and it was all over the place. Very disturbing to my
mathematical mind. So I sat down to try to understand it. What I found was:

- My bank continually shifts the actual payment date ahead from the nominal
date by a few days so that the payment date does not fall on weekends or
holidays.
- My bank counts the number of days between payments, divides by 365,
multiplies by the APR, and multiplies by the outstanding balance to
calculate the interest.
- My bank rounds that number, adds to the outstanding balance, and
subtracts my payment to determine the new balance each period.
- Even when I mirrored those calculations, my calculations differed from
those of the bank by plus or minus one cent ($0.01) about four times per
year. I can't explain the discrepancy yet, so I add a correction (of -0.01,
0.00, or 0.01) to each loan calculation which I used to compensate for the
discrepancy.

Mathematical formulas based on geometric series are nice, but you should
keep in mind that they are only an approximation to what the bank does. If
you're going to try to mirror what the bank does, for accurate bookkeeping,
you'll really have to use a spreadsheet instead.

It isn't hard to set up a spreadsheet with the basic features. I did one
for Andy in a few minutes here:
https://docs.google.com/spreadsheets/d/1LWOGrwNpHNa6Qf68J6FDAc5KchXHjV-G4Dnp64tKRaw/edit?usp=sharing
 . The first row of the spreadsheet is irregular, so I had to enter the
second row also by hand, then just copied that row and pasted up to row 61.
Note that I did not include a Date Correction column to shift the date
ahead to avoid weekends or holidays. It's not that hard to figure out a
formula to place in the Date Correction column to move the date ahead to
avoid weekends; I could share my formula for that if anyone is interested.
It is harder to avoid holidays; if you really need to do that I suggest a
manual correction to the Date Correction column to offset the date; maybe
it could be automated using a table lookup. I didn't put any date
correction columns into Andy's spreadsheet because I don't know whether
they're relevant.

When I got the spreadsheet for my car loan more or less correct with date
corrections and 1 cent rounding(?) corrections, I entered the data into
GnuCash as transactions crediting Liabilities:Loans:Car and debiting
Expenses:Interest:Car. The next time I get a statement of account, I will
reconcile it against Liabilities:Loans:Car and will likely have to make a
few more 1 or 2 cent corrections. (I can make them on my spreadsheet too,
then update my future predictions in GnuCash, but that's not really
necessary and means I would have to modify a whole bunch of predictions I
had entered into GnuCash each time I received an updated statement.)

Anyway, back to Andy's question. Something does indeed seem to be wrong. 61
payments is too much. 60 payments is probably exactly right. However, there
may be some explanation. I think that the bank may round down when it
calculates the monthly payment; in that case there will be a small payment
#61 that is less than the normal payment. (My spreadsheet shows $0.20
balance after payment 60 is made.) The bank may tell you 61 payments
because A) the last is technically a payment no matter how small, and B)
they want you to make sure you have funds to cover the last partial
payment.

Saying 61 payments could conceivably be just a way for them to avoid the
error-prone calculation that I attempted to do in my spreadsheet. It's
really hard to predict holidays by formula, particularly Easter, so the
bank just covers its donkey by warning you of an extra payment that may or
may not be significant.

The ultimate arbiter of the loan is the bank's Statement of Account. You
should get in touch with your bank/loan officer and get periodic statements
sent to you. Every month is probably not necessary, but an initial
statement and then at least once at the beginning of the year would be
helpful.


On 21 February 2015 at 12:28, Andy Pastuszak <apastuszak at gmail.com> wrote:

> Thank you.  That does help.
>
>
>
> On 02/21/2015 01:00 PM, jcard21 xxxxxxx wrote:
>
>> On Sat, Feb 21, 2015 at 12:08 PM, Andy Pastuszak <apastuszak at gmail.com>
>> wrote:
>>
>>> • Loan Principle Value $16,261.32
>>> • Loan Duration (number of years) 5 years (61 payments)
>>> • Loan Interest Rate 3.89%
>>> • Your Payment Frequency (monthly?) monthly
>>> • Bank's Required Monthly Payment Amount $299.11
>>>
>>> Car was purchased on 12/22/2014.  First payment was due on 2/5/2015.
>>>
>> Using your figures ($16,261.32 principle, 61 months, 3.89%) , I get a
>> monthly payment of $294.23 (using my Texas Instruments BA-II financial
>> calculator.)
>>
>> Using 60 months, I get a monthly payment of $298.67, a lot closer to
>> the dealer's amount.
>>
>> Could you and the dealer have included any other fees (motor vehicle
>> registration, under-body weatherproofing, etc) totaling $269.46 on top
>> of your loan principle of $16,261.32, so your actual/real loan
>> principle would be the $16,530.78 ?
>>
>> A Loan Principle of $16,530.78 would give you a monthly payment of
>> $299.11?
>>
>> I'd go back to the dealer and politely ask them to explain why your
>> calculated monthly payment  doesn't equal their monthly payment of
>> $299.11.
>>
>> I'd start by verifying with the dealer the number of months/payments
>> (60 or 61), and I'd verify the Loan Principle ($16,261.32 or
>> 16,530.78).
>>
>> I hope this helps.
>>
>>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>



-- 
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI


More information about the gnucash-user mailing list