Re: Amort table - doesn't add up

From: Norman Harker (njharker_at_optusnet.com.au)
Date: 02/14/04 

Date: Sun, 15 Feb 2004 09:01:59 +1100

Hi Bob!

Just looking at just the effective cost of loan issues.

>From a borrower's perspective:
In my view the initial amount is the loan less any charges that relate
solely to the existence of the loan. Similarly the repayment schedule
comprises the loan repayments and any other charges that relate to the
loan. In my experience the only really questionable item is any real
estate appraisal fees. If the borrower would have had an appraisal,
then it is not a cost of loan. If the borrower only had the appraisal
because of lender requirements, then it is a cost.

>From a lender's perspective:
We don't get the normal mirror image of the borrower's cash flow. With
respect to ancillary costs, if those are paid to a third party, then
they don't represent part of the income from the loan. So PMI is a
cost to the borrower but is not income to the lender. And it should be
clear that the calculated return is a gross return that takes no
account of the lender's internal costs.

As far as the rounding issues are concerned, life can get complicated
if banks adopt a policy of not accepting checks that are not rounded
to the nearest 5c.

I remain surprised that US banks haven't moved to compounding daily
balances at the daily effective rate. I think that they are an
exception here and it is certainly easier to manage than the complex
rules you describe. It also has the advantage of ensuring Truth In
Lending. 

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob" <anonymous@discussions.microsoft.com> wrote in message
news:C0191D69-1194-4723-B57C-DAE3EAE7BB12@microsoft.com...
>    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. After 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