Go Back   Team-BHP > BHP India > The Indian Car Scene


Reply
 
Thread Tools Search this Thread
Old 15th January 2006, 03:05   #1
jat
BHPian
 
Join Date: Dec 2005
Location: SINGAPORE
Posts: 265
Thanked: 3 Times
Default Calculating EMIs

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.
Attached Files
File Type: xls Jat - EMI Calculator.xls (48.5 KB, 3235 views)
File Type: xls SLK - EMI Calculator.xls (232.0 KB, 14883 views)
jat is offline   Reply With Quote
Old 15th January 2006, 11:02   #2
Senior - BHPian
 
kpzen's Avatar
 
Join Date: Oct 2004
Location: Faridabad
Posts: 5,545
Thanked: 1,598 Times
Default

Good job..!!! Hope it will be of great assistance for car buyers in the forum..
kpzen is online now   Reply With Quote
Old 16th January 2006, 12:26   #3
GTO
Team-BHP Support
 
GTO's Avatar
 
Join Date: Feb 2004
Location: Bombay
Posts: 47,714
Thanked: 88,945 Times
Default

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
GTO is offline   Reply With Quote
Old 16th January 2006, 14:42   #4
BHPian
 
vipulG's Avatar
 
Join Date: Oct 2004
Location: Surat
Posts: 32
Thanked: 0 Times
Default

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.
vipulG is offline   Reply With Quote
Old 16th January 2006, 19:30   #5
BHPian
 
msprabhakar's Avatar
 
Join Date: Jun 2005
Location: Mumbai
Posts: 264
Thanked: 73 Times
Default

Or, just go to EMI Calculator on http://scrailway.gov.in/scroa/loan/emi.html...
msprabhakar is offline   Reply With Quote
Old 16th January 2006, 23:47   #6
jat
BHPian
 
Join Date: Dec 2005
Location: SINGAPORE
Posts: 265
Thanked: 3 Times
Default

Or this site

http://www.cybersteering.com/cbmain/finance/finwiz.html

RK
jat is offline   Reply With Quote
Old 17th January 2006, 02:01   #7
SLK
Senior - BHPian
 
SLK's Avatar
 
Join Date: Feb 2004
Location: DL XX XX XXXX
Posts: 1,290
Thanked: 185 Times
Default

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!
SLK is offline   Reply With Quote
Old 17th January 2006, 08:25   #8
BHPian
 
pratheesh's Avatar
 
Join Date: Dec 2005
Location: Omnipresent
Posts: 49
Thanked: 0 Times
Default

good job!! keep posting similar things
pratheesh is offline   Reply With Quote
Old 17th January 2006, 14:48   #9
GTO
Team-BHP Support
 
GTO's Avatar
 
Join Date: Feb 2004
Location: Bombay
Posts: 47,714
Thanked: 88,945 Times
Default

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
GTO is offline   Reply With Quote
Old 5th February 2008, 23:09   #10
Distinguished - BHPian
 
jkdas's Avatar
 
Join Date: Sep 2005
Location: Thiruvananthapu
Posts: 9,661
Thanked: 1,389 Times
Default

Good job guys.

Does interest rate vary much quarterly or so? Like Jan its 11% , June its 10.5% or so?
jkdas is offline   Reply With Quote
Old 5th February 2008, 23:15   #11
BHPian
 
sajands's Avatar
 
Join Date: Mar 2005
Location: Bangalore
Posts: 830
Thanked: 135 Times
Default

thanks a lot guys, i'am planing to go for the verna cdri with abs, any idea which bank offers the best car loan???
sajands is offline   Reply With Quote
Old 5th February 2008, 23:38   #12
BHPian
 
ankit.jhamb's Avatar
 
Join Date: Jan 2007
Location: new delhi
Posts: 574
Thanked: 0 Times
Default

very useful information.thanks for sharing it.
ankit.jhamb is offline   Reply With Quote
Old 6th February 2008, 12:58   #13
BHPian
 
neeraj_infopark's Avatar
 
Join Date: Nov 2007
Location: Noida
Posts: 35
Thanked: 0 Times
Default

Very useful excel sheet. Thanks a ton for sharing it.
neeraj_infopark is offline   Reply With Quote
Old 7th February 2008, 11:16   #14
BHPian
 
hrishig's Avatar
 
Join Date: Apr 2007
Location: Pune
Posts: 372
Thanked: 12 Times
Default

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

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
hrishig is offline   Reply With Quote
Old 7th February 2008, 14:40   #15
BHPian
 
nkapoor777's Avatar
 
Join Date: Nov 2006
Location: New Delhi
Posts: 583
Thanked: 87 Times
Thumbs up

@ 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.
nkapoor777 is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads
Thread Thread Starter Forum Replies Last Post
Life without EMIs or why I remapped my Ford Figo 1.4L Diesel (Code 6) batterylow Modifications & Accessories 11 13th October 2016 21:31
Started paying EMIs, but car not delivered yet! garyjacob Indian Car Dealerships 60 7th December 2014 16:06
Indicative EMIs - Clarification Pls! Bluu Motorbikes 4 12th April 2008 20:08
Standard guidelines for calculating FE? salilpawar1 The Indian Car Scene 7 9th February 2008 12:42
Calculating Sub Wattage vebmetal In-Car Entertainment 6 14th November 2007 12:52


All times are GMT +5.5. The time now is 09:42.

Copyright 2000 - 2017, Team-BHP.com
Proudly powered by E2E Networks