Equated Monthly Installment (EMI)

Calculating your Equated Monthly Installment (EMI) is very easy to do in Excel. There are numerous online calculators that provide the same functionality. However, in our world, we prefer Excel.

Equated Monthly Installment (EMI) Math Formula

Mathematically, the formula for EMI is below. Definitely brings back order of operations.

EMI = [P x R x (1+R)^N]/[(1+R)^N-1] 
  • E – EMI
  • P – Principal Loan Amount
  • r – Rate of interest calculated on monthly basis.
  • n – Loan term in Years / Months (Divide by 12 for Months)

Calculate EMI in Excel

Excel makes it much easier to calculate EMI by using the PMT Function. Plus, you do not need to pull out your TI-83 calculator. (I had a TI-89 and still do).

=PMT (rate, nper, pv, [fv], [type])  
  • Rate:     Interest Rate per period
  • Nper:    The number of periods
  • Pv:         Present value of loan/investment
  • Fv:         Future value of the loan/investment – This is optional. If left blank, it will default to zero.
  • Type:     Defines if the payment is made at the start or end of the period. If left blank, defaults to zero.

              0 – Payment is made at the end of the period
             1 – Payment is made at the beginning of the period

Let’s look at the following example. Saw were are purchasing a car and need to borrow $15,500 over 3 years at 6.5% annual interest rate. Our EMI would be $475.06 per Month or $5,700.71 year.

Equated Monthly Installment EMI
EMI Calculator in Excel

Leave a Reply

CONCAT Function

The Excel CONCAT Function joins a range of cells or individual strings of data. This function was introduced in 2016

Read More »
Scroll to Top