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

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

"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

"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