How to calculate EMI?

EMI Calculation

In our daily life we face enormous application of mathematics. Calculation of equated monthly installments (EMI) for car or home loan is one such common application of mathematics.

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. Most of us always believe the bank executives blindly on the figure which they quote as EMI.

This post is to explain the mathematics behind EMI and how to calculate it in excel using inbuilt excel function. If you wish to learn shortcut to find the time required to double a sum of money invested at certain rate of interest, you can apply Rule of 72.

EMI Calculation Formula Here P = principal amount (loan taken)

r = interest rate per month (ex: if interest rate per annum is 10% then 10/(12*100))

n= tenure in months

For example,

EMI = 100000*0.01*(1+0.01)^24 /((1+0.01)^24 -1) =  4707

Where,

p = loan taken = 1,00,000

r = interest rate per month = 1% = 0.01

n= tenure in months = 2 Years = 24 months

This formula assumes, EMI payment is made at the end of each period (month). This is also called EMI in arrears. If EMI is paid at the beginning of each period it is called EMI in advance.

Further additions will be done on EMI for any other processing fee or possible charges which may be applicable as per the rules of financing institutions (bank).

Calculation of EMI in excel

In excel it is very simple to calculate EMI. There is an inbuilt formula for EMI calculation called PMT

PMT(rate,nper,pv)

Where,

Rate – Interest rate for the loan.

nper – Total number of payments for the loan.

PV – Present value/principal or loan taken.

FV – Future value (you can omit it)

Type – we have to put the value either 0 or 1. If payments are made at the beginning (EMI in advance) of each period, 1 is used. If EMI payments are made at the end of the period (EMI in arrears) put 0. If omitted 0 is taken a default value. Just open a new excel file and try it yourself. Vineet Patawari

Hi, I'm Vineet Patawari. I fell in love with numbers after being scared of them for quite some time. Now, I'm here to make you feel comfortable with numbers and help you get rid of Math Phobia!

48 thoughts to “How to calculate EMI?”

1. Atul Gajbhiye says:

Thanks Vinit ..
I am working on a EMI Software .The calculations according this formula getting well. Hop so will work in after implementation.

2. Shreedhar says:

Is there any manual formal available for calculating rate of interest if I have total loan amount, monthly installment amount & tenure. Example, 2875000 loan amount repaid by 360000/-per month for 10 months. Then how calculate manually the rate of interest included in this transaction.
Thank You.

3. Shreedhar says:

Is there any manual formal available for calculating rate of interest if I have total loan amount, monthly installment amount & tenure. Example, 2875000 loan amount repaid by 360000/-per month for 10 months. Then how calculate manually the rate of interest included in this transaction.
Thank You.

4. balaji m says:

Respected sir,
i am Balaji M i want to know how to calculate emi for
p = 100000
Roi= 12%
N= 12 months
in that i want what is the short cut to calculate (0.01)^12, detailed step i need so that i can proceed further otherwise i have to calculate like 0.01*0.01*0.01………like wise 12 times so plz guide with detailed steps

1. Vedmvenu says:

Simple.Take log of 0.01 and multiply it with 12 I.e find 12 log0.01 and then look at the anti log of it ….it is your answer

2. Manoj kumar says:

EMI=principal amount or loan amount × r(1+r)^n/(1+r)^n-1
= 100000×.01(1+.01)^12/(1+.01)^12-1
= 8885
Note: r = rate of interest/12×100
r = 12/12×100 = .01

3. Gadad G B says:

Send me detail emi calculation manually

4. Gadad G B says:

Send me detail emi calculation manually

5. Gadad G B says:

Send me detail emi calculation manually

5. jitendrakulkarni says:

The mathematical formula for calculating EMI is :
[P x R x (1+R) ^n] / [(1+R)^ n-1].
P is the principal loan amount,
R rate of interest per month
and N is the the number of monthly installments.

Source : http://emicalculators.in/

6. riya says:

dear sir ,

ex, loan amount is rs. 60,00000 rate of inetrest is 9.15% and is to be paid in 10 years then how much EMI i have to pay every months for 10 yrs.

1. Jaanu says:

76,493 emi

7. sougata roy says:

Dear Sir,
I calculated as you showed. But on SBI website I calculated with their EMI calculator which is different. I calculated on principal 1000000, Interest rate 9.75% and 180 months of tenure. With your formula I obtain EMI Rs. 97500 but on SBI site it is shown Rs. 10516.99. Here is the link https://www.sbi.co.in/portal/web/home/emi-calculator

8. anish says:

Sir, Good afternoon

Kindly help me to find the each step of EMI – P.r (1+r)N / (1+r)n- 1

How to work out – (1+r)n/(1+r)n – 1 confused how to calculate this for ex. Loan amt 15,00,000 int 60% tenure 12 months – while applying this formula i get some other amount kindly help me sir

1. Vineet Patawari says:

Dear Anish,

I’ve replaced the text formula of EMI with an image for better readability. Hope that will help.

Loan 15,00,000
int 60%
tenure 12 months

First step, express int rate in monthly terms. Hence it will be 60/12 = 5%

Now using the formula,
EMI = 15,00,000*0.05*(1+0.05)^12 /((1+0.05)^12 -1) = 1,69,238.11

ps: though as an example it’s fine, however, 60% interest per annum is extremely high.

1. sudhakar says:

Mr.Vineet

How the EMI is coming Rs.4707/..As per my calculation it is coming Rs.4166.66

Pl.clarify it

CMA.Sudhakar

9. Sameer says:

Hi,

Can you help me with the manual calculation for the emi? The confusing part is that all the components of the emi are variable viz. Interest, principal to be paid and the emi amount.

Thanks
Sameer

10. Fund Loan says:

Wow, good formula for EMI Calculation. Thanks for sharing. Home Loans EMI Calculator

11. Fund Loan says:

Hi,
Nice post and provide good information about Home Loans EMI Calculator. Thanks for sharing.

12. PRAJJAL DE says:

How to calculate emi for simple interest loan??for staff loans it is required.kindly show me the formulae for calculating these loans.

13. Home loan emi calculator says:

There is no doubt that loan EMI calculation is really complicated to lots of people. But thanks for your post, it’s really helpful in it.

14. manish says:

How this step

15. manish says:

Sir how we calculate step by step as
In example above shown above fist page
100000*0.01*(1+.01)^24/((1+.01)^24-1)
=4707
How it is solved
kindly provide step by step methods

1. N K verma says:

How value of^
Calculate step by step calculations

E M I 4707 calculation explain

Specially value of(1+0.01)^

1. Vineet Patawari says:

Dear Mr.Verma,

^ symbol was used to denote “raised to power”. I’ve rewritten the formula in the post by inserting an image for more clarity. Kindly also check my reply to Anish for better understanding.

Thanks
Vineet

2. digvijay says:

100000*0.01*(1+.01)^24/((1+.01)^24-1)
=100000x(1.01)*24/(1.01)*24-1
=100000/0.24
=4707
Hence Proved

No need to thanks , , that’s my job..

1. sudhakar says:

How it is coming 4707

It is coming 4166.66

pl.clarigy mr.vineet

CMA.Sudhakar

16. raj says:

The Mathematical formula for calculating
EMI = [P x R x (1+R) ^n] / [(1+R)^ n-1].
P is the principal loan amount,
R rate of interest per month and
N is the the number of monthly installments).
Manual calculations are too complicated to perform accurately, which is why many borrowers are left confused after availing a loan. Understanding this pain-point led BankBazaar to develop one of the easiest and most user-friendly online Loan EMI Calculators.

17. hearthstone arena cheat says:

Pretty part of content. I simply stumbled upon your weblog and in accession capital to claim that I get in fact enjoyed account your weblog posts.
Anyway I’ll be subscribing on your feeds or even I fulfillment you access constantly rapidly.

18. Samir says:

Will you please send me the expression for advance emi in excel sheet.i am unable to do that thanks

Regards
Samir roy
8879987159

1. Vineet Patawari says:

As mentioned in the post, while putting down the EMI formula (PMT) in excel, we’ve to mention TYPE – here we have to put the value either 0 or 1. If payments are made at the beginning (EMI in advance) of each period, 1 is used. If EMI payments are made at the end of the period (EMI in arrears) put 0. If omitted 0 is taken a default value.

19. Manoj swami says:

Hello every1.

An amount of 9270 is to be returned in 3 equal installments. If rate of intrst is 3%per annum then find each installment value.

Give a shortcut formula also if available please.

1. Naveen Chandra Gupta says:

1. Manoj swami says:

How did u calculate this. ? Please explain…

2. Naveen Chandra Gupta says:

Mr. Manoj Swami

I have Found out a new formula for this to do, not only this we can do lot or all calculations relating to EMI calculator, I think you did not visited my Blog please visit and Download my new type of EMI Calculator

Main thing in this blog is my New Type of EMI Calculator download it, if you do not download the EMI calculator wast of visiting my Blog, I have given so many links on the top of my blog all are same, so download from any one of the link and one more thing if you see the Normal Formula for calculating which i have given in the blog it is totally different formula because i have found out the formula again which is already exist instead of taking the existing formula from books or Website

3. Naveen Chandra Gupta says:

There is no shot cut formula for 3 months. There is shot cut formula for 2 months but i don’t no the formula, i saw the formula in some book.

20. Romin says:

Is there formula for reverse calculation? Eg: EMI, P, R are known. Need to find the tenure.

1. Naveen Chandra Gupta says:

21. Naveen Chandra Gupta Belide says:

There are there 3 of EMI Calculators

Formula for Calculate EMI is:

p x (((i/12) + 100)/100)^t
________________________________________________________________________

1 + (((i/12) + 100)/100) + (((i/12) + 100)/100)^2 + ——— + (((i/12) + 100)/100)^t-1

Where:

P is Principal

I is Rate of Interest per Annam

T is Tenure

22. NANDEESH H N says:

I had a bad experience with a bank. Out of the total amount refunded every year, they were bifurcating it into principal refund and interest refund. Per year their interest was about Rs 10000/= more than what it should have been. I persisted with my calculation and after many months they admitted that there was a bug in their software and thanked me for getting hold of it.
Better to be careful with the calculations.

I had suggested a thumb rule which is quite useful in comparing different loan propositions and possible EMI.

The thumb rule is
EMI is slightly more than P * (r/2 + 1/n) where P = principal, r = rate of interest per month, n = number of months.

1. balakumaresh says:

Can u Calcualte it for an amount of Rs. 10.00 Lacs for 12 months and ROI is 9.65 %

I hope its not correct ur formulae is wrongs needs to be corrected

23. purna says:

nice one………..!!!!!!

24. NANDEESH H N says:

A rough estimate of EMI can be arrived at without calculator/excel as follows:

EMI = p * (r/2 + 1/n)

In your example: p = 100,000, r = 0.01, n = 24 then EMI = 100000 * (0.005 + 0.04) = 4500.

25. NANDEESH H N says:

A rough estimate of EMI can be arrived at without calculator/excel as follows:

EMI = p * (r + 1/n)

In your example: p = 100,000, r = 0.01, n = 24 then EMI = 100000 * (0.01 + 0.04) = 5000.
This estimate is always slightly higher than the actual figure.

1. Tapan Kumar Behera says:
2. lalit bharti says: