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.
