I am a Medical Transcriptionist and Freelance Web Designer. I run the Freelance Web Designing Company, eProfessionals, which currently maintains and hosts more than 20 professional and personal blogs. ... 
|
Jun
21
|
Being associated with bank for short-term, I often get this question from friends and relatives. How to calculate EMI? When you apply for loan, they will just tell you the EMI, but does not tell you how they are calculated unless you ask them specifically to show how they calculated it. EMI table is also called as amortization table, which shows the balance at each installment.
EMI or equated monthly installments is the most popular form of loan payment. It is a fixed amount of repayment made every month towards the loan, which includes payment towards both principal and interest.
It is easy to prepare an EMI table in Excel sheet. There is a financial function called PMT to calculate the EMI.
PMT(rate,nper,pv)
rate – Interest rate for the loan.
nper – Total number of payments for the loan.
pv – Present value/principal.
Prepare excel sheet with the following data and enter the PMT function in the cell “C7.”
- Rate = C6/C5, which divides rate of interest by number of months thus giving rate of interest per month.
- Nper = C4*C5, which gives total number of months we need to pay equated installment.
- Do not forget to take the loan amount in minus value, so that our EMI table shows positive values.
Now prepare table with Serial Number of Payment, Monthly Installment, Interest Amount, Principal Amount, and Balance. Take the first row with serial number “0” and in the balance amount point the cell to “Loan Amount” – type “=C3” Next Enter the serial number as 1. Monthly installments will remain same so take absolute cell reference (by adding $) which will not change when you drag the cells to auto increment. In “Monthly Installment” column, enter “=$C$7“. In the “Interest Amount” column, enter “=F10*$C$6/$C$5” which means we are dividing the balance amount at the end of the month by monthly interest rate (rate of interest/payments per year). As the rate of interest and yearly payments are not going to change, we used absolute reference using $ sign. In “Principal Amount” column type “=C11-D11” which deducts the monthly interest from monthly EMI and whatever the balance available shows as payment towards the principal amount. In “Balance” column enter,” =F10-E11“, which means we are deducting the principal amount in the EMI to ascertain the original balance at the end of the month upon which we are going to calculate interest for the next month. Now select the row and drag it until balance becomes “0”.
That’s all! We made our own EMI calculator in Excel. This Excel EMI calculator is created for a tenure of 20 years. If you are just calculating for 5 years, i.e., 60 months, 61st month shows values red colored and in minus range. So you need to take up to “0” in the balance column. Ignore the values in RED. If you want to calculate for more years, then just select the last row and drag it to extend it.
Download the Excel EMI Calculator.
Update – Oct 16, 2009: Mr. Parixit Namdhar, one of my blog readers, suggested that instead of entering the formula “=$C$7” in column “C” entering “=IF($B11>($C$4*$C$5),0,$C$7)” solves the minus values problem. This formula makes the minus values zero making it easily understandable and looks neat. Thanks a lot Parixit!
Related Posts:
- Savings account interest rate – Now calculated on a daily basis After making usage of any bank ATM free, RBI now...
Dear Naveen
Your EMI calculator is really helpful.
Hi Naveen, I found your EMI calculator on the web and found it very useful.
Thank you.
-ravi
How to get monthly installment if you have only. Period & loan amount & interest rate…….? if you dont have PMT formula.
yes..absolutely correct… i think, that is the system of EMI ie: payment in installments which is accepted by the whole..
naveen G
ur software saved my lot of valueable time.
thanks for this social service!
I had been looking for the method how to calculate emi in excel for a long time. Thank you very much that you have not put your password in the coloums.
dear mr naveen pls send excel spread sheet for calculate home loans
It is same like a personal loan….just enter the loan amount, rate, and tenure. That’s all.
Hi Navin
The xl sheet was cool… However when we have the interest rates change frquently how do we calculate the interest on the daily balance method ?
Dear Mr Navin,
Many many thanks for your efforts. all the best. thanks.
good you have done good effort
Thanks for taking time to appreciate my work.
Good effort. It was very useful
Its really very gud and useful.
Thanks for the EMI Calculator, But do you have any Excel sheet to calculate the Actual Area of Flat by adding different rooms.
Hello Roy! If you can explain me your requirements, I will search, learn, experiment, and come up with an excel sheet which can automate your requirements.
Naveen, great tool yar. Very helpful to know the balance
. Thanks for sharing.
This calculator is really very helpful. thanx a ton!!
Is this is the standard one, used by all banks?
Hi Naveen,
Please let me know how could i recalculate my EMI ,if I pay 20 % of the principal amount after 12 months .
Excellant. Thanks a lot. Can you please also provide us saving or fixed deposite calculators..
Thanks for appreciation. Sure…I will provide all useful calculators in a single Excel sheet when I get free time.
many thanks Naveen ji,
Its really easy and useful.
thanks again
First of all thanks for this excellent sheet. However I also would like to know how to calculate the same for a diminishing loan……I dont know exactly what they (bankers) mean by diminishing loan but if you can help me on that, it would be great!
thanks in advance!!!
Wonderfull
Appriciated it had helped me solving my Problems regarding
If you could also provide some info on Schedules and Variable Rate of Interest to Parameters like
EFS
EPI
PMI
PPF
IOA
IPI
Which will still helpfull
Keep up the Good Work
Regards
Sameer
Dear Naveen,
Is there any formula where there is moratorium period (Late commencement of EMI) is there.
Else the excel sheet is very useful.
Regards,
hi naveen
ur EMI calci really saves a lots of time and more comprehensive.
Dear neevem
we are looking for such a calculator from long time
its realy a helpful
I am glad you find it helpful.
required excell EMI calculator
I think you haven’t noticed the link. It is at the bottom.
thanks for your service that to free.
if you need my help in indore do mail me
thanks
cr deshmukh
Thanks for your kind comments.
Is any facility avail to insert EMI calculator to our blogs?
Just google the words “EMI calculator widget” and you will find some.
your calculator is very useful to me… thank you very much
Hi Naveen,
Thanks for sharing the information about how to calculate EMI.
Its really very helpful to know the remaining principal amount after each EMI.
hey this is reaaly good thing which is very helpful so that no banker can make fool of a consumer if this kind of things are easily avialable to a common people ……
keep it up ………..
Hi Naveen, this is very nice and helpful… 1 suggestion… way not put another row after Row 7. This should have the total amount that you will be paying. That is C7*C5*C5.
This will give a quick complete information and will help people decide better…
Thanks for your suggestion.
Hi Naveen,
Very Useful calculation, looking for since long.
Please provide if possible emi calculation on reducing balance.
Regards
can someon tell me how to calculate EMI on simple calculator? Esp for longer term like 72 to 84 months. Once my friend did it on simple calculator, the next time he did it on his mind….was amazing. am sure there should be a simpler way to break down values
Dear Naveen
Really useful work. Thank u for sharing. I was looking for such calculators for fixed deposits and monthly recurring deposits where compound interest is calculated either monthly or quarterly basis.best wishes
with regards
satheesh
Dear sir,
I am really grateful from your note. I really appreciate this kind of simple way to make people understand things. I would really like to be a part of your team
thanks,
Pradip Layek
Realy your excel calculator is realy helpful
Thanx
Dear Naveen,
The information you have given is really helpful.
Thanks, God bless you.
Yasir
Thank you very much naveen thanks a lot
Dear Naveen,
The information you have given is really helpful.
Thanks, God bless you.
Sijo Thomson
Dude…
Thanks a lot for your emi calculator….
Best wishes.
Prasanth.
Hey,
Thanks a lot Buddy, you have saved my time. Keep up with your social service.
Thanks once again. God bless you:)
Ganesh
It is very informative tool in finance & Accounts
Sir
Example
Loan amount 15000
Monthly Pay 1000 and 12% intrest
I want to statement reduce to Monthly installment and intrest
thanks a lot – it is really helpful to all.
Thanks alot. It is really helpful. It helped me in understanding saving I will make by switching bank
Hi,
Thanks for EMI calculator. It was very useful. Can you advise how EMI is calculated when we make part payment and when we try to fore-closure (when they ask for 2% surcharge)
i have input data like loan amount, rate of interest, monthly installment amount. How to calculate loan term?
Naveen,
Excellent, Please provide us some more useful tools like this. Is there way to arrive at Period, Loan amount and pre-closure period to gain maximum advantage!!!
Thanks
nice thnx
i am bank employee
hai naveen.
U r EMI calculator is very helped me a lot and saved my time.
I need some suggestions regarding experimenting with EXCEL sheets.
dear sir,
great ,really helpful for me.thanks a lot.
Hi Naveen,
Great software…….. helped me a lot in getting bifurcated details on the loan payment.
Thanks a lot again…….
Kind Regards,
Vikas
Hi Naveen,
Thanks. found it very useful.
regards,
vinita
Thanks a lot Navin for this wonderful emi calc.
I have a question how to calculate EMI for loan rates esp. car loans where we have differential rates. for eg. 8%(1st yr), 10%(2nd yr), 10%(3rd yr) and RBI rate from fourth year. In that case which values to change?
Mr.naveen
Thank you very much for your effort in making the HL widgets and spread sheets ,aking our life easy.
How to use it when frequently changing ROI? pls clarify.
Thanks Naveen for ur EMI calculator…… very helpful for us
Hi Naveen,
First of all let me thank you very much for the valuable calculator.
I have taken loan for 20 yrs for X amount @ 10%.
After 3 years the rate of interest has been reduced to 8%, how do I know my Principal and Interest with this new rate of interest.
Waiting for your valuable reply.
Regards,
Bhaskar.
Halllo Naveen,
This is very useful , thanks for sharing this information ,
Appreciate your good work …
Thanks
Nidhil
hi,
Dear Mr Navin,
its great, i mean ur emi calculation sheet , is wonderful.
can u incorporate one more column, so that impact of additional repayments or monthly some lumpsump payment can be shown on, in excel sheet
thanks
amit
Dear Mr. Navin,
Its great, its very helpful for me.
Thanks,
Dhirendra Singh
Hi Naveen,
Thanks a lot for ur gr8 work.
I had the same calculator which i used to use online.
But yesterday, I was trying to do it offline at home.
I stuck somewhere.
I got the answer.
Thanks a lot.
Keep on providing such work for offline use.
thank you very much Naveen,! helpful to all
HI Naveen,
This is good work. Can someone post a similar worksheet but accounting for variable rate of interest. So, now the worksheet should have one more column called “Rate of Interest” and could change from month to month.
Thanks much!
Naveen,
Gr8 job !!!
however, can you come-up with EMI calculator for floating interest rate?
the current EMI calculator provides EMI only for fixed rates.
Thanks,
Prasad
Naveen,
Wonderful piece of work done here. I was searching for something like this before I start my work.
I will add on Variable Interest rates, Multi Disbursement (for new houses) and once done will post it here with a link
Thanks Again
It’s a wonderful calculator. Helped me understand the EMI concept nicely.
Thanks you very much Naveen
The calculator is useful but m looking for one which allows me to enter principal prepayment anywhere between the tenure and recalculates the balance with reduced tenure.
if it can be down lode on mobile then its a best facility for us
THE EMI FORMULA IS PERFECT.
Hey Naveen,
Your EMI calculator is very useful.
But this calculator is as per current bank calculations ?
Dear Naveen
Excellent!
Your EMI calculator is really helpful. Keep on providing if anything is new. This is really a social service! All the best for all of your future efforts. God bless you:)
Regards.
if one installment is paid at the time of disbursement then by this formula EMI get wrong on monthly reducing .
Gr8 job, which you have done. But I was wondering that how to use this Excel file to find out the emi for banks like sbi who keep 8% interest rate for the first year and 9.5% or so for the remaining years.
What i mean to say is, how to calculate the EMI for two different rate of interests where we know the duration of both the rates in months
i am working releted to banks.i need to 3 type of emi caculater. how to caculet rate of intrest, how to caculet loan amt., how to caculet IRR,
Sir, How to calculate in case of pre-closure loan – principal balance and interest saved pre-closure.
Thanx Naveen,its a very useful tool.
But I was wondering that how to use this Excel file to find out the emi for banks like HDFC who are giving me the per lac emi for 3yrs, 4yrs and 5 yrs. Also i want to know abt sbi who keep 8% interest rate for the first year and 10.5% or so for the remaining years.
same doubt where Hansraj quoted few days before.
What i mean to say is, how to calculate the EMI for two different rate of interests where we know the duration of both the rates in months
hello,
I want to download the emi calculator, plz advise
Haven’t you noticed the links at bottom to download the Excel file?
hi…Naveen…hey thanks alot yaar…it is very useful tool …
good effort
Dear Naveen,
Found u r excel sheet very interesting,
Pl update the excel sheet with the impact of extra lumpsum amount which is paid occasionally by us.
if updated pl send one copy to gholap.amol@yahoo.co.in
hi naveen,
ur excel was great, it helped me to identify more info about my loan.
i have a small request, if possiable let me know how to calculate the flat roi from dimni roi.
thanks
john
Thanks for EMI calculator. It was very useful. Can you advise how EMI is calculated when we make part payment and when we try to fore-closure
sir,
I have availed education loan for 1.5lac and I am paying interest and part of principal of Rs.5000 on 25th every month. The rate of interest is 10.5%. In the moratorium period- i.e study period they charge simple interest on the outstanding-if you pay interest every month-
How to calculate interest in this case
it’s a great stuff naveen . however, if someone wants to know what actually PMT is then here is a reference:
E= P x r x (1 + r)n / ((1+r)n -1)
Here p=principal amount
r = interesr rate per month(ex: if interest rate per annum is 10% then 10/(12*100))
n= tenure in months
Thanks Parijat!
I have been getting a lot of questions with various options like rate change in between and floating rates. If you or any other members of my blog can solve these questions, please post it in comments.
Hi Naveen!
Your software is really helpful. Thank you so much!
hi naveen … this great job…..
pls make some where a box which shows total amount of interest ….paid in total period… thanks….
Dear Sir
Seen the EMI calculator. It will be more usefull if it is slightly modified to calculate the EMI & interest those who are availaing SBI loan. It is like they are charging 9.5% compound ineterst. Moreover inaddition to the monthly EMI you can pay addtional amount whatever you want to reduce the principal amount. One more thing I have noticed interest amount varies depending upon the no of days for different months. Fo example for feb if the int amount is 1000 then for march it becomes nearly 1500. Please let me know if the software is modified as i want to know the procedure they are following. Waiting for your reply.
With Regards
Prasenjit Das
Kolkata
Good sheet , really helpful . If someone wants to pre payment , than how to calculate his previous & after pre payment amount statement.
Thanks for making this available to us Naveen. cheers.
Hi
I had availed a home loan for 15 lk on a self constructed house. During the construction phase itself I paid back close to 1 lk. I understand that this will be categorized as pre-emi and the entire amt would be offset againt interest.
My doubt is , how should I modify the xls (simply great one !!) to include this adjustment ?
Thanks
Thanx a lot Naveen…. your calulators have really helped me and also have learnt quite a bit. Thank you.
hello sir, i have required a emi calculator, but not simple as presence in market. suppose that i have get 700000 Rs. loan . first year interest is 8% and second $ third year will be 8.5%. then how can i calculate emi for 3 year .i have required that type of calculator so please send me email
Unfortunately, I don’t know more about this type of calculation. Still, I will find out how they care calculated and come up with a solution for that when I get free time.
Thanks for your comment.
dear naveen, good work. one thing i want to know is i’ve opted for car loan. int rates are 8% – I year, 10% – II,III year, 11.25% from IV th year onwards. i’ve took the loan for 7 years. can u pls help me to calculate the EMI.
THANX BUDDDY…………..:)
iT REALLY WORK NICE
Your EMI calculator was of great help in understanding the segregation of principal and interest. thanks for posting it and explaining in detail how the sheet works. Thanks once again.
Dear Naveen,
With floating rates coming in, this calculator has to be dynamic for changing interest rates. So in the EMI Calculator there should be column for interest rate % in the Amortization schedule and not just a fixed interest rate on top.
Please add a column, Interest % after the principal as Column F and calculate the interest for the month based on the rate against entered. By default you can keep the Initial loan interest rate to all months. Let the user change the rate whenever to know the impact of the rate increase or decrease.
Good idea. If you can update that xls and send a link to the file via contact form would be appreciated. Thanks for the tip.
hi,
thnx a lot for the speed u gave to my exercise.. pls also let me no how interest will be calculated if there is a moratorium period
Hi,
This is in response to query for a EMI calculator for floating interest.As per my knowledge, even though the Rate of Interest changes the EMI(“Equated” Monthly Installments) remain same, the change is only in the number of installments(i.e the period for repayment decreases/increases).This is useful so that the debtor knows how much he is expected to pay every month.We can ask the lender to change our EMI but that’s not a regular activity.Please correct me if I am wrong.
my loan amount is Rs.950000/-, rate of intrest 8.5% instalment fixed @12000/per month,first month 2 instalment deducted (Rs.12000+12000),loan period 10 years.please send me up to 0 balance calculation with deduction of intrest and principal amount. Thanks in hope to do
Nice formate
thanks mr.naveen its very useful
ur emi calculator is very fast. however, we want to make repayment period in months and not in year which the system does not allow. also prepayment option is also required. kindly help.
hi,
gr8 job,it is very usefull.
Hi Navin,
Your EMI calculator was of great help in understanding the segregation of principal and interest when EMI is to be started immediately.
I have required a emi calculator, to calculate details in following synario.
Suppose i have got 5,00,000 Rs. loan. first year interest is 8% and second and third year will be 8.5%.
then how can i calculate interest per month as i got moratorium period for 18 months.
Then till 18 months I will have to pay interest only and from 19 month full EMI and interest will be paid. I want to know the calculation for the same.
I have required that type of calculator so please send me email.
I also want to know in EMI loan calculator V2 what is “Payments per year” means for?
Upen.
Keep the 12 in “Payments per year.” As the formula has number of months in it, I just created that field fer better understanding of how values are taken in the formula.
Regarding your query, I am not sure about the calculation for floating rates. Lot of people have been asking me as few banks are offering loans at different interest rates in different years. I will post an updated calculator once I get time to meet the loan people in bank and see how the amortization chart is prepared.
Thanks Naveen,
Is it possible to you at least how to calculate interest from 1st month to 6 months for the moratorium period 18 months and 8 % interest rate and 5, 00, 000 loan amount. Because till 18 months I will be paying only interest and full emi will be from 19 month.
Upen
hi Naveen,
great job.. its very helpful..
Thanks a lot..
Thanks Naven,
I want “loan Transfer Emi Calulater”. means, Currently, many bank offers different interest Rate. so i want to transfer my home loan to another bank. but i am confuse about my profit or loss. if you have this type of calculator then it’s very useful to us.
thanks
Jeeten
Dear Naveen bhai
Most of people don’t even know what Emi is. People think they have to pay whatever bank or private company is demanding for, as a installments of loan. No one calculate what actual they are paying after getting loan. your social work is heping very much to innocent consumers. thnx so much for this help to everyone.
how do we “understand” what that PMT function does? I looked up in google but couldn’t find it.
I think you have missed the link provided to understand it better. Once again the link: http://office.microsoft.com/en-us/excel-help/pmt-HP005209215.aspx
Buddy,
Your calculator is awesome. It really helps a lot for all loan seekers.
I have one request, which to me seems very trivial, Excel Sheet should also display total amount being paid back. (I know this should be very easy. =SUM(C11:C250) should be enough.)
Thanks,
Harsh
thakyou sir
but i have another problem how to calculate monthly deposited interest.
& how to calculate NPA calculation
hi naveen you are a wonder. dude even i have also made some plans regarding deposits,loans. so you are we will share them.
Hi Naveen,
Its a nice one you are done.
I feel its better to shows the interest rate difference in IRR and ROI,
That’s easy to understand by the users. Make awareness about the Flat rate and the Diminishing rate its important to know before going for a loan.
Hi Naveen,
Is there any difference in calculating EMI for Hybrid and normal loans. I am talking about SBI’s Easy home loan. In this case the interest rate for first yesr is 8%, interest rate for second and third year is 9% and rest of the period, the interest rate 2.75% below SBAR rate (SBAR = 11.75% p.a.).
How can we calculate EMI in this kind of loans?
Thanks in advance,
Srikanth
Hi,
Its excellent………..
Thanks bhai, You helped me lot with this format as my boss is asking how to calculate Home loan EMI
Awsome Naveen….thanks 4 such a great tool
Hi Naveen,
how to put calculation for floating rate of interests??
MY BOSS ASKING ME. IF WE WILL BRROWING LAON HOW TO CALCULATE INTEREST
This is one of the best ting that i have found on the net. I was about write one myself but boy you nailed it. Let there be one less EMI Calculator gr8 job ….
Hello Naveen,
I have a car loan of Rs 2.5 lakhs. Rate of interest was 8% for 1st year and 10% for 2nd and 3rd year. I have paid total 6 installments till now each of Rs 7865/-. Now I want to reduce the tenure of my loan tenure to 18 months. Please advice me how to calculate what additional amount I have to pay per month now
Really, I don’t have an idea on how they calculate it for variable interest rate. But, just tried to find a rough value, though don’t assume it exact. Total loan amount + 6 months interest at 8% comes to 2.6 lakhs. You paid 47190 and the amount left is 2,12,810. So, if you are now converting them into 18 equated installments at 10%, it comes to Rs. 12,781 per month (using the EMI calculator attached to this post).
Good
Thanks a lot for your help
Dear Naveen:- Your EMI Calculator is very helpful. How to go about or do if we want the period in months instead of years
Open a new excel sheet and enter this formula: =months-you-need/12 for example if it is 8 months, then =8/12. Now you get a value in that cell. (0.666666667 in example case) Paste that value into the “Loan Term (years)” field. You will get the EMI chart for that many months. If you need it for 2 years 8 months, then the value becomes 2.666666667. 2 denotes years.
hi Naveen, this calculater is very easy, beautiful and helpful, above all you site is also too good. different and informative. can you help me with some macro writing ideas also.
Thanks.
Amreesh Nehra
Search the term “excel macros writing filetype:pdf” in Google and you will find a lot of PDF files to learn macro writing.
helpful calculater formula . please create inexcel & send me….
are mere bhai manually solve karne ka tareka bta yaar ye to mere ko pta h koi formulla h kya h to jaldi bta
Good job Naveen.Keep it up.
hi naveen,
it was a great time that i visited ur site, thanks for ur calculator, how about the mortorium period in cas of term loans from banks
regards
ramesh
Hi
It’s very easy to calculate. ThanQ very much.
very interesting calculation.try very easy calculation
Hi naveen, i am working in bank. i know normat pmt formula for constant payment. how to arrive to emi with amortisation chart when rate differs with varying periods. (e.g. 1st year 8%, 2nd n 3rd year 10%, 4th n 5th year 11.25, n for 6th n 7th year 11.50 %, as prevalent in State Bank. what r the formulas n how to proceed in excel for amortisation schedule. Its very urgent please. thanks in anticipation for ur definite reply.
That is what lot of people were asking here, which I am not able to answer. I never have come across that situation, so really don’t know how it is calculated. I am looking for some who can explain it and show me a way to do that.
thanks dude…
hi naveen,
in EMI calculator which you provided is very good, i need one thing i want to edit EMI cell also so that depends on that my EMI amount or tenure should change which is available with bank people. if possible look into that..
thanks
Hi Naveen,
I am a microfinance professional and keen to learn the different components affecting EMI calculation by using template.
Hi Naveen,
Good to find your website dedicated to EMI and other common but difficult calculations.
I have got a quotation for bank loan but I’m not able to understand the calculation formula. Could you please help me decode the formula based on the numbers they have provided.
Details:
Loan Amount = 75000 + 5000 (processing fees). 5000 is not to be paid separately. That means, the loan amount can be considered as 80000.
EMI quotations provided by the bank :-
5800 for 15 month period
4900 for 18 month period
3800 for 24 month period
2700 for 36 month period
Request you to derive the formula for calculating the above and share it with me. Rate of interest is not known.
Thanks & Best regards,
Vivek
Download the calculator Version 2. I just entered the months in “Loan Term (Years)” filed (Example: 1.25 for 15 months and 1.5 for 18 months) and 80,000 in “Loan Amount” field. Now, I started to change the interest rate in “Rate of Interest” field until it matched with the EMIs you have given. At 13%, it matched with the rate of interest. I have not tried any formula. There might be one, but this appeared simple to me. Try it and send your comments.
I really found it useful.
Would you be able to add a column for pre-payments too.
That will let us look at the impact on the EMI or tenure.
i have a question for loan rate IRR how to calculate it ? also i want to know if i lends rs. 13000/- @ 15 % flat emi will be bi-weekly and emi amounted to rs. 299/- every bi-weekly so what is the IRR. please send me excel sheet if possible
THANKS FOR YOUR KIND SUPPORT
Hi Naveen
I need the calculator for calculating the savings in special home loan schemes like the SBI Home Saver, HSBC Smart Home Loan, Citi Home Credit. I want to calculate the savings in home loan interest in these schemes by putting some savings on a monthly basis in the overdraft current account, linked to the o/s principal account.
thanks & rgds
Puneet
Hi,
Can u pls suggest an Home Loan Excel download that also takes into account prepayment options (over the entire tenure of loan)
That shall really be helpful as we seldom utilize the entire tenure of loan and annual prepayments are routine
Thanks
Hi Naveen
Good job. Many banks have recently offered a ‘discount’ on their interest rates for a concessional period. Take a scenario where the interest rate is 8% for year 1, 9% for year 2 and then 9.5% year 3 onwards. What is the principle there and how can one incorporate this in an excel sheet?
I am not sure if someone has asked this before since I did not read all of the many comments.
Have been asked many times. I am really not sure about how their calculation is, so unable to answer the queries. Sorry!
Naveen, I have come across your emi calculator and I am interested in the same please clarify the same.
I have taken union bank of india education loan of Rs.15.00 lakhs for commercial pilot and the interest rate is 12% p.a. Now I am pay interest during moratatium period and after completion I have to pay instalments which ubi has given as Rs.26500/ as emi. please send me formula how to calculate interest and emi for which I have taken education loan on 8th December, 2008. please calculate since I am paying interest from December, 2008 onwards
Thanking you,
Hi Naveen,
Some banks have a varied rate of interest for car loans.
For Example:
First 12 months @ 8% p.a
Next 24 months @ 10% p.a
Next 48 months @ 11.5% p.a
How do I calculate the same
Thanks in advance.
Parv
hi, naveen,while i came across this and find it interesting as am a power user of Excel. so if you really provide that one to calculate the intrest calculation to me i would be greatful.
thaning you,
devi
angul, nalco
Sorry! I didn’t get you what you are asking.
Hi,
how do i calculate my pre emi charges for a home loan…..
My loan disbursement started 2 months back but the total amount is still not released….. so i just pay the interest till then….. how do i calculate the same based on the duration between the first and last release of the amount. is this calculated based on Simple Interest only?
Hi Sir,
Please help me to find out how does bank calculate EMIs @ reducing rate of interest wherein EMI remains the same thruout the Duration
HI Naveen
I am confused about FV, PMT, EMI. What is difference ? Pls guide me.
Thks & Rgds
Swaminathan
Those are all Excel functions to do calculations. Google those words along with the word “Excel” and you can get the appropriate pages to gain knowledge about.
hi naveen it is realy fantastic have you balance sheet analysis excel sheet also if yes plese send to me by mail
dear naveen, thanx its really good formula for intt calculation.
vinod
thanks for the wonderful template.
Can you please find the way to calculate EMI for Loan from DIMINISHING RATE OF INTEREST.
It will be very helpful..
Thank you very much….
This calculator is based on diminishing rate only.
aku minta rumus perhitungan angsuran ya… apabila ada biaya administrasi provisi dan asuransi… contoh :
harga kendaraan : misal 50.000.000
pokok hutang : ?
bunga : misal 10 %
administrasi : misal 500.000
provisi : misal 500.000 / 1%
asuransi : misal 1.500.000 / 1.5%
angsuran : ?
dana terima bersih : 20.000.000
bagi yang bisa bantu harap email rumusnya ke email ku ya di :
ibenk_vincent@yahoo.co.id THANKS
Though they charge the administrative and insurance fees separately, they all include in the loan. For example, I have taken a loan of 200000 in which processing and insurance fees is deducted at 5000 and they deposit 195000 in our account. But, we need to pay the whole amount that is 200000. So, take the amount 50.000.000 as the principal amount and interest rate that is 10%. No separate calculation is needed.
Naveenji, could you please modify the spreadsheet to tabulate EMI for the following conditions:
Loan amount – X, Loan tenure – T, Interest rate for first 5 years – i1, Interest rate for remaining (T-5) years – i2.
Many thanks.
the amount is displaying in decimals even after using the accurate formulaes…why….? how can i resolve this problem
regards
kedarnath
Thanks for the excel sheet.
I have one query over here. If we make some lump sum amount in between then how can we will know the next emi payment and other details like principal, interest etc.
Regards
Charaku S
Good work
LOAN AMOUNT RS. 7500 INSTALMENT AMT PAYABLE RS 345 EVERY 15TH DAY AT FLAT RATE OF 15% P.A. TOTAL INST NO 25. SUPPOSE LOAN IS TAKEN TODAY i.e. 28th DEC,2010. THE FIRST INST. IS PAYABLE ON 11TH JAN, 2011 AND LAST INST. SHALL BE DUE ON 13TH DEC, 2011. PLEASE HELP TO GET REDUCING RATE OF INTEREST PAID
IN ABOVE MY QUESTION I HAVE TO PAY RS. 150 AS FINANCIAL CHARGES; SO CONSIDERING THIS IT SHALL INCREASE MY REDUCING RATE OF INTEREST PAID BY ME . SO PLEASE CONSIDER IT AND HELP TO GET THE REDUCING RATE OF INTEREST PAID BY ME
Hi,
I came across your EMI calculator and found it very useful. But for my requirement I have variable installments after 5 years..10 years and hence I want to edit the cells with my values. Since the sheet is password protected, could u please share the password with me (if not on site then on my mail).
#
LOAN AMOUNT RS. 7500 INSTALMENT AMT PAYABLE RS 345 EVERY 15TH DAY AT FLAT RATE OF 15% P.A. TOTAL INST NO 25. SUPPOSE LOAN IS TAKEN TODAY i.e. 28th DEC,2010. THE FIRST INST. IS PAYABLE ON 11TH JAN, 2011 AND LAST INST. SHALL BE DUE ON 13TH DEC, 2011. PLEASE HELP TO GET REDUCING RATE OF INTEREST PAID
# 180. HEMAL THAKKARNo Gravatar Says:
December 28th, 2010 at 7:26 pm
IN ABOVE MY QUESTION I HAVE TO PAY RS. 150 AS FINANCIAL CHARGES; SO CONSIDERING THIS IT SHALL INCREASE MY REDUCING RATE OF INTEREST PAID BY ME . SO PLEASE CONSIDER IT AND HELP TO GET THE REDUCING RATE OF INTEREST PAID BY ME
I HAVE SO MANY TIMES ASKED YOU FOR DAILY BASIS REDUCING BALANCE CALCULATION FORMULA BUT STILL I HAVE NOT RECEIVED SO PLEASE HELP ME FOR THIS
Sorry for not replying. I do not have one for calculating it daily because EMIs are calculated at monthly intervals and I have not thought about it.
HI Naveen
i have a requirement. I had searched a lot for excel based daily reducing loan table that could take care about but still not found hope you can help me
I required excel loan calculater with daily reducing balane
3. ability to change interest rate (every one know the interest rate gets changed many times over the loan period though it may be fixed for 1 or 2 or 3 years at start)
4. ability to add disbursements in installments (all available excels considers one time full disbursement, in case of under construction flats, dibursement is in installement as per work progress)
4. ability to change EMI amount (many times to repay loan ASAP, we change the EMI amount, sothat maximum principle can get deducted)
5. ability to compare total amount paied, without extra repayments and with extra repayment. i.e calculate how much interest and loan term you have saved.
IT IS VERY USEFUL FOR A MAN WHO WILL LEND A LOAN IN BANK
I am having differential rates for SBI car loan like ist year 8%, iind year and 3rd year 9.75 % so how do i calculate my EMI? do we have any programmable differential rate EMI calculator?
Hi naveen, i am working in bank. i know normal pmt formula for constant payment. how to arrive to emi with amortisation chart when rate differs with varying periods. (e.g. 1st year 8%, 2nd n 3rd year 10%, 4th n 5th year 11.25, n for 6th n 7th year 11.50 %, as prevalent in State Bank. what r the formulas n how to proceed in excel for amortisation schedule. Its very urgent please. thanks in anticipation for ur definite reply. this was submitted in Sept 2010, still awaiting solution. Hw to prepare amortisation chart as per varying periods and intt rates. pl send with priority.
I have not got enough time to search and develop an excel sheet for this. Though, when I get free time and enough data to do that, I will sure update it. Thanks for taking time to write comment. I appreciate it.
Hi, This is useful, but I found bank like ICICI is cheating me, They are keeping ROI increasing in a year 4 times and also tenure is showing 431 months against original 240 months. I’m not bale to calculate exact figure using this calculator for o/s balance. Can you please help me to do this ASAP.As I’m also looking for transferring loan to some other bank.
Rg,
Sandeep
Thanks for sharing this excel sheet EMI calculator. his is very helpful.
nice………….easy to understand
hi naveen ji,
thx for the excellent social service sheet. is there any formula to calculate EMI with floating interest rates (veriable interest rates).
if u r aware of some please do another social service.
thx a lot
parashar
thx . naveenji, it is realy great
Dear Naveen,
The information you have given is really helpful.
Thanks, God bless you.
Dear Mr. Naveen,
The Principal and Interest component could also be calculated using the PPMT and IPMT functions.
Dear Naveen,
This is really a good information. Best of luck for your future RND.
Pl. inform me on my e-mail for your any such informations, bcz it really
works in our routine life. Its amazing, thx a lot. Please Keep it up.
Excellent Buddy. Keep it up.
Thanks Naveen, But it should not display negative values, U can cut down or hide, Only to avoid confusion, Ok bye, Keep it up
Oh! seems that you have downloaded the old one. Just below it, there is an updated version with negative values removed along with what to do to achieve that. Anyway, removing it from the post, seems lot of people just downloading that old one.
Thanks Naveen,
I have i problem please slove me.
Example : Loan Amount : 22 Lakhs, tenure :36, Rate :10% , Arrears
33 month EMI :64160/- ; 12,24,36th months is will pay Bullet of Rs. 150000/- how to calcuate the emi automatically (EMI of Rs.64160/-Please help me.
Good work.. u can copy this worksheet, n then extend the tenure to whatever period u want. that will help u.
What is the difference between fixed rate calculation and reducing balance rate calculation
Fixed rate is a percentage calculated every year and then split into equal installments. Eg: 10000*10% per year and 12 EMIs=Rs. 917 per month
Diminished is calculated on balance at the end of month and then split into equal installments. Eg: 10000*10% diminishing and 12 EMIs=Rs. 879 per month
Diminishing Rate always benefits you.
I used ur EMI calculator it is usefull
Thanks Naveen
Updated the XLS sheet with extra columns deleted and centered the calculator in sheet (better viewed in wide screens). Download the new version.
Thanks Naveen to Excel EMI formula,but I suppose to know how to calculate EMI on Normal Calculator.What is the mathematics formula for this.
Please sent it on my mail ID.
Thanks
You can’t do that in a normal calculator. It needs a lot of manual calculations too.
This is an excellent tool. I usually search on Internet every time. But now, I using Naveen’s EMI calculator which handy and available in my local computer.
Thanks Naveen, really it’s a great tool.
Thanks,
Ramesh
hi naveen….
u done great job….carry forward ur new ideas
Dear Naveen,
Your excel is of invaluable help. Thanks for uploading.
I have one question regarding the Home Loans, I have taken a flat that is under construction and partial amount of home is disbured. Below are the specifics
Loan Amount: 20 Lakhs
Loan Tenure: 5 Years
ROI : 9.15 % Floating
Amount Disbursed: 8.8 Lakhs
I found that bank is calculating EMI for 20 Lakhs though only 8.8 Lakhs is disbursed, when i enquired with the bank, they said the rest of interest will be deducted in the principal and i will gain because of it. Can you let me know if that is true?
Really a wonderful tool to calculate principle/interest components of the EMIs paid.
Thank you very much for all the efforts put-in by you
Hi Naveen,
I modified the calculation sheet provided by you into 3 separate worksheets to help calculate EMI on fixed-cum-floating rates (as is prevalent in banks for home loans). This is a very rough version. I wish to send it to you as an attachment (don’t worry, there’s no virus in it) or upload onto a link from where you can see it. It’s broken up by years (rate1 for first 2 years, rate 2 for years 3 and 4 and rate3 for the remaining term). Please let me know when and how I can send it. We can discuss this further to come up with a proper calculator for home loans thereafter if needed.
Upload to some free file hosting services like Dropox.com or Box.net and send the link through “contact me” form. Thanks!
Hi Naveen,
How wouldit handle a moratorium for the first payment. That is if the loan is Feb 25, 2011 then no payment would be due until April 25, 2011.
Bear in mind that interest accrues still from FEb. 25, 2011
Thanks in advance
Downloaded your EMI calc.
Nice work.
Thank you.
Thanks for your kind words.
very nice. try to by the EMI to found term(years)
Good work!
Dear Naveen,
Your excel is of invaluable help. Thanks for uploading.
I have one question regarding the Home Loans, I have taken a flat that is under construction and partial amount of home is disbured. Below are the specifics
Loan Amount: 20 Lakhs
Loan Tenure: 5 Years
ROI : 9.15 % Floating
Amount Disbursed: 8.8 Lakhs
I found that bank is calculating EMI for 20 Lakhs though only 8.8 Lakhs is disbursed, when i enquired with the bank, they said the rest of interest will be deducted in the principal and i will gain because of it. Can you let me know if that is true?
Sorry! I do not have an idea about it.
Great Job! Thanks for Sharing. Can you update the sheet to have flexibility to change either Monthly Instalment or Loan Term. In my case Monthly Instalement is fixed.
I have worked out an worksheet which would calculate the total interest and loan end date for SBI Max Gain type of loan. Please follow the link to try it out.
https://spreadsheets.google.com/ccc?key=0AiFXuWPU0vJ5dDd0dG5ld0kxQ01tTDJ5YnVPZ0FWdnc&hl=en
This should help you maximize the interest gained by parking surplus fund on the o/d account.
Any comments are welcome.
great job
Dear Naveen , it is really helpful thanx for it.
great!!!!!!!!!
Dear Naveen, It is realy helpful to have ur emi calculator. Infact I was always confused about the way bank calculate and no banks explain it how? Now I am very much clear. One question u pls. clarify that all banks tells the rate which sounds very simple ie. 8%–9.5% etc but is it always compounded or at simple interest rate? Because I could not find that actually what rate of interest is being charged by this EMI calculation?
Thank a lot, Naveen…
Hi Naveen,
i got loan saction in SBI Last month, my current interest is
1st year – 9.75 ( Base Rate +0.5% )
2ed & 3Year- 10.5( Base Rate +1.25% )
from 4th Year – 10.75 ( Base Rate +1.5% )
i have option now to change 10.25% ( Base Rate +1.0% ) for 20 years,
i don’t know which is Good, shall i go with my current plan, Or changing is good
Hi Naveen,
Thanks a lot for the excel sheet.
Can i have the password of the excel sheet to modify.
I want to calculate for paying some money extra money
hi
very nice. good job.
i would be more happy, if you could provide the emi calculator for floating rate emi basis.
if you have the database of RBI’s interest rates, time to time for last 5 or 10 years, you can also add up.
thanks dear.
Thanks Naveen. This tool is very helpful.
Can Loan Term be put in months.Rgt not ther is restriction to put the tearm in years & that too not less that 12 moinths
Hi Naveen
Your EMI calculator is very helpful. Thanks
Manjunath
Thanks Naveen… this tool I was looking for many a times. It is very useful for me. Thanks.
thanks a lot
Thanks… Naveen
Can i have the password of the excel sheet to modify.
I want to calculate for paying some money extra money
I just put a random password and can’t remember. See if you can create one from the instructions given.
Thanks Naveen This will be useful for my profession,
Even it helps other peoples who can calculate the EMI
Thx, Naveen. I have validated the results. It’s accurate and easy to use. For lay man like me, it’s of great help and we can ensure that the banks don’t take us for a ride.
Great Effort Sir. Thank U very much. It is very Helpful
very helpful indeed
Good Job ! Keep up the good work !!!
thanks for this valuable site
Is there any govt guidelines on how an EMI should be calculated? I can see that EMI has larger proportion of Interest than principal in initial installments. If somebody does foreclosure say after 5 years, then he has already payed most of the interest and is actually paying the 70-80% of Principal…. Is it right?
Satish, yes you are right , In a single year I paid almost 3.5 lacs [for 24 lacs loan)…and still principal amount remain almost same…..pre closing also charging me another 60,000….Please avoid ICICI bank…this is my honest advice to you guys……
sir i need for calculation
Hi naveen..pls check your calculator again, I think there is some bug in your emi calculator..for example if I took FA=35500, Tenure=12, ROI=15%, then there should be emi of 3403 but your calculator is giving 3204 so pls check and try to fix…for any of your query you may write to t.jayaswal@gmail.com..I would be happy to assist you.
hi, thanks for doing such a great help,
can u plz help me for prepayment calculation…….
waiting for ur support.
Thanks Naveen
what if i decide to pay additional sum along with emi ? it’s effect on term or reduction in emi?
Thanks for your EMI EXCEL Formula.
Its excellent work.
Can you please send me your EMI calculator .
this is very helpful, thank you so much naveen
this information was very useful.
can u also provide the calculation, if we pay the installment before due date and after due date whatsoever.
thanks
I need to calculate Bank loan EMI, Principal amt in excel format….
You can use it to calculate.
thanks a ton boss!!u made it easy for us!!keep up the good work!GOD BLESS YOU!!
Thanks
This is absolutely great. i have done in 5 min…. thanks Navin
This is very useful information . Thanks a lot for posting this in website
Hi,
Cost of the bike is INR 67,507. I didn’t have that amount right now. I applied for load there only. They said that down payment is INR 33,718 and monthly installment is INR 4068 for 10 months with an interest rate of 8.5% and service tax of 2.5%. At the end of total amount I am paying an extra amount of INR 6891.
I am not getting how they calculated this.
Can any one explain me clearly?
It will be very helpful for me. thanks in advance.
Hey Naveen,
Thanks for the useful calci.
–
Thanks & regds,
Suraj Pradhan
Can some one help me in calculating the interest rate for Chitfunds? I need the calculation for 10 months, 20 months & 50 months. Thanks in advance…
sir ji very useful information & Thanks a lot posting this site I really v.thanks
Thanks a lot, it is very helpful.
Ravi
Dear Sir , I want to know about the bank interest calculation formula in excel sheet. please do the needful .
hi naveen,
two three years and visitors are still dropping by and besides your page ranks No 1 on how to calculate my EMI !!!
good job Naveen and thanks for the excel sheet.
regards,
BRM
thanks for the calculation sheet
this is very very use full for calculating emi
you have done a great job it your effort that my problem got solved thanks a lot
Thank you!!!!
Thanks Navin, it has erased lot of my confusions
Good job
Hi Navin,
Your excel is really helped me and i have a suggestion for modification.
If you could sum the Principal and Interest amount paid and display below monthly installment column would help lot.
Thanks
Hi Naveen, Thanks a lot. Its really a great tool.
Great job.
Hey thank you !!!!!!!! it helps me a lot
thank you
Its good and useful for do home work before go for loan
Hey Naveen, Great Job dude. I was looking for something like this. However, I saw that its write protected. I wanted to calculate how much would I need to pay if I prepay Rs. 500000 extra every year. Could you please send me the Unprotected sheet to my email address. I would be really very greatful.
Email Id: thposter at gmail dot com
Thanks,
Rahul
hi Naveen
thanks for your EMI calculator , your have good skills
best
EMI calculator is very good , it saved my time and helped me in analyse the loan details.
PLease send me if you have one for calculating housing loan EMI.
Please send emi calculator as i had taken Rs 22 lakhs house loan from HDFC and paid 3 months @ 10.25%. I want to repay Rs 1 lakh then what will be the emi for rest 177 months.
Dear Sir , I Want Know Structure EMI Calculation…….. Bank Given Me Different EMI So I Want To Know About Per Month Interest And Per Month Principal Amount. After 6 Month And After 12 Month emi r Chang so plz Help Me. I Need Per Month Interest And Principal Amount in excel……
Savan Thakkar
Hi…Tnx for the excel file. Is there any way i can enter prepayment amount in the calculation? because if i made prepayment, it completly alters the entire calculation you made in the excel, since my principle goes down. Thnx
Hi Naveen,
I am planning to prepay my car loan and want to know how I can find the amount to be paid as well as savings in the interest. I have a table with interest and principal column but I think the calculation is not that straightforward is it? Please let me know if you have some idea. Thanks.
your spread sheet helped me a lot. i am planning to learn macros in excell
Useful!
thanks bhai, but how to calculate clp plan loan value………..
Thanks.. Liked it..
great job dude!!
Superb!!!!
SUPER & EASY
Good mg,
Emi calculator is very useful and helpful. Thank you. Bank staff is saying there are two types of ways that emi has been calculated and they are saying flat emi and diminishing emi. kindly make me understand and send the flat emi calculator, if possible…
regards
prabu
Good job, keep it
very useful tool to explore loan amount
Dear Naveen, your EMI calculator is really useful, and it is a great community service that you have done by creating and sharing it freely for anyone who can download it. Am using for a clean-tech business plan, and if we are successful, we will definitely acknowledge the great help from your side (that is the least we can do… if we are not successful, no one is going to read what we write anyway
)
Thanks for your kind words. Best of luck with your business.
Its Ok but i think here total should also of all column.
Thanks Naveen for sharing EMI calculator. It helps me a lot. I will be very greatful to you If you could tell how to calculate loan eligibility.
It depends on lot of factors like salary, company where you are working, your creditworthiness, your loan repayment or credit card payment, etc.
can u also provide the calculation, if we pay the installment before due date and after due date whatsoever.
Keep it Up !!!
its useful to every one
Can u Also do the total of principle & total of interest
Hi Naveen…
I downloaded the excel file. Its really helpful.
Thanks a lot!!!!
Hi naveen,
pl. send me the EMI exel sheet alongwith calculator, so that ican calculat the EMI & exel Sheet.
Sub : Interest repayment on Loan for Higher Education
I would like to draw your attention that I had taken higher education loan for imparting higher education to my daughter ( Alisha Sengupta ) in USA. Loan A/c No. 30825564172.
I had to submit documents under Income Tax act against section 80D for getting I. T. rebate.
I would request you to please give me a certificate towards repayment of interest against loan from November’ 2011 to March’ 2012.
Thanking you,
Yours faithfully,
( Shankar Sengupta )
You should ask this in your bank and not here.
Thanks for saving my time Naveen.
Venkatesh
Hey thanks for the sheet Naveen… very useful….
Hello navin ,
hi this us very useful for us
thanks fr sharing it.
Hi! It’s really a great work. But please add some other features like annual cost and total payout.
very nice
very good,……….I like it
Thank you Very much & very useful for all of us.
Thanks! it was useful to prepare my spreadsheet calculating the EMI and what portion lies in Interest and how much in Principal.
Can you please add extra column for total amount anybody have to pay including interest at the end.??
Nice tool. Good to calculate and try different figures. Thanks for posting the tool.
I am plannig to buy home loan, so which is better option in payment like : to have less home loan or do prepayment after having the full loan. for e.g. loan of 50 lac and do prepayment after 1 month of 10 lac or taking loan of 40 lac.
Thanks in advance. Appreciate your help.
Hi Naveen,
this sheet is really helpful. Just have one more query.
In education loan, interest is charged monthly and we pay monthly interest till the time course is on. Once the education is complete, then we pay the principal amount. I want to understand how can we calculate the interest which bank is charging monthly.
Please respond.
Thanks for sharing this calculator, it is quite useful for me.. Thanks a ton buddy…
Thank you very much, Naveen.
really its very use full, thank u sir,
naveen sir i havnt paid my loan for 2 1/2 years.how they will calculate my loan amt and installmant
Along with EMI, you also need to pay penalty too. Check with the bank officials and see how much extra you need to pay.
Thanks Naveen.
Thanks Navin for this excel emi calculator .. its quite useful and save lot of time
Thanks for easy calculator.
Thanks a ton buddy………………
Navin, I am in need of EMI Calculator…
Please give me a EMI Calculator
Link is give at the bottom. I think you have missed that.
I AM VERY MUCH THANKFUL FOR YOUR FORMULA IT IS VERY HELPFUL
AND I WANT JUST YOUR SUGGESTION IF I COMPLETE FULL BALANCE LOAN THEN I HAVE TO PAY ONLY PRINCIPAL AMOUNT OR WHAT WILL BE THE POSITION?
Balance and Interest both are calculated together and equated in EMIs.
EMI Calculator on flat rate interest baiis required
Good work naveen…
I need the formulae to create emi
sir, i want to take a loan for purches four wheeler.
loan amount=450500/-
intrest rate=7.8%
EMI=???
halp me
Naveen short n superb. Simple & meets the need
Its very good for every one those who are planning to take loan.
Very nice information got, i can my work easily.
thaks
navin sir,
your emi calculater is very helpful for me
again thanks
nitin
hi naveen
thank you so much
hi
naveen great tool,
do you have formula / format to know how much principal i am paying per month.
in case of partial disbursement of loan & at the same time full EMI is started. i heard it is good becuase you pay higher principal then then normal & at the end your tenure will automatically reduced.
eg. i have taken loan of Rs 10,00,000 , disbursed amount is Rs 2,00,000 only, but i have started paying full EMI of ~ Rs 9986.
with this can you how to calculate interest & principal for this type of situation.
thanx
Dear Naveen, your EMI calculator is really useful, and it is a great community service that you have done by creating and sharing it freely for anyone who can download it. Am using for a clean-tech business plan, and if we are successful, we will definitely acknowledge the great help from your side
Thanks for the kind words.
Supperb calculation and its very usefull Naveen.
good one. But i need some addition in to this.
If rate of interest changes every 03 months.
1] Then how to calculate balance term , if EMI is fixed.
2] What will be the EMI, If term is fixed.
refer my mail dated 24/1/2012, Yet to receive any reply from you. Have you got my queries ??
Might be your queries are out of knowledge. Sorry, I won’t be able to help you. Due to busy schedule, I might have forgot to tell you that.
Hi,
Thanks for such a wonderful format. I would like to advice that if u can add another furmula under each section which will automatically give the total of huw much principle and how much interest the customer ends up paying would add more value to the same
ANYWAYS….Wonder stuff…
Thanks
Hi Naveen,
I came across ur EMI calculator excel and found it very useful. U r doing an excellent work. Keep up the good work
Thanks for the appreciation.
Thank you very much ….. Nanba !!
I have lot of confusion of this calculation, Now i got conclusion ….
Thanks ……………..
I would like to have a calculator on Deposits.
I am investing 5 Yr compounded fd,
To calcualte the TDS which bank is going to deduct every year
I need break up of value at the end of financial year.
Eg, 1,00,000 @ 9.25 % comounded 5 yrs
maturityu value – 1,54,757
I need break up of year wise value addition to enable me to calcuate my TDS liablitiy
Thanks
Dud, how can I get the floating rate, and its not matching with Home loan calculation.
dear naveeen.
the emi calculator is very useful.i want to know how to calculate in ordinary calculator.
thanks
Hi Navin,
The calculator you have created is excellent. However, as you know in India, if you buy a flat/ apartment in a building the interest / EMI is charged on the basis of disbursed amount and not on approved amount. Can you in-corporate that ?
Ex:
Person A purchased a flat worth 10L on 01.01.2012. The tenure is 10 years and ROI is 10.75%. 1st installment is 2L disbursed on 15.01.2012. Now bank will start the EMI of 13634 from 15.01.2012 but the interest will be only on 2L ( which is 1708.22 and pay towards principal is 13634-1708.22 = 11925.78) so at the end of the month principal outstanding would be 188074. incase there is another disbursement of 1L on 20.02.2012 then outstanding will go up and it would be 288074.
would you be able to include this?
Regards,
Kiran
hi naveen,
how to calculate the effective rate if 1 month advance emi is collected, does that mean we only pay 35 months instead of 36 months. i am confused can you explain how does it work out and kindly send a calculator with advance emi option included in it.
srinivas
I don’t have knowledge about how to calculate in those situations (advance EMI).
How to get monthly installment if you have only. Period & loan amount & interest rate…….? if you dont have PMT formula.