monthly payment discrepancy

Question

8/28/2015 original post -
I recently took out a loan as follows:

• loan amount: $116,000
• interest rate: 4.75%
• term: 360 months

When I calculate the monthly payment it comes out to $605.11/month. This is the answer whether calculating it in Excel using the PMT function, on a Texas Instruments BA II Plus calculator, or by hand plugging all the variables into an equation to calculate the payment. The problem is that the lender shows this loan has a monthly payment of $605.19. The discrepancy may be related to the interest calculation being 30/360 which is specified in the loan document. In the calculations I did, 0.0475/12 was used. But 0.0475/12 is equivalent to 0.0475 * 1/12 which is also equivalent to 0.0475 * 30/360. So, is the lender's monthly payment too high by $0.08 or am I not doing something correctly when calculating the payment?

Note that the equation being used to calculate the payment by hand is as follows:

payment = (interest/12 * loan amount) / (1 - (1 + interest/12)^-term)

---

11/22/2015 update -
Additional facts pertinent to this problem:

• this is a 5 year balloon mortgage
• funding date was 8/31/2015
• payment due dates are the first day of each month
• day count convention is 30/360

Further figuring -
This loan is for 5 years plus one day. This is because I opted to make loan payments on the 1st of each month instead of the anniversary of the funding date (31st) every month.

Interest due for the additional day the money is borrowed (the 8/31/15 date): $116,000 x 0.0475 ÷ 360 = $15.30555... rounded to dollars and cents: $15.31.
This seems to be the origin of the extra $15+ bucks that Spehro Pefhany identified because when $116,015.31 (from $116,000.00 + $15.31) is plugged into Excel's PMT formula like so:
=-PMT(0.0475/360*30,360,116015.31,0,0) the monthly payment answer exactly matches what's on the loan documents: $605.19.
Because the loan is amortized over 360 months, this math implies that the $15.31 is paid back over 30 years and interest is charged on the $15.31 over that time because it was added to the principal amount when the loan began.

I think the bank's software is incorrectly handling this math; however, the error is in favor of the borrower. The loan statements show that the extra $0.08/payment is being applying to principal rather than interest. Not only does this make the principal of $116,000 decrease slightly faster but it saves the borrower some interest money over the long run because the balance is decreasing at a faster rate. As far as I can tell, the bank does not recover any of the $15.31.

I did notify the banker of these findings as they should be collecting more from the borrower in such a situation ($15.31 in the example above). This example is a small loan but on large commercial loans the bank could be out a significant amount of money.

Answer 1

Your calculations are correct.

It is likely that the bank's software has a rounding error. In effect, either your bank is overstating your interest by eight cents per month, or your bank is insisting that you prepay your principal by eight cents per month. If the bank's ongoing interest calculations are correct, your final payment will be slightly smaller (because of the prepaid principal, and because of compound interest on those prepayments).

I have performed similar calculations for my mortgages over the years, and except upon early payoff in the middle of the month, I have always matched my banks' calculations to the penny. Ironically, this means that my banks' formulas are a bit weird:

• Instead of charging a daily interest rate on the mortgage,
• The banks are charging a monthly interest rate on the mortgage, and pro-rating the interest for the first 30 days of the month.
• The interest for the 31st day of the month seems to be free.
• I don't know how they handle a payoff during February, but
• The total interest for the month of February is the same percentage as the total interest for the month of July.

After making these adjustments, even my calculations for the mid-month payoff matched to the penny.

Answer 2

I know this is an old thread, but it still shows up high in google when searching for this answer as I was earlier today. I have a theory as to why these calculators never seem to quite match (at least for my loans)

as @keshlam suggests, the formula the bank uses is slightly more complicated, the biggest factor I can see is that on every home loan I've ever received, the bank never actually collected a payment in the first month. This of course means that on a 20 year note, if I paid what the Excel amortization template suggested, it would take longer than 20 years to pay off unless I were to pay a little more than what the 20 year amortization recommends. Here's what I did to make my Excel amortization calculator (almost) match the banks payment schedule and balances:

• downloaded an excel amortization template from the internet. There are a lot of them out there, all very similar and most will let you do what I'm about to do
• I punched in all the numbers like I normally would - on this loan I see that there is a $2.60 discrepancy between what Excel says my monthly payment should be and what the bank is actually charging.
• I deleted the first payment where it lists out all the payments that excel things you're going to pay - remember the bank never actually collects that one.
• I specified $2.60 as my "extra" payment on every month for the entirety of the loan (except the first month) so now my monthly payments in excel match what the bank is collecting
• It now shows my loan as having 241 payments. The first payment is actually zero dollars and the 241st is a smaller payment.

I'm about 2 years into the loan - and the balance Excel shows I should have right now and the balance the bank shows is off by $200 (bank shows lower balance). I am guessing this is because the bank started my loan in the middle of a month (the 16th actually) and only charged a portion of the interest on that month where Excel will treat all months as a full month. If I mess with the amount of interest in the first month (just changed the number by hand to be significantly less), I can get the calculated balance for July 2019 to match the actual bank balance AND for that 241st payment to go away AND for the 240th payment to be a partial payment which I suspect might be accurate for a loan that ends mid-month.

In short, all of the amortization calculators I've seen will fail to match the bank primarily due to the "weirdness" of starting a loan mid month and the bank not collecting a payment for the first full month.

For those that care - if my math is correct, while it was nice to not have that first month's loan payment, it will effectively net my bank another $400 (out of my pocket) over the life of my almost $70k loan since the first month's interest was simply added to my principal. Depending on how much your loan is for, this number could vary drastically.