Amort table - doesn't add up

[Bob]

Hi all,
I've been playing around what the amortization table
template, and have notice a perculiar oddity, and wanted
to know what you thought. I plunked into a couple extra
columns on the right of the table and made a cumlitive
pricipal entry. Simple enough. Wouldn't you think that
when the loan is all paid off, the total amount of
pricipal paid would be zero? I sure did! Nope, not the
case according to Excel. It almost always several dollars
off up or down. The first thing that came to mind was it
was some kind of rounding error, so I put in a few
rounding fuctions on the payment, principal and interest
columns to two digits. Still off. Next I made my own
schedule, slightly differently, and I'm still a penny
off. What's going on here? Anyone? No? How about you?

 

[Norman Harker]

Hi Bob!

I have samples that add the principal repayments and the sum of those
is adding correct to the 2nd or greater number of places. So I'll send
them on request to email below or you can send yours and I'll test my
virus protection is working.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob]

Hi Norman!
Thought that I'd been forgotten about.
I can do it so the sum is correct when nothing is
rounded. The problem I've got is that a real loan payment
cannot be divided up into anything less than a certian
integer number of cents (or dollars rounded to 2 places)
Do you have a sample that every balance and every payment
rounds off to two places, and the sum of each of the
principal payments equals the beginning amount. Another
test that all of my rounding examples are failing is
summing the remaining prinipal payments 'should' equal
the current balance. I put that column down and you can
see where it goes wrong. Even the am schedule (printout)
I got from a bank is wrong. A few months down the line,
it's getting one cent off up or down.

I guess a deeper question, is how do the banks 'really'
compute the interest each month.

 

[Norman Harker]

Hi Bob!

It's a sign of how speedy the responses are in these newsgroups that 5
1/2 hours is considered a long wait. Some times it's a case of those
specialising in the problem area being asleep. But well titled and
expressed problems like yours rarely go too long without an answer.
Re-express and re-post (saying it's a re-post) if it's more than a
day.

Except by coincidence the sum of the rounded payments will not equal
the principal.

But it's your final question that is important in terms of getting
accuracy of the totals and especially accuracy of the final balance.

"I guess a deeper question, is how do the banks 'really' compute the
interest each month."

I believe that most banks now compute interest on outstanding balances
on a day to day basis using the daily effective equivalent of the
quoted interest rate. We can replicate this in an amortization
schedule but it is complicated for "forward looking" schedules by the
need to determine with precision the date upon which payments are
actually credited to the account.

Because of this approach most amortization schedules will contain
errors because the interest calculated in the schedules is based upon
the monthly effective equivalent of the quoted rate and because all
months do not have the same number of days. Those errors are far
greater than the errors that result from your rounding problems.

For most purposes these problems are only an annoyance.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob]

Hi again Norman!
In America, most banks use a 30 day month. Payments are 'due' on the 1st and not 'late' until after the 15th. If you pay a 'monthly payment' early or before the 15th, it is credited to the account as if it were paid on the 1st. After the 15th there is a 5% of the payment amount late charge, but the remainder of that payment acts as if it were paid on the 1st. This is true for every month of the loan except the first and last. The first month is differet because the bank wants all the payments to post on the 1st every month, so if your new loan funds in the middle of the month, they charge 'odd days interest' or interest in advance, which is the loan amount times the note rate as decimal divided by 360, 365, 366, or 365.25 depending on the bank, times the number of days remaining in the month. Then the first payment is due the 1st of the following month after next as the rest of the interest is paid in arrears. So if you got a loan today, your first payment would be 'due' April 1. If the loan funds during the first week of month however, the first payment is due on the next 1st, less than 30 days away, so the bank gives you an interest credit for the number of days in the month before the loan funded. The last month of the loan, uses the payoff formula, of principal balance plus interest based on the number of days since the last payment (which is always the 1st remember) using a similar formula as for Odd Day's interest. However certian types of loans always count the interest until the end of the month no matter when it was paid off. Also add any periodic charge like PMI if applicable.
For the Am schedule I've got, the first month is easy because the odd day's interest is included in the loan amount, ironic if you think about it, paying interest on interest. And the last month is easy using a MIN(PAYOFF AMOUNT,NORMAL PAYMENT) function down the line, or if the loan is in it's maturity month, then simply PAYOFF AMOUNT no matter what it is. In any case, there's got to be a way that the sum of the actual rounded dollars and cents of principal payments remaining can equal the principal balance at any given month given these rules.
Hope this clears it up alittle. Let me know what you think.
Bob

 

[Norman Harker]

Hi Bob!

What will be interesting in your calculations is your calculation of
the IRR found by creating a schedule comprising the Loan and the
repayments (excluding PMI). Is that different from the declared rate
of interest? Does Truth in Lending apply?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi again Norman!
In America, most banks use a 30 day month. Payments are 'due' on

the 1st and not 'late' until after the 15th. If you pay a 'monthly
payment' early or before the 15th, it is credited to the account as if
it were paid on the 1st. After the 15th there is a 5% of the payment
amount late charge, but the remainder of that payment acts as if it
were paid on the 1st. This is true for every month of the loan except
the first and last. The first month is differet because the bank wants
all the payments to post on the 1st every month, so if your new loan
funds in the middle of the month, they charge 'odd days interest' or
interest in advance, which is the loan amount times the note rate as
decimal divided by 360, 365, 366, or 365.25 depending on the bank,
times the number of days remaining in the month. Then the first
payment is due the 1st of the following month after next as the rest
of the interest is paid in arrears. So if you got a loan today, your
first payment would be 'due' April 1. If the loan funds during the
first week of month however, the first payment is due on the next 1st,
less than 30 days away, so the bank gives you an interest credit for
the number of days in the month before the loan funded. The last month
of the loan, uses the payoff formula, of principal balance plus
interest based on the number of days since the last payment (which is
always the 1st remember) using a similar formula as for Odd Day's
interest. However certian types of loans always count the interest
until the end of the month no matter when it was paid off. Also add
any periodic charge like PMI if applicable.

For the Am schedule I've got, the first month is easy because the

odd day's interest is included in the loan amount, ironic if you think
about it, paying interest on interest. And the last month is easy
using a MIN(PAYOFF AMOUNT,NORMAL PAYMENT) function down the line, or
if the loan is in it's maturity month, then simply PAYOFF AMOUNT no
matter what it is. In any case, there's got to be a way that the sum
of the actual rounded dollars and cents of principal payments
remaining can equal the principal balance at any given month given
these rules.

 

[Bob]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

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.

 

[Bob]

Norman,
I found a solution (cop up solution, but solution none
the less):

My am schedule already does a pretty good job. I wanted
to see what the amount of principal paid at any given
time in the future would be. What I was trying to do was
SUMIF the payment number <= the given time in the future,
then add the corresponding prinical payments from the
principal column. You'd think that would work, at least I
did, and it was close. A much simpler way, and it works
with any rounding errors, instead of against them, is to
take the current balance, minus the balance of the future
month in question. Works every time (so far).

Here's another question that you might be able to
enlighten me on. Say you're trying to determine the "true
cost of a loan" at a given time in the future. What I've
got right now expresses this as amount of interest paid
to date + amount of other fees paid (like PMI) to date +
the closing costs. I'm now convinced that this is
not "true" because I think it's only "true" if the
closing costs were actually paid out of pocket. If they
were rolled into the loan, this is the part I'd like your
opinion on, the interest and PMI portions stay the same,
but change the closing costs to MIN(Closing costs, MAX
(Prinipal paid to date minus Closing Costs,0) because if
you haven't actually paid for them with your own money,
it's not yet a cost to you. What I'm trying to avoid is
this: at the time when the loan is completely paid off,
the "true" cost is total interest paid, plus total other
monthly fees paid, plus total out-of-pocket closing
costs. The reason I'm distinguising between out of poctet
closing costs, and those that are rolled into the new
loan, is because if they were all rolled in, the "true"
cost would be the total of all the payments minus the
beginning loan amount (which is the same as the total
principal paid). By rolling in the closing costs you
actually pay for them over time. The question is, how to
you deteremine how they're paid over time? I'm interested
in how you would do it. Additionally, if some of the
closing costs were paid out-of-pocket, and some more were
rolled in, the "true" costs would add the out of pocket
ones directly, and the rolled in ones only equal the the
principal paid to date maxing at rolled in closing cost's
amount. Note: if the loan is paid off, by winning the
lottery, selling the house, or refinancing, before the
prinical payments to date surpasses the amount of rolled
in closing costs, then the Payoff Amount must cover the
additional require principal to date thus, making all of
the closing costs a cost. If you pay it off when the
balance is half of what it started at, the prinical
payments to date have covered all of the closing costs,
so the how would you find the "true" cost of the loan
then? I'm not talking about APR here, just dollar figures.

Thanks,
Bob

 

[Norman Harker]

Hi Bob!

Calculating true costs of loan! A matter close to my heart. The
essential technique is one of comparing actual payments with the
effective loan.

Looking at up front payments. If they are paid out of pocket. You
calculate the PMT based upon the loan amount. You then calculate the
RATE based upon the loan less up front fees and the previously
calculated PMT.

Looking at payments that are added to the loan. Add the fees to the
loan and calculate the PMT. Then calculate the RATE based upon the
loan without fees and your calculated PMT.

With both alternatives you can nest the first calculation in the
second calculation to get a direct result.

Here's a comparison of loan fees of $1000 on a loan of $100000 over 10
years at 6% nominal compounded monthly:

Out of Pocket method:
=RATE(120,PMT(6%/12,120,100000,0,0),100000-1000,0,0,0)
Returns: 0.518554630373894%

Rolled into loan method:
=RATE(120,PMT(6%/12,120,100000+1000,0,0),100000,0,0,0)
Returns: 0.51836962152902%

You'll see that the "rolled into loan" method has the lower effective
rate. This is because the fees are paid over a 10 year period.

However, the "rolled into loan" method gives you higher total payments
on the loan because you're paying interest on the fees.

Out of Pocket method:
=PMT(6%/12,120,100000,0,0)*120-1000
Returns: -134224.602329982

Rolled into loan method:
=PMT(6%/12,120,100000+1000,0,0)*120-1000
Returns: -135556.848353282



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob]

Hi Norman,
Care to clarify your last response a little please?
Are you saying that the "true" cost (dollar amount
viewpoint) of the loan with rolled in fees is

Rolled into loan method:
=PMT(6%/12,120,100000+1000,0,0)*120-1000
Returns: -135556.848353282

and paying the fees out of pocket is

Out of Pocket method:
=PMT(6%/12,120,100000,0,0)*120-1000
Returns: -134224.602329982

?

I would be more likely to say the the "true" cost of a
loan would be the total of all payments (here, payments
means principal, interest, PMI and other associated
recurring fees, and any out of pocket set up fees or
closing costs) minus the beginning loan balance. Or put
another way, anything paid by the borrower at any time
before or during the loan, except for principal balance
repayments. Because if you lent me $10, and I paid you
back $10 however long it took me to do that and you
declared my debt paid in full, the true cost of my loan
was $0. The $10 I repaid you wasn't mine to begin with,
so it wasn't a cost to me. If I had to pay you $11 to
have it paid in full, then the "true" cost of the loan to
me would be $1. I'm not talking about note rates, APRs,
effective/nominal rates or things of that nature, because
as I previously mentioned, American banks calculate them
in an arbitrary manner. But nothing is arbitrary about
the actually dollars that a borrower repays. Also, how
would you calculate the "true" cost of your example loan
if it were taken out today, and I wanted to know what the
total "true" cost were 36 months from now? How about if
the closing costs were instead $5000, and say only 4
months had gone by?
Regards,
Bob

 

[Norman Harker]

Hi Bob!

By all means deduct the amount of the loan to get the cost net of
principal repayment as opposed to the total of repayments.

What you regard as the cost at future dates depends upon whether or
not you are only looking forwards or whether you are looking both
forwards and backwards.

If looking forwards only, you compare the future payments with the
outstanding balance of the loan. The complicating factor would be
where the set up costs were rolled into the loan. There are different
approaches to handling that but I suppose I'd tend towards a ratio
approach adopting as the ratio the commencing loan / fees ratio.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob]

Hi Norman!
That's very similar to what I've been doing. I've
been "crediting" the first several pricipal payments
towards the rolled in closing costs up to the point where
they are covered. That way the "true" cost of a loan
where all of the fees are rolled in really does equal the
amount of non principal payments, if the first few went
to cover the rolled in fees. Hmm, or does it? I keep
flipping around on this issue. If there had been no fees,
than the loan amount would have been smaller, therefore
less interest, less "true" cost. Perhaps you do need to
include the rolled in closing costs at the end on the
loan in addition to non-principal payments. But that's
why I was "crediting" the first few principal payments
towards the rolled in fees.
You can see I've even confused myself. Please continue.
Bob

 

[Norman Harker]

Hi Bob!

With the fees absorbed into the loan Excel won't "distinguish" between
the fees and the loan. The user might however take the view that
payments made until the balance falls to the amount of the loan are
payments of the fess and that thereafter they are repayments of the
loan. That appears quite logical.

A fully adjusted estimate of true loan costs takes account of up front
fees, periodic "service" fees, and any terminal payments. If you
express true cost as the effective interest rate, you'll find that
these all vary in curved line relationships depending upon the length
of the loan term. In passing, you'll find that truncating the agreed
loan term increases the effective interest cost; perhaps one reason
why banks encourage accelerated repayments!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.