Fv & Emi!

[via135]

i need a formula to get the result for the following:

i am getting a loan of Rs10000/- @ 10% pa with monthly rests and the
loan is payable in 18 months (15 months for principal and 3 months for
interest portion). can i have a formula to find out the EMI for the
loan together with the intt as well as the FV of my loan? hlp pl..??!!

i give below the meanings for my terminology!

1) "/" nothing but decimal separator "." and "-" means "no decimal"

2) "monthly rests" means interest being applied for monthly products
ie, month end balance.
3) interest is applied on "simple interest basis" and not on
"compounding effect". ie, interest is applied for the month end balance
of principal amount and the interest amount is transferred to another
account for which no interest is applied. that's why the repayment is
being separately fixed for principal & interest.

4) EMI means Equated Monthly Instalments..?


-via135

 

[joeu2004]

can i have a formula to find out the EMI for the
loan together with the intt as well as the FV of my loan?

Good explanation, this time. But either I do not understand
something, or something else is amiss.

First, I assume that "10% pa" means 10% per annum. But
is that the nominal rate (i.e, 10%/12 monthly), or is it the
effective annual rate -- that is, the monthly rate is
RATE(12,,-1,1+10%) = (1+10%)^(1/12) - 1?

I assume it is the nominal rate. But the bigger question is ....

You said that the 10000 rupee principal is paid off in 15
months, and the simple interest on the declining balance is
accumulated into another account, which is subsequently paid
off in 3 additional months (interest-free!). You are looking for
equal monthly payments that pays off both. Right?

Based on that description, I model the accumulated interest
as follows (i is the period interest rate, 10%/12 in your case,
and "pmt" is the monthly payment, what you call EMI):

Period 1: 10000 * i
Period 2: (10000 - pmt) * i
Period 3: (10000 - 2*pmt) * i
.....
Period 15: (10000 - 14*pmt) * i

Is that what you intended?

If so, the formula for the accumulated interest is (n is the
number of periods, 15 in your case):

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

In order to pay off the 10000 principal alone in 15 months,
the periodic payment __must__ be 666.67 = 10000 / 15.

But there is no a priori guarantee that that periodic payment
would pay off the accumulated interest in 3 months -- unless
the interest rate had been determined based on that requirement.

In fact, with the terms of the loan you stated -- principal of
10000, 10% nominal annual rate, 15 payments -- the
accumulated interest is mathematically equal to __one__
payment, 666.67, not taking rounding into account. So the
accumulated interest could be paid off in __one__ additional
equal payment, not 3. Did you misstate the terms of the loan?

That makes more sense to me, from a business perspective.
It does not make sense that a lender would permit you to
accumulate interest and pay it off over time without charging
interest on the remaining balance of unpaid interest. It __does__
make some sense that a lender might structure a loan such
that you pay off the principal first, then you pay off the
accumulated interest in one more equal payment. The lender
loses the cost of capital (interest) on the accumulated interest
for one period. But it is simple for both the lender and borrower
to understand.

Thus, from the lender's perspective, the problem becomes:
determine the number of periods, n, or the periodic interest
rate, i, to pay off a loan in n periods plus the accumulated
interest in k more periods. Interestingly, the loan amount does
not matter.

i = 2 * k / n / (n+1)

n = SQRT((1 + 8*k) / 4 / i) - 0.5

Note: There might a simpler way to approximate n that
eludes me at the moment.

The periodic payment (pmt, emi) is simply the loan amount
divided by the number of periods, n.

pmt = prin / n ("prin" is the loan amount)

int = pmt * i * n * (n+1) / 2 ("int" is accumulated interest)

Of course, all of this is predicated on my understanding of
the method for computing accumulated interested above.
If that is wrong, all of the above is probably wrong. GIGO.

Please let me know if I have any misunderstandings. If not,
let me know if you need further explanation or translation
into Excel formulas. And please confirm any misstatement
of the terms of the loan.


-----

 

[via135]

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

-via135

 

[joeu2004]

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]

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

 

[joeu2004]

is there any worksheet function to represent your
formula as well as other financial calculations?

None that I am aware of for the formulas that I developed
for this loan structure. As for "other financial calculations",
there is a plethora of formulas including FV(), PMT(), NPER(),
and RATE(). Use Excel Help to learn about those functions
and others.

 

[via135]

thks JOEU!
regds!

-via135

None that I am aware of for the formulas that I developed
for this loan structure. As for "other financial calculations",
there is a plethora of formulas including FV(), PMT(), NPER(),
and RATE(). Use Excel Help to learn about those functions
and others.