Monday, July 2, 2007

EMI, Interest, Amortization Calculator

Loan EMI Calculator

If you want to apply for an loan, this will enable you to calculate your Equated Monthly Installment (EMI ) if you know the loan amount you require, term for which you want to take the loan & the rate of interest that is applicable. You can check the EMIs for different loan tenures to decide which would be most suitable for you.

Though large number of websites are offering EMI calulators which are reliable and easy to use, i thought of quizing around with me and tried to formulate.

The forluma to calculate EMI goes like this.


EMI = (P * I)
[(1 + I)^n] / [(1 + I)^n] - 1


EMI - Equated Monthly Installment
P - Principal amount
I - Fractional Interest rate i.e., Anually interest rate divided by 12
n - Loan tenure in months

Lets take an example,
Rajesh took a loan of 100000 for one year @ 11% A.P

Here,
P = 100000
I = 11/(12*100) = 0.00917
n = 1 year = 12 months

let put the same in our formula

EMI = (100000 * 0.00917) * (1 + 0.0096)^12 / (1 + 0.0096)^12 - 1
= 917 * (1.11576/1.11576 - 1)
= 917 * 9.638
= 8838

So, EMI is 8838

I have also formulated the same in a spreadsheet.


Loan Amortization Calculator

I thought that it would be easy to formulate Amortization, after all this was just a thought !!.

Lets first look at what amortization is ?

Amortization is a means of paying out a predetermined sum (the principal) plus interest that is Equated Monthly Installment (EMI ) over a fixed period of time, so that the principal is completely eliminated by the end of the term.

Let try formulate it,
There are three main components involved
1. Principal Amount (Amount for which you have taken loan for)
2. Interest rate
3. EMI

For one minute if we think that bank has given a loan for 120000 @ 0% for a period of 1 year. thats only a asumption :). Then, it would be trivial if interest weren't involved, since one could simply divide the principal amount into a certain number of payments and be done with it.

The trick is to find the right payment amount, which includes both principal and interest. Now this is reality.

I started deriving the using a simple pocket calculator, math actually is not celestial mechanics, but it probably doesn't come standard on the basic pocket calculator.

This is my derivation of the formula for amortization. The goal is to find a payment amount, x, which pays off the loan principal, P, after a specified number of payments, N. We start with some variable definitions:

P - The principal borrowed
N - The number of payments
i - The fractional (periodic) interest rate
Pj - The principal part of payment j
Ij - The interest part of payment j
x - The regular payment

Assuming that all payments are the same amount, a payment consists of its interest part and its principal part:

x = Ij + Pj
I1 = iP P1 = x − I1
I2 = i(P − P1) P2 = x − I2
I3 = i(P − P1 − P2) P3 = x − I3, etc.

This schedule states that the payment x includes interest on all of the remaining principal, including that which is part of the current payment. The first payment, there- fore, includes an interest payment on the total borrowed, which defines the minimum payment if we are to make any progress toward paying off the loan.

The Pj ’s may be rewritten into a recurrence relation:
P1 = x − iP
P2 = x − i(P − P1)
= x − i [P − (x − iP)]
= x − iP + ix − i^2P
= (x − iP)(1 + i)
P3 = x − i(P − P1 − P2)
= x − i P − (x − iP) − (x − iP + ix − i^2P) = x + 2ix + i^2x − iP − 2i^2P − i^3P
= x(1 + i)^2 − iP(1 + i)^2
= (x − iP)(1 + i)^2

In general, we will find that
Pj = (x − iP)(1 + i)^(j−1).

So, if we have x which is nothing but the EMI we have calculate in the above earlier. now it would not be difficult to seperate out Prinicipal and Interest part the our equated monthly payment.

Lets take the above example of rajesh and create the amortization chart.

Princiapal - x
100000.00
Rate of Intrest per Year 11%
Loan Durataion years 1.00
Rate of Intrest per Month 0.00917
Loan Durataion Months 12.00
EMI 8838.17

so for the first month lets caluclate the Pj and Ij, i.e, P1 and I1

P1 = (8838 - 0.00917*100000)(1+1)^1-1
= 8838 - 917
= 7921

Ij = x - Pj = 917

Smilarly we can calculate for all the months. monthwise amortization is given below

Month EMI Prinicipal Interest
1 8838.17 7921.50 916.67
2 8838.17 7994.11 844.05
3 8838.17 8067.39 770.77
4 8838.17 8141.34 696.82
5 8838.17 8215.97 622.19
6 8838.17 8291.29 546.88
7 8838.17 8367.29 470.88
8 8838.17 8443.99 394.18
9 8838.17 8521.39 316.77
10 8838.17 8599.51 238.66
11 8838.17 8678.33 159.83
12 8838.17 8757.89 80.28
13 8838.17 8838.17 0.00



4 comments:

Ajay said...

There is an opening in ICICI Bank - Loan Department. :)

But something which is very confusing in other way is put in clear n easy to understand way.

rajesh said...

Thanx for this Post

Sanket said...

Good work dude.. That's correct formula for finding out amortization.

If one knows formula for EMI, finding out amortization schedule is simple job using excel. You can also put one more parameter for prepayment of principle amount and find out how much you can save using prepayment.

Realmoney said...

This post is awesome..i've been reading tons of crap posts from other blogs, but shows you have a more educated reader base.
amortization

Click here to know more