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

EMI 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.

Lot more can be discussed about EMI. Please share your knowledge, doubts or experiences with EMI calculations by posting comments below.

46 Comments
  1. Atul Gajbhiye
  2. Shreedhar
  3. Shreedhar
  4. balaji m
    • Vedmvenu
    • Manoj kumar
    • Gadad G B
    • Gadad G B
    • Gadad G B
  5. jitendrakulkarni
  6. riya
    • Jaanu
  7. sougata roy
  8. anish
    • Vineet Patawari
      • sudhakar
  9. Sameer
  10. Fund Loan
  11. Fund Loan
  12. PRAJJAL DE
  13. Home loan emi calculator
  14. manish
  15. manish
    • N K verma
      • Vineet Patawari
    • digvijay
      • sudhakar
  16. raj
  17. hearthstone arena cheat
  18. Samir
    • Vineet Patawari
  19. Manoj swami
    • Naveen Chandra Gupta
      • Manoj swami
      • Naveen Chandra Gupta
      • Naveen Chandra Gupta
  20. Romin
  21. Naveen Chandra Gupta Belide
  22. NANDEESH H N
    • balakumaresh
  23. purna
  24. NANDEESH H N
  25. NANDEESH H N
    • Tapan Kumar Behera
    • lalit bharti

Leave a Reply

Your email address will not be published. Required fields are marked *