Team-BHP - Calculating EMIs

Team-BHP (https://www.team-bhp.com/forum/)

- The Indian Car Scene (https://www.team-bhp.com/forum/indian-car-scene/)

- - Calculating EMIs (https://www.team-bhp.com/forum/indian-car-scene/9889-calculating-emis.html)

Note from the Admin : Thanks to BHPians "Jat" and "SLK" for sharing their EMI Calculators. The excel sheets are attached herewith. The effort is much appreciated.

I hope people are going to find this piece of information useful.

Installment calculation.
The formula used to calculate the EMIs for you car loan is as follows:
EMI = (m*L/12)*((i/m)*(1+i/m)^(m*n)/((1+i/m)^(m*n)-1))
where
m = multiplier for intervals at which interest are calculated eg =1 for annual, 4 for quarterly and 12 for monthly etc.
i = annual rate of interest in decimals eg 10% = 0.01
L = Loan amount or principal
n = number of years

If the installments are not equals and also the interest are going to vary then you can tabulate the entire calculation with an example:
Suppose a person has taken a loan of Rs 100,000/- on a floating rate of interest which is going to vary and he is not paying any EMI but amount as per his capacity in each month.
Loan is paid back on advance installments terms and first month he has paid Rs 10,000/- and int rate was 12% and 2nd month Rs 15,000/- and int rate was 12% and 3rd month again Rs 10,000/- at the interest at 10% and 4th month was Rs 20,000 @ 9%

So the calculation is as follows:

Create a table with following headings:

SNo. [L(Loan amnt)] [i(int rate)] [I(interest)] [Amt(Installment)] [RB(Bal to reduce)] [LB(Balance loan)]where I=i/1200*L and RB=Amt-I and LB=L-RB

1. [100,000] [12%] [= 0 (adv payment)] [10,000] [=10,000-0 =10000] [90,000]

2. [90,000] [12%] [=12/12/100*90000=900] [15,000] [=15,000-900=14,100] [75,900]

3. [75,900] [10%] [=632.50] [10,000] [=9367.50] [66532.50]

4. [66532.50] [9%] [=499.00] [20,000] [=19,501.00] [47,031.50]

and so on.......
Please note the last value LB of 1. has become the first value L of 2. and continues like that.

If it was balance payments terms then the interest in SNo 1. will be calculated and will be = Rs 1000/- which will be deducted from your First Installment for calculating the balnce loan amount. You can use this format for EMI also and vary the different values and can see the result.

It is a very tedious calculation but if you have some spreadsheet software which can do basic arithmetic calculation in formulae, then it would become very much easy.

There are many built in formulae in Excel spreadsheet for loan calculation. Even MS Works 7.0 has a built in car loan calculator.

Good job..!!! Hope it will be of great assistance for car buyers in the forum..

Thanks for sharing this, Jat. If you can email me an excel sheet on rush@team-bhp.com, I will upload it to the forum for easy download.

GTO

There is a standard mathematical function available in MS Excel (spreadsheet), called PMT. PMT(rate,installment in a year, loan amount). This will calculate your EMI. For detailed instructions , use HELP in MS Excel.

Or, just go to EMI Calculator on http://scrailway.gov.in/scroa/loan/emi.html...

Well both the above mentioned calculators do not consider the advance installment paid at the very begening of the loan. This installment does not include any interest. So if you compare the amortisation schedules generated from these calculators with the bank's schedule you would always have a difference.

I had made an Excel sheet about 6 years back... though it returns perfect results, I don't think its in presentable format. I'll see if I can get it in a user friendly format!

good job!! keep posting similar things

Note from the Admin : Thanks to BHPians "Jat" and "SLK" for sharing their EMI Calculators. The excel sheets are attached in the opening post. The effort is much appreciated.

GTO

Good job guys.

Does interest rate vary much quarterly or so? Like Jan its 11% , June its 10.5% or so?

thanks a lot guys, i'am planing to go for the verna cdri with abs, any idea which bank offers the best car loan???

very useful information.thanks for sharing it.

Very useful excel sheet. Thanks a ton for sharing it.

The EMI calculation is inbuit in the MS Excel ( 2003 , 2007 )lol:

No need to put any formula's

Just follow these steps

Start Excel

1) File -> New -> Templates on my computer -> Spreadsheet solutions

2) Select and open Loan amortization solution

3) Enter the desired values

4) Get the result

@ JAT/SLK: Thank you guys for sharing these files, especially the one shared by SLK. Simply superb, great formatting! It really can't get any easier to work around for such calculations. :thumbs up