Re: Amort table - doesn't add up

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Bob (anonymous_at_discussions.microsoft.com)
Date: 02/14/04 

Date: Sat, 14 Feb 2004 09:21:06 -0800

   Well, the schedule of payments is affected very little by the various rounding errors. Only the last payment is affected. It is usually off by a few cents, even under a standard loan amortization the last payment will always be a few cents different the the rest of the payment except by dumb luck. So, to your question, firstly in order to get a rate back using IRR, I've got to put an 'initial investment' at the top of the column, followed by the 'gross profit' of each period after that, which I've got as payments in negative. The initial investment you've really got 2 choices. The first choice is to use the beginning loan amount, which returns the declared rate / 12 in my case. Because the rounding errors only effect the last payment, and only by a few cents, the rate IRR returns is still accurate to 3 places, unless the last payment is several dollars off, then you start to notice a difference. The second choice for the initial investment is to use the amount financed, which is the beginning loan amount
minus the so-called pre-paid finance charges, which usually are just the closing costs. Under this choice, IRR returns the APR, who's only purpose (in America) is for the Truth in Lending Disclosure. Even that is sort of arbitrary because diffent banks decide on different closings costs to call pre-paid finance charges, so the exact same loan with the same closing costs can have a different ARP depending on the bank's choices of what closing costs are pre-paid finance charges and which ones aren't. Additionally, banks calculate the payment schedule for adjustable rate mortgage different, causing remarkably greater discrepancy in APR calculations. For example, adjustable rate mortgage, or ARMs have a starting rate, and an index and a margin. A common index in the LIBOR, right now it's roughly 1.4%, and the margin is the bank's mark up on the rate and depends on the loan, but 2.25% is common. The start rate depends on how long the start rate is going to be fixed for, the longer the time, the higher the rate. A
fter the fixed period is over the rate changes each adjustment period, usually 6 or 12 months, under restriction, like no more than 2% change the first change, 3% per change after that, and 6% over the life of the loan, for example. Unless the rate needs to change by more than the restrictions, at the date of adjustment, it chages to equal the current index plus the margin. Some banks calculate APR based on a payment schedule where, as soon as it can, the rate adjusts to match the index (from the beginning of the loan) plus the margin, and assumes the index will never change. This produces an APR that can be dramatically lower than the start rate. The Truth in Lending Disclosure is supposed to reflect a worst case scenerio with no extra pre-payments, and in my opinion, the rate changing the absoluate worst that it can, and as soon as it reaches the life cap, it stays there until payoff. This results in dramatically higher APR than the start rate.
   We've gotten off the topic alittle, but no harm done. Have you been able to come up with a rounded-to-2-places am schedule that accurately has the remaining principal payments equallying the current balance? I have, but I've found a problem elsewhere. I've got three cell, say P1, Q1, and R1, where I've putting in the number of month's that have gone by. Using some SUMIF formulas, it's supposed to return the amount of interest paid so far, principal paid so far, and total payment so far. So far, so good. However, the interest to date plus principal to date is again off by about a penny compared to the principal to date as soon as you get a few months into it. After examining the prinout schedule from a real bank, it some how has this same rounding mistake. Say in month 15 the principal + interest - payment = +or-$0.01 That's just plain odd. Money from nothing I guess.
   Say you've got an interest bearing account. You would expect it to eventually get bigger, but if there was only one cent in the account, each month it would round back down to one cent. Now say, you've got $10,000 in 1,000,000 separate accounts, each holding one cent, you can really see how your round off error adds up. The bank can't put one cent of interest into some of the accounts, and none into others, and unless I'm wrong, they're not going to put one in every account. But that's a whole other problem.
I digress,
Bob