| || ||Thread Tools||Search this Thread|
|15th January 2006, 03:05||#1|
Join Date: Dec 2005
Thanked: 3 Times
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.
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))
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.
|16th January 2006, 14:42||#4|
Join Date: Oct 2004
Thanked: 0 Times
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.
|17th January 2006, 02:01||#7|
Senior - BHPian
Join Date: Feb 2004
Location: DL XX XX XXXX
Thanked: 190 Times
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!
|17th January 2006, 14:48||#9|
Join Date: Feb 2004
Thanked: 99,961 Times
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.
|7th February 2008, 11:16||#14|
Join Date: Apr 2007
Thanked: 13 Times
The EMI calculation is inbuit in the MS Excel ( 2003 , 2007 )
No need to put any formula's
Just follow these steps
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
|7th February 2008, 14:40||#15|
Join Date: Nov 2006
Location: New Delhi
Thanked: 91 Times
@ 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.
|Thread Tools||Search this Thread|
|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||15||12th March 2018 16:59|
|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|