Hi Guys

I have a problem, and am not too good in excel ... and am not a
financial wizard so please be patient :-)

The file I am working with is available 'here'
(http://www.jacqdar.com.au/bits/AMORTIZE2.xls)

(1) I need to work out the value of 2 years worth of interest on a loan
- details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the
interest per month etc ... at this time I simply highlight and
calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest
calculations (although the repayments are the same and the total
interest payable amounts are the same)

The banks calculations are said to be done on the basis that the
minimum monthly repayment is met and there are no additional fees
thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan,
traditionally amortisation tables calculate the amount of the
repayments, the interest component and the principal component ... some
include a column where you can factor in additional repayments on a row
by row basis (like the one I am referring to) the table assumes that
the payment is made on the due date ...

what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert
an overdue fee


Any and all help greatly appreciated.

MTIA

Darrin


--
dwe
------------------------------------------------------------------------
dwe's Profile: http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

"dwe" wrote:
The file I am working with is available 'here'
(http://www.jacqdar.com.au/bits/AMORTIZE2.xls)
[....]
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet
which calculates the interest per month etc ...
[....]
the problem is this calculation varies from the bank
interest calculations

That does not surprise me. The bank probably compounds
interest daily, whereas the spreadsheet you point to
compounds monthly.

(although the repayments are the same and the total
interest payable amounts are the same)

The latter would surprise me, unless the bank's statement
of the total interest is only an estimate.

If 14.9% is the nominal annual rate, the daily rate is
14.9%/365. Since you have the payment date in the
spreadsheet, the monthly interest can be computed as:

(PreviousBalance)*(1 + 14.9%/365)^(DueDate - PreviousDueDate)
- (PreviousBalance)

Actually, you should compute =ROUND(...,2), where "..." is
the expression above. And you need to add the loan date
in the row with the initial balance.

Does that come a little closer to the bank's computation?

PS: If 14.9% is the APR, other adjustments are needed
to match the bank's computation.

Postscript and errata ....

I wrote:
the monthly interest can be computed as:

(PreviousBalance)*(1 + 14.9%/365)^(DueDate - PreviousDueDate)
- (PreviousBalance)

The following alternative might seem more clear:

ROUND(FV(14.9%/365, DueDate - PreviousDueDate,, -PreviousBalance), 2)
- PreviousBalance

Actually, you should compute =ROUND(...,2), where
"..." is the expression above.

With the previous formula, "..." should be just the
"(...)*(...)^(...)" part. That is, round before subtracting
the previous balance in the end.

"dwe" wrote in message
...

Hi Guys

I have a problem, and am not too good in excel ... and am not a
financial wizard so please be patient :-)

The file I am working with is available 'here'
(http://www.jacqdar.com.au/bits/AMORTIZE2.xls)

(1) I need to work out the value of 2 years worth of interest on a loan
- details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

1 - Banks don't know mathematics
2 - Then the often make mistakes in calculations
3 - When they make mistakes the loss is always for client

So, these are the exact parameters for your plan
(as is results from your file):
Loan = 30 180.00
Montly interest = 1.2416768%
Terms = 60 months

A monthly interst rate of 1.2416768% corresponds
to 15.961% annual rate.
You get this with the formula:
(1 + 0.012414768)^12 - 1 = 0.15961 = 15.961%

From 15.961% per year you get daily interest rate with:
(1 + 0.15961)^(1/365) - 1 = 0.000405791 = 0.0405791%
Then the overdue fee for 23 day-delay in montly payment is:
716.40 * ((1 + 0.000405791)^23 -1) = 6.71623

Anything less then this is a gift compared with mathematical
results, anything more is a robbery.
I suppose the second is the case.

Ciao
Bruno

Hi Bruno and Joe - many thanks for the responses ...

Wow, well I am lost somewhat ... but will keep going ...

Bruno - how do you arrive at 15.961% annual rate - when the interest
said to be charged by the bank is 14.9% ...

MTIA

Darrin


--
dwe
------------------------------------------------------------------------
dwe's Profile: http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

To calculate an effective annual rate, ask the question "if I borrowed $100 at
the start of the year, made no payments, how much would I owe at the end of the
year?". If interest is compounded monthly, the answer is:

=FV(14.9%/12,12,0,-100) = 115.961

You now know that 14.9% compounded monthly is the same as 15.961% compounded
annually. 14.9% compounded daily is an effective annual rate of 16.06%. As you
can see, the compounding period has a significant effect on the actual interest
being charged.

--
Regards,
Fred


"dwe" wrote in message
...

Hi Bruno and Joe - many thanks for the responses ...

Wow, well I am lost somewhat ... but will keep going ...

Bruno - how do you arrive at 15.961% annual rate - when the interest
said to be charged by the bank is 14.9% ...

MTIA

Darrin


--
dwe
------------------------------------------------------------------------
dwe's Profile:
http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

"Bruno Campanini" wrote:
"dwe" [...] wrote:
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months
[....]
So, these are the exact parameters for your plan [...]:
Loan = 30 180.00
Montly interest = 1.2416768%
Terms = 60 months

A monthly interst rate of 1.2416768% corresponds
to 15.961% annual rate. You get this with the formula:
(1 + 0.012414768)^12 - 1 = 0.15961 = 15.961%

I think you intended to write 1.2416667% (14.9%/12).
I suspect that you had correctly computed
(1 + 14.9%/12)^12 - 1 = 15.960861%. But after you
rounded that to 15.961%, you tried to reverse-engineer
the monthly rate with: (1 + 0.15961)^(1/12) -1 =
1.2416768%.

From 15.961% per year you get daily interest rate with:
(1 + 0.15961)^(1/365) - 1 = 0.000405791 = 0.0405791%

I concur that this computation would be consistent with the
usual way to compute the monthly payment. But I would
use accurate numbers to minimize numerical error, namely:
(1 + (1 + 14.9%/12)^12 - 1)^(1/365) - 1 =
(1 + 14.9%/12)^(12/365) - 1 = 0.0405788%.

To be honest, I am not sure that lenders do not do just that,
instead of compounding 14.9%/365, as I had said. Your
formula would explain why the OP's numbers match the
lender's computation of the payment and the total interest,
even though interest for individual months did not match.

I am trying to get ahold of my daughter's loan records to
vet all of this for modern (US) loans.

Then the overdue fee for 23 day-delay in montly payment
is: 716.40 * ((1 + 0.000405791)^23 -1) = 6.71623

That might be one way for the lendor to compute the late
fee plus interest. But there is no "mathematical" right or
wrong here. In fact, the late fee could be totally unrelated,
as is the case with my 30-year-old loan. The late fee was
8% per month times the monthly payment, even though the
nominal annual rate was 8.75%.

Anything less then this is a gift compared with mathematical
results, anything more is a robbery.

Perhaps. But arguably the lender chooses a high late fee
partly to discourage you from being late and partly due to
uncertain opportunistic costs that the lender might incur
because late payments might mean that the lender has
less capital to work with.

" wrote
in message ...
"Bruno Campanini" wrote:
"dwe" [...] wrote:
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months
[....]
So, these are the exact parameters for your plan [...]:
Loan = 30 180.00
Montly interest = 1.2416768%
Terms = 60 months

A monthly interst rate of 1.2416768% corresponds
to 15.961% annual rate. You get this with the formula:
(1 + 0.012414768)^12 - 1 = 0.15961 = 15.961%

I think you intended to write 1.2416667% (14.9%/12).
I suspect that you had correctly computed
(1 + 14.9%/12)^12 - 1 = 15.960861%. But after you
rounded that to 15.961%, you tried to reverse-engineer
the monthly rate with: (1 + 0.15961)^(1/12) -1 =
1.2416768%.

No.
I calculated effective montlhy rate of interest from:
1 - principal 30 180.00
2 - terms = 60 month
3 - montly payment = 716.40

Say Im the monthly interest
Iy the yearly interest
Id the daily interest

Then from
(1 + Iy) = (1 + Im)^12
I get Iy = (1 + Im)^12 - 1

From
(1 + Iy) = (1 + Id)^365
I get Id. = (1 + Iy)^(1/365)

This is the only mathematically-correct way to make
such calculations.
For sure the parts may agree on a more practical way
of making such calculations.
But a "mora practical way" translates in a little
"more profit" for one part and a little "more loss" for
the other. No doubt on this point.

That might be one way for the lendor to compute the late
fee plus interest. But there is no "mathematical" right or
wrong here. In fact, the late fee could be totally unrelated,
as is the case with my 30-year-old loan. The late fee was
8% per month times the monthly payment, even though the
nominal annual rate was 8.75%.

Perhaps. But arguably the lender chooses a high late fee
partly to discourage you from being late and partly due to
uncertain opportunistic costs that the lender might incur
because late payments might mean that the lender has
less capital to work with.

I agree with you on this point.
Normally the bank states a higher interest rate for late
overdue fee, the client accepts and they undersign an act.
Then the "act" is the "law" between them.
You can only tell a mathematical judgment, nothing more.

Ciao
Bruno

Hi. Just two cents:
=Effect(14.9%,12)
returns 15.961%

We note the equation for Effective monthly rate from above...
=(1 + 14.9%/12)^12 - 1

We note that as the time period tends towards infinity, the above equation
reduces to:

=EXP(14.9%)-1
16.067%

--
Dana DeLouis
Win XP & Office 2003


"Fred Smith" wrote in message
...
To calculate an effective annual rate, ask the question "if I borrowed
$100 at the start of the year, made no payments, how much would I owe at
the end of the year?". If interest is compounded monthly, the answer is:

=FV(14.9%/12,12,0,-100) = 115.961

You now know that 14.9% compounded monthly is the same as 15.961%
compounded annually. 14.9% compounded daily is an effective annual rate of
16.06%. As you can see, the compounding period has a significant effect on
the actual interest being charged.

--
Regards,
Fred


"dwe" wrote in message
...

Hi Bruno and Joe - many thanks for the responses ...

Wow, well I am lost somewhat ... but will keep going ...

Bruno - how do you arrive at 15.961% annual rate - when the interest
said to be charged by the bank is 14.9% ...

MTIA

Darrin


--
dwe
------------------------------------------------------------------------
dwe's Profile:
http://www.excelforum.com/member.php...o&userid=27914
View this thread:
http://www.excelforum.com/showthread...hreadid=490382

I agree that EFFECT and NOMINAL are simple ways of converting to/from effective
interest rates, but I find people understand much more quickly when I use my
future value example. As soon as they calculate the future value of $100, they
say "now I understand how compound interest works."

--
Regards,
Fred


"Dana DeLouis" wrote in message
...
Hi. Just two cents:
=Effect(14.9%,12)
returns 15.961%

We note the equation for Effective monthly rate from above...
=(1 + 14.9%/12)^12 - 1

We note that as the time period tends towards infinity, the above equation
reduces to:

=EXP(14.9%)-1
16.067%

--
Dana DeLouis
Win XP & Office 2003


"Fred Smith" wrote in message
...
To calculate an effective annual rate, ask the question "if I borrowed $100
at the start of the year, made no payments, how much would I owe at the end
of the year?". If interest is compounded monthly, the answer is:

=FV(14.9%/12,12,0,-100) = 115.961

You now know that 14.9% compounded monthly is the same as 15.961% compounded
annually. 14.9% compounded daily is an effective annual rate of 16.06%. As
you can see, the compounding period has a significant effect on the actual
interest being charged.

--
Regards,
Fred


"dwe" wrote in message
...

Hi Bruno and Joe - many thanks for the responses ...

Wow, well I am lost somewhat ... but will keep going ...

Bruno - how do you arrive at 15.961% annual rate - when the interest
said to be charged by the bank is 14.9% ...

MTIA

Darrin


--
dwe
------------------------------------------------------------------------
dwe's Profile:
http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

Hey guys this is great and I really appreciate the assistance ...

One more thing I need to work out is the rate of total interest payable
on a worst case scenario ..

1 - principal = 30, 180.00
2 - interest is 14.90% pa calculated daily
3 - terms = 60 month
4 - montly payment = 716.40
5 - late payment fee = 85
6 - interest on arrears is calculated at 24% pa on a daily basis

example: the borrower loans $30,180.00 - his monthly repayments are due
on say the 1st of every month following the date of funding - he fails
to pay his first instalment - but pays it 14 days late - he would then
be liable for the payment of $714 + $85.00 + 24% on $714 for 14 days

How do I equate this to a % on a pa basis ... such as 14.90% equates to
15.961% pa

Hoping I make sense ...

Regards

DWE


--
dwe
------------------------------------------------------------------------
dwe's Profile: http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

Fred - I have to say your $100 example assisted me greatly in
understanding the effective interest rate ... I am truly greatful for
simplified answers because as I said in my opening post - I am no
financial wizard :-)

DWE


--
dwe
------------------------------------------------------------------------
dwe's Profile: http://www.excelforum.com/member.php...o&userid=27914
View this thread: http://www.excelforum.com/showthread...hreadid=490382

dwe wrote:
6 - interest on arrears is calculated at 24% pa on a daily basis
[....]
How do I equate this to a % on a pa basis ... such as 14.90%
equates to 15.961% pa

The correct way to ask the question is: what is the effective
annual rate of the nominal annual rate of 24% compounded
daily. The answer is:

=FV(24%/365,365,,-1) - 1

which is 27.11%. That is the same as (1 + 24%/365)^365 - 1
and EFFECT(24%,365). But I hope the FV() formulation helps
you understand the concept.

"Bruno Campanini" wrote:
" [...] wrote:
So, these are the exact parameters for your plan [...]:
Loan = 30 180.00
Montly interest = 1.2416768%
Terms = 60 months
[....]
I think you intended to write 1.2416667% (14.9%/12).
I suspect that [...] you rounded that to 15.961%,
you tried to reverse-engineer the monthly rate with:
(1 + 0.15961)^(1/12) - 1 = 1.2416768%.
[....]

No. I calculated effective montlhy rate of interest
from:
1 - principal 30 180.00
2 - terms = 60 month
3 - montly payment = 716.40

Then I compute 1.24168311% = RATE(60,716.40,-30180).
If you continue to disagree, please provide the
exact formula or method and numbers that you use
to compute 1.2416768%. I would be very interested
-- no pun intended ;-).

This is the only mathematically-correct way to
make such calculations.

No need to pontificate. You are preaching to the
choir. I was not questioning your methods, merely
your number.

If by "mathematically correct", you mean a monthly
interest rate that reduces the principal to zero
with 60 payments all of which are $716.40, then
RATE(60,716.40,-30180) -- approx 1.24168311% --
is the correct rate, not 1.2416768%. This is
self-evident if you build an amortization table
and dispense with any rounding. With 1.2416768%,
the last payment must be $716.29, whereas with
RATE(60,716.40,-30180), the last payment is
"exactly" $716.40, formatted to 6 decimal places.

Alternatively, you can use the following formula:

FV = (-30180 + 716.40*(1 - (1+i)^(-60))/i)/((1+i)^(-60))

When i = 1.24168311%, FV is nearly zero (-7E-6);
FV is -2E-8 when i = RATE(60,716.40,-30180).
When i = 1.2416768%, FV is 0.11, indicating that
that 60 payments of $716.40 is too much. $0.11
is consistent with requiring the last payment to
be $716.29 ($716.40 - $.11) in the aforementioned
amoritzation table.

No matter! I think you make the important point
that lenders might use (1 + 14.9%/12)^(12/365),
not 14.9%/365, to determine the effective daily
rate. The first formula yields the same effective
annual rate as the nominal monthly rate (14.9%/12).
Arguably, that does seem more "mathematically
consistent".

I cannot say for sure at this time which formula
lenders use -- or even if they all make the same
choice.

" wrote
in message ...

[...]
Then I compute 1.24168311% = RATE(60,716.40,-30180).
If you continue to disagree, please provide the
exact formula or method and numbers that you use
to compute 1.2416768%. I would be very interested
-- no pun intended ;-).

No pun at all, only a little of mathematics.
Given:
Pm = monthly payment
n = terms = 60 months
Im = monthly interst = 0.01246768
C = principal = 30180

Im
Pm = C * ----------------------------- = 716.3987949... = 716.40
1
1 - ( ----------------) ^ n
1 + Im

But I don't have Im, just I must calculate it from the above formula.
I can do this in a lot of ways.
I chose the iteration method so:


Pm 1
Im = ------- * 1 - ( ----------------) ^ n
C 1 + Im

Put Im = your "guess" (say 0.0125) into the second member.
You get Im = 0.012472421

Put again this Im into the second member
You get Im = 0.012454076

Put again this Im into the second member
You get Im = 0.012441771

You see Im is decreasing every iteration, starting from
the 6th figure, the first 5 figures being stable.
Do until you have the first 10 or 15 or what you
want figures stable.

No need to pontificate. You are preaching to the
choir. I was not questioning your methods, merely
your number.

If by "mathematically correct", you mean a monthly
interest rate that reduces the principal to zero
with 60 payments all of which are $716.40, then
RATE(60,716.40,-30180) -- approx 1.24168311% --
is the correct rate, not 1.2416768%. This is
self-evident if you build an amortization table
and dispense with any rounding. With 1.2416768%,
the last payment must be $716.29, whereas with
RATE(60,716.40,-30180), the last payment is
"exactly" $716.40, formatted to 6 decimal places.

I can't continue any longer discussing of mathematics
while you are discussing of Excel formulas.
Here is my amortization table made in Excel sheet
using mathematical, not Excel's, formulas.

A = 30 180.00 Tassi relativi a Durate in periodi
Rate/anno = 12 Anni 1/12 di anno di 1/12 di anno
i(1) = 15.96% 2 0.012416768 24
i(2) = 15.96% 2 0.012416768 24
i(3) = 15.96% 1 0.012416768 12
iv = 6.00% 0.004867551 60
716.40

42 983.93 12 803.93 30 180.00
Rata QI QC DE DR
30 180.00
1 716.40 374.74 341.66 341.66 29 838.34
2 716.40 370.50 345.90 687.56 29 492.44
3 716.40 366.20 350.20 1 037.76 29 142.24
4 716.40 361.85 354.55 1 392.31 28 787.69
5 716.40 357.45 358.95 1 751.26 28 428.74


.................................................. .................................................. ................

58 716.40 26.04 690.36 28 773.45 1 406.55
59 716.40 17.46 698.93 29 472.39 707.61
60 716.3987949 8.786260412 707.6125345 30180 -2.17824E-10



For your convenience I've formatted the last row with full decimals.
No need of adjusting any elements to get the exact
balance. Only format cells to two decimals.

Excel is an extraordinary good program, allowing everybody
to deal with complex mathematical problems, but it uses
often algorithms and approximation methods which sometimes
give results slightly different from the ones you can get
with proper math formulas.
I think this is one case.
As from my opinion, the difference laying mainly in that
14.90/12 which is only allowed if 14.90 is a "nominal yearly
interest rate monthly convertible".

Ciao
Bruno

" wrote in message ...

[...]
Then I compute 1.24168311% = RATE(60,716.40,-30180).
If you continue to disagree, please provide the
exact formula or method and numbers that you use
to compute 1.2416768%. I would be very interested
-- no pun intended ;-).

No pun at all, only a little of mathematics.
Given:
Pm = monthly payment
n = terms = 60 months
Im = monthly interst = 0.01246768
C = principal = 30180

Im
Pm = C * ----------------------------- = 716.3987949... = 716.40
1
1 - ( ----------------) ^ n
1 + Im

But I don't have Im, just I must calculate it from the above formula.
I can do this in a lot of ways.
I chose the iteration method so:


Pm 1
Im = ------- * 1 - ( ----------------) ^ n
C 1 + Im

Put Im = your "guess" (say 0.0125) into the second member.
You get Im = 0.012472421

Put again this Im into the second member
You get Im = 0.012454076

Put again this Im into the second member
You get Im = 0.012441771

You see Im is decreasing every iteration, starting from
the 6th figure, the first 5 figures being stable.
Do until you have the first 10 or 15 or what you
want figures stable.

No need to pontificate. You are preaching to the
choir. I was not questioning your methods, merely
your number.

If by "mathematically correct", you mean a monthly
interest rate that reduces the principal to zero
with 60 payments all of which are $716.40, then
RATE(60,716.40,-30180) -- approx 1.24168311% --
is the correct rate, not 1.2416768%. This is
self-evident if you build an amortization table
and dispense with any rounding. With 1.2416768%,
the last payment must be $716.29, whereas with
RATE(60,716.40,-30180), the last payment is
"exactly" $716.40, formatted to 6 decimal places.

I can't continue any longer discussing of mathematics
while you are discussing of Excel formulas.
Here is my amortization table made in Excel sheet
using mathematical, not Excel's, formulas.

A = 30 180.00 Tassi relativi a Durate in periodi
Rate/anno = 12 Anni 1/12 di anno di 1/12 di anno
i(1) = 15.96% 2 0.012416768 24
i(2) = 15.96% 2 0.012416768 24
i(3) = 15.96% 1 0.012416768 12
iv = 6.00% 0.004867551 60
716.40

42 983.93 12 803.93 30 180.00
Rata QI QC DE DR
30 180.00
1 716.40 374.74 341.66 341.66 29 838.34
2 716.40 370.50 345.90 687.56 29 492.44
3 716.40 366.20 350.20 1 037.76 29 142.24
4 716.40 361.85 354.55 1 392.31 28 787.69
5 716.40 357.45 358.95 1 751.26 28 428.74

.................................................. ........

56 716.40 42.87 673.53 27 401.20 2 778.80
57 716.40 34.50 681.90 28 083.09 2 096.91
58 716.40 26.04 690.36 28 773.45 1 406.55
59 716.40 17.46 698.93 29 472.39 707.61
60 716.3987949 8.786260412 707.6125345 30180 -2.17824E-10


For your convenience I've formatted the last row with full decimals.
No need of adjusting any elements to get the exact
balance. Only format cells to two decimals.

Excel is an extraordinary good program, allowing everybody
to deal with complex mathematical problems, but it uses
often algorithms and approximation methods which sometimes
give results slightly different from the ones you can get
with proper math formulas.
I think this is one case.
As from my opinion, the difference laying mainly in that
14.90/12 which is only allowed if 14.90 is a "nominal yearly
interest rate monthly convertible".

Ciao
Bruno

"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.

" wrote
in message ...

It seems to me that's clear enough we both know financial
mathematics to correctly deal with an amortization plan.
May be I don't know enough your language to go deeper end
deeper into the matter.
Then let me summarize the origin of my objection.
It was that 14.90% simply declared as "Annual interest rate"
instead of "Annual nominal interst rate, monthly convertible".
In fact the table was built up using a monthly effective interest rate
of 14.9%/12, which leads to (1+14.9%/12)^12-1 =15.96...%
as annual effective interest rate.

Then that "Annual interest rate = 14.90%" in not correct
or very ambiguous.

(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.)

My HP12C doen't work any more; as a handy calculator I use
its brother HP11C or HP28S.
But I made the calculations on this matter only using Excel
worksheet elementary formulas, not those infamous
Single/Double VBA data types.


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).

I depends mainly to the fact of considering the monthly payment
equal to 716.40 rounded to 2 decimals, or 716.40 as a
format made up 15 decimals.

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

Which correctly leads only to an effective monthly
interest rate of i/12.
And you can't use i/365 to get a daily effective
interest rate.
But you should get (1+i%/12)^12-1 = Iy% = annual
effective unterest rate; from (1+Iy%)=(1+Id%)^365
you get Id% = (1+Iy%)^(1/365)-1 as effective daily
interest rate and 365*Id% as Annual nominal interst rate,
daily convertible.

I think these points are clear enough to both of us.

Ciao
Bruno