Why is every mortgage amortization calculator wrong?



  • I have a 15 year mortgage through CitiMortgage at 5.625% interest with a starting balance of $200,000.  I recently noticed that the principal balance CitiMortgage says I still owe is higher than the amount Microsoft Money says.  When my mortgage payments get recorded in Money it automatically calculates how much goes to principal and how much to interest.  For some reason every other month or so CitiMortgage takes an extra penny interest.  I thought I'd see who was right by finding a mortgage amortization calculator on the Internet.  I found several, but for some reason all of them are obviously wrong in the same places.  CitiMortgage and Microsoft Money both consistently follow these four simple rules:

    1.  The current principal balance is equal to the previous principal balance minus the amount of the current payment applied to principal.

    2.  The total interest paid to date is equal to the total interest paid to date from the previous payment plus the amount of the current payment applied to interest.

    3.  The amount of the current payment applied to interest plus the amount of the current payment applied to principal always equals the total payment amount.

    4.  The total payment amount never changes.

    These four rules seem pretty basic and you'd think any amortization calculator would be able to follow them, but for some reason every one I have tried breaks rules 1 and 2 and either 3 or 4, and always in the same places.

    They all seem to agree that after the 2nd payment the principal balance is $198,576.75, but they all say that $716.63 of the third payment goes to principal leaving a principal balance of $197,860.11 (breaking rule 1).

    They all seem to agree that after the fifth payment the total interest paid is $4,654.06, but they all say that $920.70 of the sixth payment goes to interest giving a total interest paid to date of $5,574.77 (breaking rule 2).

    They all seems to agree that $730.17 of the seventh payment is principal and $917.30 is interest, though most of them claim each payment is for a total of $1,647.46 (breaking rule 3) though I did find one that for some reason breakes rule 4 instead by changing the total payment amount for the seventh payment to $1,647.47 (payments 1-6 were for $1,647.46).



  • Rounding error... they probably use a formula such as Round(Sum(Principle for each week)*rate) instead of Sum(Round(Principle for each week))



  • They could use a 360 day year instead of 365.

    When I got my mortgage, my amounts were slightly off from what Excel had calculated using Interest = (Timespan/365) * Interest Rate * Principal. No-one at the bank could give me a satisfactory answer.

    All they really know is their magic program. Even basic math is pretty much voodoo to them. Showing an excel printout was like watching the monkey scene from 2001.



  •  I have a 25 year fixed-rate mortgage as well, it's been 3 1/2 years now, and my last statement showed remaining amortization of 17 years and 24 weeks.  I never paid attention before, but last year's statement for the same date showed 18 years and 24 weeks as well. 

     I calculated it myself using the current (fixed) interest rate and came to 17 years and 39 weeks, somewhat more than the bank's figure.  Either way, it's several years short of the target (I should have roughly 21 and a half years remaining).  I called and asked my bank about this last week, and they couldn't figure it out; they told me they'd call me back... that was a week ago.  As far as I can tell, my weekly payments weren't set up properly and are somewhat higher than they should have been. 

     I tried a variety of things to get 17 years and 39 weeks down to the bank's 17 years and 24 weeks but couldn't- I have no idea how they came to that figure either.

     The fortunate side : I can make payments up to twice the regular amount without any sort of 'overpayment penalty' , so the fact that I'm ahead won't cause me problems in the long run.

     

     



  • They write "never use doubles for money" all over the internet, but it seems web developers still don't get it. Or don't bother to force JavaScript to use integers (I suppose those online calculators are actually implemented in JavaScript—the calculation is easy enough, so why bother server—and it does not distinguish between doubles and integers, so one has to force desired rounding in each step).



  • It would appear that you have an ... [puts on sunglasses] ... interesting problem.



  • @Anonymouse said:

    It would appear that you have an ... [puts on sunglasses] ... interesting problem.

     

    You mean it's a problem of small interest.

     



  • @dhromed said:

    You mean it's a problem of small interest.
    So much so, that it's almost teresting.



  • Rule 1 might be wrong: depending on when you pay, you might have to subtract the previous payment. That shifts everything a little bit, including rule 2, since it changes over which amount you pay interest.

    And I guess the extra penny is because most programs calculate your payment in cents, but don't take into account that there is something behind the second digit, which can easily add up to one penny per two months.



  • @alapalme said:


     The fortunate side : I can make payments up to twice the regular amount without any sort of 'overpayment penalty' , so the fact that I'm ahead won't cause me problems in the long run.

     

     

    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

     


  • ♿ (Parody)

    @TwoScoopsOfHot said:

    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

    You're obviously not very familiar with US mortgages. It's common to have an early payment fee as part of the contract. After all, they make money off of the interest that you pay, and so paying down principle faster reduces the amount that they make.



  • @boomzilla said:

    @TwoScoopsOfHot said:
    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

    You're obviously not very familiar with US mortgages. It's common to have an early payment fee as part of the contract. After all, they make money off of the interest that you pay, and so paying down principle faster reduces the amount that they make.

     

     

    we have the same in uk



  • Two thoughts...

     

    1) Perhaps they are using "banker's rounding," rather than the "away from zero" algorithm we all learned in school:

    http://blogs.msdn.com/b/ericlippert/archive/2003/09/26/bankers-rounding.aspx

     

    2) Perhaps Citi is intentionally skimming a cent here and there as a secret profit center.  Sure, it's only going to earn them a dollar or so over the lifetime of your loan, but if they're doing the same thing with ten million other mortgages at the same time...

     



  • @boomzilla said:

    @TwoScoopsOfHot said:
    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

    You're obviously not very familiar with US mortgages. It's common to have an early payment fee as part of the contract. After all, they make money off of the interest that you pay, and so paying down principle faster reduces the amount that they make.

     

    I'm not positive about this, but I believe that varies by state.  When I bought my car I was told that early payments fees are illegal in New York, so I paid it off straight away.  Stories I've heard from homeowners suggest that it's also illegal here for mortgages.



  • @Justice said:

    I'm not positive about this, but I believe that varies by state.

    Look. Every State has its own government. EVERYTHING varies by State. When you're posting stuff like this to the Internet, please say: "in New York, X" not "In the US, X" because if you say the latter you are almost certainly wrong. And the fact that people who have lived in the US all their lives don't get this gives me a sad.

    Plus it confuses the Euro-trash, who seem to be under the impression that the US is governed by the Federal Government. Which is wrong. The Feds have a ton of control, and about 500% more than they did a century ago, but the States still control the vast majority of the things you deal with on a day-to-day basis like, say, laws dealing with mortgage payments.

    For example, DUI checkpoints are illegal here in Washington State, because our Government actually respects the Constitution more than the Supreme Court. So even though the Supreme Court (wrongly) ruled DUI checkpoints legal, we still don't have to deal with the problem because our State independently made the right choice on the matter. So if you went on the Internet and started spouting, "DUI checkpoints are legal in the US!" you'd be... well if not completely wrong, at best you'd be giving an extremely confusing interpretation of reality. The real answer to, "are DUI checkpoints legal?", as with almost all questions about US law, is, "what State are you in?"

    And for God's sake learn how your own fucking Government works.



  •  I'd suggest their interest totals can be explained simply by rounding errors.  If you use 5.61185% as the rate, you get almost the exact numbers you posted.  They're probably tracking the *true* outstanding balance with no rounding, and reporting principle and interest rounded to the nearest cent.  I am a little confused about the seventh payment, though... I've never seen the P+I change on my mortgage.



  • @blakeyrat said:

    DUI checkpoints are illegal here in Washington State
    Because not being allowed to run over schoolchildren in a drunken stupor violates your constitutional rights?



  • @Anonymouse said:

    @blakeyrat said:

    DUI checkpoints are illegal here in Washington State
    Because not being allowed to run over schoolchildren in a drunken stupor violates your constitutional rights?

    I would guess it's because of that whole... no search and seizure without a warrant... thing.  I'm pretty sure running over school children (con or sans drunken stupor) will get you more than stopped at a checkpoint.

  • ♿ (Parody)

    @Anonymouse said:

    @blakeyrat said:
    DUI checkpoints are illegal here in Washington State

    Because not being allowed to run over schoolchildren in a drunken stupor violates your constitutional rights?

    But why then are you advocating getting rid of school crosswalks?



  • Having developed (for clients) a number financial systems, I can tell you that it is not uncommon to use additional digits of precision in internal calculations, and then apply rounding on the outputs only. If one looks only at a sequential set of outputs [e.g. montly statements] it is quite possible to see "penny errors" because the internal number has crossed a 1 cent boundary.


  • Considered Harmful

    @TheCPUWizard said:

    Having developed (for clients) a number financial systems, I can tell you that it is not uncommon to use additional digits of precision in internal calculations, and then apply rounding on the outputs only. If one looks only at a sequential set of outputs [e.g. montly statements] it is quite possible to see "penny errors" because the internal number has crossed a 1 cent boundary.

    Isn't this incorrect, though? You don't actually make sub-penny payments. Monetary values are discrete, not continuous. If the figures disagree, it's the higher precision number that's in error, the lower precision number is representative of the actual value paid.



  • Not necessarily.  You don't make a sub-penny payment, but there's no reason the remaining principal can't have a sub-penny value.  I do wonder if the amortization works out just so, or if banks are having to charge extra fractional cents for your last payment to get a round number.



  • @TwoScoopsOfHot said:

    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast." 

     

    I'm in Canada, and yes, there is an annual limit on how much I can pay back, which afaik is twice the total sum of regular payments in a year.

     



  • @joe.edwards said:

    @TheCPUWizard said:
    Having developed (for clients) a number financial systems, I can tell you that it is not uncommon to use additional digits of precision in internal calculations, and then apply rounding on the outputs only. If one looks only at a sequential set of outputs [e.g. montly statements] it is quite possible to see "penny errors" because the internal number has crossed a 1 cent boundary.

    Isn't this incorrect, though? You don't actually make sub-penny payments. Monetary values are discrete, not continuous. If the figures disagree, it's the higher precision number that's in error, the lower precision number is representative of the actual value paid.

     Monetary TRANSACTIONS are discrete (at least for cash, and most electronic forms exposed to "Consumers") but values are NOT.

     Here is a simple proof..... Set up an interest calculation (simple interest) for a period of time (say 5 years) on an initial deposit, with no other transactions. Look at the final value. This is good.

     Now calculate the interest for the first hour. Round to the nearest penny (which means round to $0.00 for all but very large amounts), Now using this figure calculate the interest (and round) for the next hour... Still equal to the initial deposit..Repeat for the full 5 years, and at the end you still will not have gotten ANY interest (again, unless the amount is huge)...which is clearly BAD.


  • Considered Harmful

    I guess what I mean is the payments are rounded, so if your math is saying that your monthly payment is $1252.657645, your actual payment won't match that, and the figures skew if they are assuming that was the actual amount paid.



  •  You can get discrepancies just because one source rounds at a different point in the calculation, although it takes longer for it to add up to anything significant.  When I started a new 30-year mortgage, I embarked on a plan to pay it off in twenty without the payment getting too big along the way: for the first five years, I'd pay an additional amount equal to what the following month's principal would be.  For years six through ten, I cut the extra amount down to half the following month's principal, then five more years of one-third the following month's principal, and close it out with five years at one-sixth.

    This happens to come out evenly if you don't round, because 1 + 1/2 + 1/3 + 1/6 = 2.  In practice, though, I redid the figures every five years because the bank and I were carrying different amounts of fractional pennies, so I'd always find that the remaining balance had drifted a couple of bucks away from where I thought it should be.



  • And redoing the figures was more important than paying $20 on your last statement?

    =P



  • @Sutherlands said:

    And redoing the figures was more important than paying $20 on your last statement?

    =P

    No, it was more important than having a final $5.71 payment after the end of the twenty years because my calculations didn't quite amortize the entire amount.  If you think banks are logy on day-to-day stuff, you should see how they drag their feet sending you the forms that certify you've paid them off, especially when you do it before you originally said you were going to.

     



  • @da Doctah said:

    @Sutherlands said:

    And redoing the figures was more important than paying $20 on your last statement?

    =P

    No, it was more important than having a final $5.71 payment after the end of the twenty years because my calculations didn't quite amortize the entire amount.  If you think banks are logy on day-to-day stuff, you should see how they drag their feet sending you the forms that certify you've paid them off, especially when you do it before you originally said you were going to.

     Could be worse. I once paid off a load, but there was a <$0.10 discrepency. I got no further bills, but was getting hit with late penalties, then interest on the penalties, then more penalties....all without notification.

     Fast forward almost 4 years, get a call from a debt collector [Iam very carefuly about my credit]. They wanted over $5,000 to settle (i.e. *not* pay in full).

     Took getting a laywer to get them to back off.



  • @blakeyrat said:

    Plus it confuses the Euro-trash

    Good! Now I won't have ANY compunctions about referring to you as 'USA scum,' Mr. Rat.



  • Technically, a prepayment/overpayment penalty reduces the lender's reinvestment risk. Imagine, for instance, if you had a 10-year certificate of deposit you bought a little while ago with a 8% interest rate... then, today, the bank called up to say they were going to close the CD and give you all your money back and the interest you've earned so far. So you get all your money back... but the 8% interest rate is gone. You're not going to find it anywhere these days. (Maybe you can get 3%). You might be somewhat annoyed.

    Most CDs are not callable like that, but some are. If you had a choice between a callable CD and a non-callable CD, you'd never choose the callable one unless it paid a better rate, because it means that your interest rate is protected.

    The same applies in home finance. The fact that most US mortgages are prepayable adds a substantial bit to the interest rate (I forget exactly how much, but the sources I saw said it was in the neighborhood of 0.5%, maybe more). Is it worth it, if you're able to refinance your home mortgage in lower-interest-rate environments like this one? For most homeowners, possibly. There's a consumer-protection angle to making it illegal because the typical consumer is not going to be sufficiently educated to realize all the implications of a moderately sophisticated financial instrument. For an auto loan, it may be less of a big deal.

    You'll also notice that an adjustable-rate mortgage can get you a lower rate to start out with, because the lender can finance it by issuing short-term debt which they will roll over later, and the bond market will give them lower interest rates because it's less of a commitment and gives them more options down the road.

    @Justice said:

    [quote user="boomzilla"][quote user="TwoScoopsOfHot"]
    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

    You're obviously not very familiar with US mortgages. It's common to have an early payment fee as part of the contract. After all, they make money off of the interest that you pay, and so paying down principle faster reduces the amount that they make.[/quote] 

    I'm not positive about this, but I believe that varies by state.  When I bought my car I was told that early payments fees are illegal in New York, so I paid it off straight away.  Stories I've heard from homeowners suggest that it's also illegal here for mortgages.

    [/quote]



  • @boomzilla said:

    @Anonymouse said:
    @blakeyrat said:
    DUI checkpoints are illegal here in Washington State
    Because not being allowed to run over schoolchildren in a drunken stupor violates your constitutional rights?
    But why then are you advocating getting rid of school crosswalks?
    One word: Darwinism.

    @Cad Delworth said:

    @blakeyrat said:
    Plus it confuses the Euro-trash
    Good! Now I won't have ANY compunctions about referring to you as 'USA scum,' Mr. Rat.
    I'm surprised you even had them to begin with...



  • @Anonymouse said:

    @Cad Delworth said:

    @blakeyrat said:
    Plus it confuses the Euro-trash
    Good! Now I won't have ANY compunctions about referring to you as 'USA scum,' Mr. Rat.
    I'm surprised you even had them to begin with...


    Unlike Mr. Rat (for whom it seems to be second nature), I don't usually stoop to racist abuse of any kind. So actually, it would be pointless for me to do so in his case: he clearly wouldn't give a toss. I don't know whether or not the USA has laws against racial hatred, but I really must find out. And unlike the cowardly Mr. Rat, I do have the guts to post here under my real name, rather than hide behind a pseudonym. Though it's fairly easy to find his real name. ;)


  • ♿ (Parody)

    @Cad Delworth said:

    I don't know whether or not the USA has laws against racial hatred, but I really must find out.

    What would a "law against racial hatred" actually be? How would it be enforced? I suspect that you mean something along the lines of a law to prosecute people who say certain things. And in general, we actually have pretty robust free speech, which makes laws like that, which attempt to prosecute someone for saying something that does not actually incite violence or other lawless activity, non-criminal.

    Why would you think a law like this to be a good thing (which seems to be the implication)?

    I guess I missed the racism in blakey's post, anyways. Why did you bring it up?



  • 1) Yes, there are Hate Crime laws in the USA. Generally speaking these impose additional penalties to the commission of a crime when the act is based on a prejudcial hatred (race is a common one, but it is not specific).

    2) USA vs. EU is not racial....Cultural could be appropriate, and even Ethnic may be...but not Racial.



  • @TheCPUWizard said:

    1) Yes, there are Hate Crime laws in the USA. Generally speaking these impose additional penalties to the commission of a crime when the act is based on a prejudcial hatred (race is a common one, but it is not specific).

    It also has to be an actual crime. I can't trigger a Hate Crime law by talking on the Internet, which is protected by the First Amendment. If I hit you over the head with a baseball bat, and the court decides it was racially motivated, they could decide to up the penalty. Also note that all of this stuff varies by State; criminal law is the domain of the State, not the Federal Government, for all crimes except Federal Offenses. Yes, the whole concept is stupid. The laws were probably written by some touchy-feely Eurotrash.

    And yes, "American" isn't a race. We're kind of proud of that, you know? The whole "give us your tired, your weak, your yearning masses" thing?



  • It varies by mortgage contract in Canada. 2x isn't legislated or even necessarily the norm.

    On my previous home I had an open variable rate mortgage. An open mortgage you can close at any time with no penalty (this flexibility generally means a higher interest rate), and there was no limit to paying it off (on my particular contract). That being said I've heard of open mortgages that limit how much you can pay. You can close it (i.e. pay it all off, no penalty), but they limit your contributions if you aren't paying it off completely.

    I think my current mortgage is 20% of the principle per year max additional contributions. I'd have to check my contract to confirm.

    It would be nice if there was legislation that allowed you to pay as much back as you want. That may increase loaning rates slightly (depends on the stats behind people paying back early), but that's not necessarily a bad thing (housing prices are still soaring where I live despite issues with the economy). Of course, banks are free to offer an unlimited payment mortgage product at a different rate than their other mortgage products. If it doesn't exist, then there probably isn't a big push for it (as many are in no hurry to pay off their mortgage).



  • @blakeyrat said:

    I can't trigger a Hate Crime law by talking on the Internet, which is protected by the First Amendment.
    Sure you can, if it's obscenity, defamation, incitement to riot, or fighting words.



  • @Sutherlands said:

    @blakeyrat said:
    I can't trigger a Hate Crime law by talking on the Internet, which is protected by the First Amendment.
    Sure you can, if it's obscenity, defamation, incitement to riot, or fighting words.
    Not to mention that whole SOPA/PIPA mess...



  • @boomzilla said:

    @TwoScoopsOfHot said:
    What an interesting idea, charging an "overpayment fee", I assume you're not in the US, because I'm fairly sure that's illegal here.  If I owe you a debt, and I want to pay that debt off, I can do so at any time prior to the agreed upon date, and you cannot tell me "no, you're paying me back too fast."

    You're obviously not very familiar with US mortgages. It's common to have an early payment fee as part of the contract. After all, they make money off of the interest that you pay, and so paying down principle faster reduces the amount that they make.

    As far as I am aware, for mortgages and personal loans here (that I am aware of), it you're on a fixed rate, the issuer generally slugs you with a fee if you attempt to make any kind of early or large payment, the issuer will want a fee. If you're on variable rate, provided you at the least make the minimum payments you can make extra ones as you please, but they generally charge a fee if you pay within the first year or two on longer personal loans.



  • @blakeyrat said:

    Look. Every State has its own government. EVERYTHING varies by State. When you're posting stuff like this to the Internet, please say: "in New York, X" not "In the US, X" because if you say the latter you are almost certainly wrong.

    In the US, EVERYTHING varies by State.



  • @Xyro said:

    @blakeyrat said:
    Look. Every State has its own government. EVERYTHING varies by State. When you're posting stuff like this to the Internet, please say: "in New York, X" not "In the US, X" because if you say the latter you are almost certainly wrong.
    In the US, EVERYTHING varies by State.
    Except for those States where everything is the same.



  •  I live in Canada, and here saying anything that "offends or is likely to offend" a protected group (visible minorities, homosexual activists, etc.) is a hate crime. Even if you dodge the complaint in front of the Human Rights Comission (HRC) you still have to pay for the accuser's lawyer.


Log in to reply