Microsoft's Loan Amortization template

[Wilfred]

I may be missing the obvious, but I don't understand the logic of the Loan Amortization template
(http://office.microsoft.com/en-us/templates/TC010197771033.aspx?CategoryID=CT011377171033)
on Microsoft's website.

It appears to me that it doesn't correctly handle the final payment of a
loan. The payment amount of the last payment is reduced to equal the prior
month's ending balance (when the balance is less than the scheduled
payment). This doesn't reduce the principal to zero.

For Example:

Loan Amount: $10,000
Annual Interest Rate: 8%
Loan Period in Years: 1
Number of Payments Per Year: 12
Start Date of Loan: 4/1/2006
Optional Extra Payments $0.00

In this example, the Total Payment on the final row is reduced to equal the
Beginning Balance of $864.12 . This payment amount is used to apply
$5.76 to pay the interest on the balance and the remaining $858.36 of the payment
amount is applied to the principal, leaving an unpaid balance
of $5.76.

The balance of $864.12 minus the principal reduction of $858.36 does not
equal zero.

What am I missing?

 

[joeu2004]

I don't understand the logic of the Loan Amortization
template
(http://office.microsoft.com/en-us/templates/TC010197771033.aspx?CategoryID=CT011377171033)
on Microsoft's website.

Actually, I think you do very well.

It appears to me that it doesn't correctly handle the final
payment of a loan.
[....]
For Example:
Loan Amount: $10,000
Annual Interest Rate: 8%
Loan Period in Years: 1
Number of Payments Per Year: 12
Start Date of Loan: 4/1/2006
Optional Extra Payments $0.00
[....]
The balance of $864.12 minus the principal reduction of
$858.36 does not equal zero.

I agree with you: the template computation is wrong!

I would say that the Total Payment formula should be, at least:

=IF(AND(Pay_Num<>"", Sched_Pay+Extra_Pay<Beg_Bal+Int),
Sched_Pay+Extra_Pay, IF(Pay_Num<>"",Beg_Bal+Int,""))

Note the judicious addition of "+Int" in two places.

But that is not all that is wrong.

For starters, I believe the first Pay_Num<>"" should be
effectively Pay_Num<>NPER(Interest_Rate/Num_Pmt_Per_Year,
Scheduled_Monthly_Payment,-Loan_Amount). I would
prefer to replace NPER(...) with Number_of_Payments or
H7 (suitably named), but that creates a circular reference
due to template design. In fact, neithter of those values is
what we need anyway. NPER(...) should be placed in a cell
called "Max number of payments".

That accounts for a "balloon payment" in the case where the
sum of the payments is not sufficient to cover the loan plus
periodic interest. I believe this may happen because the
lender must round payments to cents, since they represent
a real exchange of money. (A prudent lender will round up
the payment to avoid such a "balloon payment".)

And to that end, the error that you point out is not the only
thing wrong with the template. For example, with your loan
terms, consider the line with payment #1. The sum of the
principal and interest is $0.01 greater than the payment
(803.22 + 66.67 <> 869.88), and the balance was reduced
by the "inflated" principal (10000 - 803.22 = 9196.78). I can
assure you that lenders will not give you credit for more
principal than you actually paid.

Of course, the flaw is the lack of judicious use of ROUND()
or ROUNDUP() before dependent computation in other cells.

In any case, I believe the computation of monthly interest
does not match real life. I believe most loans compound
interest daily at the rate of Interest_Rate/365 (some say 360).
The number of compounding periods will be based on the
actual payment dates. Even ignoring provisions for grace
periods and late payments, the Interest formula is still too
complicated to try to tweak here without careful testing. I
just want to point out the over-simplification of the template.

(If you are interested in the "correct" Interest formula, I
could provide that. But I believe other changes in the
template are needed in order for it to work with the new
formula.)

And finally, because of daily compounding, is unclear whether
the computation of Scheduled_Monthly_Payment (H5) matches
what lenders actually compute.

If it does, that is all the more reason why the Total Payment
formula needs to take the "balloon payment" into account.
On the other hand, the Scheduled_Monthly_Payment could be
computed to take daily compounding into account. I do not
know if lenders "typically" do that. But even that likely results
in a small "balloon payment" -- unless lenders take special
steps to avoid it.

I have not been able to confirm which methods lenders really
use to compute the monthly payment.

 

[Wilfred]

I am neither a banking expert nor a spreadsheet expert. I stumbled on the error intuitively. Just looking at the last payment seemed obviously wrong.

I replaced the Total Payment formula with your suggested formula. I played around with some of the other changes you suggest and I agree with you. The NPER formula works but it seems unnecessarily burdensome and quite frankly, I never would have figured it out on my own. When I replaced the first Pay_Num<>"" with $H$7, I didn't get a circular reference error.

I think that for bank loans, the interest computation formulas that may require calculation of interest on a daily basis are the ones that affect a partial month, e.g. at the beginning of a loan or when a premature payoff is involved. Mortgage payments for example, usually are paid at the end of the month. Most banks collect interest for the initial fraction of a month (date loan is issued through the end on the month) and then set fixed monthly payments for the term of the mortgage in "whole calendar months" (180, 240, 360 months, etc.). In essence, a 20 year mortgage is really 20 years plus the number of days between the loan origination and the end of the month. Consumer loans do not appear to follow this "calendar month" pattern but I think only a "payoff" would trigger a "daily interest" calculation. Of course, we were taught in the 7th grade that banks use a 360 day calendar, but I must admit that I have seen daily interest calculated both ways.

For my use (a consumer), the "simplified" interest calculation in the template is "good enough" although I agree that the rounding is "injudicious".

I don't understand the logic of the Loan Amortization
template
(http://office.microsoft.com/en-us/templates/TC010197771033.aspx?CategoryID=CT011377171033)
on Microsoft's website.

Actually, I think you do very well.

It appears to me that it doesn't correctly handle the final
payment of a loan.
[....]
For Example:
Loan Amount: $10,000
Annual Interest Rate: 8%
Loan Period in Years: 1
Number of Payments Per Year: 12
Start Date of Loan: 4/1/2006
Optional Extra Payments $0.00
[....]
The balance of $864.12 minus the principal reduction of
$858.36 does not equal zero.

I agree with you: the template computation is wrong!

I would say that the Total Payment formula should be, at least:

=IF(AND(Pay_Num<>"", Sched_Pay+Extra_Pay<Beg_Bal+Int),
Sched_Pay+Extra_Pay, IF(Pay_Num<>"",Beg_Bal+Int,""))

Note the judicious addition of "+Int" in two places.

But that is not all that is wrong.

For starters, I believe the first Pay_Num<>"" should be
effectively Pay_Num<>NPER(Interest_Rate/Num_Pmt_Per_Year,
Scheduled_Monthly_Payment,-Loan_Amount). I would
prefer to replace NPER(...) with Number_of_Payments or
H7 (suitably named), but that creates a circular reference
due to template design. In fact, neithter of those values is
what we need anyway. NPER(...) should be placed in a cell
called "Max number of payments".

That accounts for a "balloon payment" in the case where the
sum of the payments is not sufficient to cover the loan plus
periodic interest. I believe this may happen because the
lender must round payments to cents, since they represent
a real exchange of money. (A prudent lender will round up
the payment to avoid such a "balloon payment".)

And to that end, the error that you point out is not the only
thing wrong with the template. For example, with your loan
terms, consider the line with payment #1. The sum of the
principal and interest is $0.01 greater than the payment
(803.22 + 66.67 <> 869.88), and the balance was reduced
by the "inflated" principal (10000 - 803.22 = 9196.78). I can
assure you that lenders will not give you credit for more
principal than you actually paid.

Of course, the flaw is the lack of judicious use of ROUND()
or ROUNDUP() before dependent computation in other cells.

In any case, I believe the computation of monthly interest
does not match real life. I believe most loans compound
interest daily at the rate of Interest_Rate/365 (some say 360).
The number of compounding periods will be based on the
actual payment dates. Even ignoring provisions for grace
periods and late payments, the Interest formula is still too
complicated to try to tweak here without careful testing. I
just want to point out the over-simplification of the template.

(If you are interested in the "correct" Interest formula, I
could provide that. But I believe other changes in the
template are needed in order for it to work with the new
formula.)

And finally, because of daily compounding, is unclear whether
the computation of Scheduled_Monthly_Payment (H5) matches
what lenders actually compute.

If it does, that is all the more reason why the Total Payment
formula needs to take the "balloon payment" into account.
On the other hand, the Scheduled_Monthly_Payment could be
computed to take daily compounding into account. I do not
know if lenders "typically" do that. But even that likely results
in a small "balloon payment" -- unless lenders take special
steps to avoid it.

I have not been able to confirm which methods lenders really
use to compute the monthly payment.

 

[joeu2004]

When I replaced the first Pay_Num<>"" with $H$7, I
didn't get a circular reference error.

That is interesting. When I named H7 and used the name
in the Total Payment formula, I got a circular reference
because, as I recall (I'm not double-checking), one of the
elements of the formula in H7 referenced a MATCH()
formula (a mystery to me!) which referred to ending balance,
which of course depends on Total Payment. I wonder if you
replaced H7 with the NPER() formula that I suggested,
thereby avoiding the circular reference.

There was some reason why I wanted to preserve H7 as-is.
Oh yes: because without taking the time to understand the
formula, I thought it might track the actual number of extra
payment amounts retired the loan earlier.

I think that for bank loans, the interest computation
formulas that may require calculation of interest on a
daily basis are the ones that affect a partial month

My original mortgage 30+ years ago compounded monthly.
But when the mortgage was taken over by another lender
about 10 years later, the new lender compounded daily.
This was true for the subsequent 2-3 lenders that the
mortgage passed to due to mergers and acquisitions.
Hence, I ass-u-med that most modern mortgages do indeed
compound daily between monthly payments. I believe I
found some confirmation on the web, although I believe
I also found contradictions, and none of the web sites was
dispositive.

But all of this was "a long time ago in a galaxy far, far away".
Since my mortgage terms were never updated(!) after a
merger or acquisition -- I only noticed these changes by
analyzing past statements -- and because I have retired
the mortgage, I have no authority to ask right now.