"Bruno Campanini" wrote:
I can't continue any longer discussing of mathematics
while you are discussing of Excel formulas.

And I cannot continue any discussion with you as long as
you suffer from the delusion that anything I have said has
anything to do with the Excel implementation of financial
functions. Although I might use Excel functions to express
the exact value of something, I have verified my results
independently using "mathematics" as you put it -- by
which I presume you mean algebraic formulas involving
simple arithmetic operators (+,-,*,/,^) -- and using
"mathmetics" with another calculating device (HP 12C).

(HP 12C results differ slightly from PC computation after 9
significant digits. I conjecture that the HP 12C, released in
1981, might not use the IEEE-754 double-precision format
internally, first release as a standard in 1985. Just a guess.)

I will just tidy up some loose ends and be done with this
discussion.

I chose the iteration method so:

Pm 1
Im = ------- * 1 - ( ----------------) ^ n
C 1 + Im
[....]
Do until you have the first 10 or 15 or what you want
figures stable.

When I do this, I get the result that I expected, namely
1.24168310958368% -- approx 1.24168311% -- after
58-60 iterations, which matches the RATE() results, I
might add. I cannot say why you get different results.
There can be many reasons, none of which have to do
with "mathematical correctness" (approach).

No matter! Your method of determining the interest
rate based on the (rounded?) payment is fundamentally
flawed, at least for the purpose of this discussion.

The OP asked why his/her computation of the interest
amount during a period did not match the lender's.
In effect, the OP is asking how lenders determine the
interest rate and the interest amount.

Lenders do not determine the interest rate from the
payment amount, as you attempt to, nor do they have
to in order to be "mathematically correct". Instead,
they compute the payment based on the nominal
interest rate, which they set based on business
requirements, along with other terms of the loan
(loan amount, term of the loan, repayment frequency,
and interest charge frequency). There is nothing
fundamentally with letting the payment depend on
the interest.

To summarize ....

If the interest is compounded monthly, the mortgage
payment can be computed by:

PMT = PV * r / (1 - 1/((1 + r)^n))

where PV is the loan amount, n is the length of the
loan in months, and r is i/12, where i is the nominal
annual interest rate (not the APR). In Excel, it is
simply PMT(i/12, n,, -PV). Both methods yield the
same result for the OP's loan parameters, namely
$716.396878002440. The lender should always round
__up__ PMT at least to cents to ensure that the last
payment is no more than the others -- unless the
lender chooses to disclose the difference. In my
experience, lenders do round up.

If the interest is compounded daily (as in the OP's
case), you correctly point out that the mortgage
payment __should__ be computed as above, but r
is (1 + i/365)^(365/12) - 1. In Excel:
PMT((1 + i/365)^(365/12) - 1, n,, -PV). In Excel, r
computed using FV(i/365, 365/12,, -1) - 1. All of
theses approaches yield the same result, namely
r = 1.24915081949526% and PMT =
$717.818668999167 -- approx $717.82.

However, I cannot say if any lenders use either of
those formulas to determine the payment when
interest is compounded daily. Instead, the payment
might still be determined by the compounded-monthly
formula, and the daily interest rate might be as simple
as i/365. Either or both can result in a larger last
payment, which I believe the lender would have to
disclose in the Reg Z statement (for US loans).

That certainly seems to be that case for the OP. I
believe that is the "mathetical incorrectness" that
you intended to identify. And I agree.

As an aside, I hasten to point out that even if the
lender uses the more accurate compounded-daily
formula, the last payment is likely to be slightly higher
-- at least in amortization tables that I have created.
This is because months contain 30, 31, 28 and 29
days, not 365/12 days. So there will always be
some "mathematical incorrectness" due to numerical
analytical realities, albeit this disparity is small in the
OP's amortization table.

(In the OP's case, equal payments of $718.06 with a
final payment of $717.28 would work.)

In any case, I do not recall seeing disclosure of a
balloon payment in the conventional US mortgages
that I have been involved in directly or indirectly. So
I wonder if the lender is prepared to simply eat the
extra interest in the last period (suprise!). Many
mortgages are paid off before the last scheduled
payment anyway.

Nonetheless, if the payment is based on the monthly
compounding rate, but interest is compounded
daily, I believe that results in an acceleration of
interest -- and indeed an excess of interest paid if
the loan lasts long enough.

Since I sincerely doubt the latter can be the case for
US loans at least, I have some doubts about this analysis.
Unfortunately, I do not have any (US) Reg Z statements
close at hand to look at. So I cannot check the analysis
against reality -- not until next week.

But I hope this summary serves to put the discussion
back on track.

PS: One remaining question in my mind is whether
lenders round the interest computation each month,
or if they carry the numbers (interest and balance) to
whatever precision their computer hardware/software
allows.