hi JOEU!

1) i am cleared now that i can't expect the same "pmt" for the entire
loan period of 18m since the accumulated interest is based on the
"pmt"

2) yes..it is 10%/12

3) i agree that the "pmt" for the first 15m will be 666.67 & for the
remaining
3m it will be 222.22

4) yes..the FV=principal+acc intt ie, 10000+666.67

i have been cleared 101%

ok..for acadamic intt...is there any worksheet function to represent
your formula as well as other financial calculations?

thks for your prompt followup!

-via135



Wrote:
"via135" wrote:
hi JOEU! you are 99.99% on my track!
don't look at the loan at business angle. in fact the loan
is being disbursed by a company to it's employees just
as a concession without any business motive. that's why
the loan is being charged with simple interest for the
principal only.

Okay. In that case, did I answer your questions?

If the loan of 10000 is paid off in 15 installments, where
each payment is applied entirely to the principal, the
monthly payment is necessarily 666.67 = 10000 / 15.

You asked for equal payments that would first pay off the
loan, then pay off the accumulated interest in 3 additional
installments. I explained that the accumulated interest is
666.67 = 666.67 * (10%/12) * 15 * (15+1) / 2.

Since the accumulated interest equals the monthly payment,
it will take only 1 "equal monthly installment", not 3, to pay
off the accumulated interest.

Based on the terms of the loan, it is simply not possible to
compute a single monthly payment amount that pays off
the principal of 10000 in 15 periods and the accumulated
interest in 3 periods.

But all of that is predicated on my interpretation of the
terms and methodology of the loan. To confirm that, I
asked several questions, which you have not answered.
Please do so.

1. (New question) When you asked for "equal monthly
installments", I assumed you are looking for the same
monthly installment to pay off both principal and interest
in the specified respective time periods.

But now I wonder: did you really want to know what the
monthly payment would be to pay off the accumulated
interest in 3 periods, allowing that monthly payment to be
different from the monthly payment to pay off the principal
in 15 periods?

If that is the case, I should have helped you find that
answer by providing the formula above to compute the
total accumulated interest. The 3 "interest installments"
would simply be 222.22 (approximately) = 666.67 / 3.
Do you agree?

2. Is the "10% pa" interest the __nominal__ annual rate?
That is, is the monthly rate 10%/12?

3. Do you agree that the balance is reduced according to
the following schedule, confirming my formulation, where
"pmt" is the monthly payment, what you call "emi":

initialBbalance = 10000
Period 1: balance = previousBalance - pmt
Period 2: balance = previousBalance - pmt
....
Period 15: 0 = previousBalance - pmt

4. Assuming #2 is correct, do you agree that interest
accumulates according to the following schedule,
confirming my formulation, where "i" is the monthly
interest rate:

Period 1: int = previousBalance * i
Period 2: int = previousInt + (previousBalance * i)
....
Period 15: int = previousInt + (previousBalance * i)

Note that the interest for the period is based on the
previous balance, before the payment for the period.
Do you agree?

Also note that in each period k, "previousBalance" is the
same as initialBalance - (k-1)*pmt, which is what I wrote
previously.

Finally, you asked for the "FV". That is simply the principal
plus accumulated interest. If you agree with all of the above,
I had noted earlier that the accumulated interest (int) is:

int = pmt * i * n * (n+1) / 2

where "pmt" is the monthly payment (of the principal), "i" is
the monthly interest rate, and "n" is the number of payments
to pay off the principal (15).

Is everything clear? Are all your questions answered?


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=500250