How to calculate EMI in Excel?
EMI value can be calculated in Excel using PMT financial function. this function can be used for future value calcualtion of an investment. Here is syntax of PMT function:
=PMT(RATE, NPER, PV, FV, TYPE) where
- rate – the annual interest rate for the loan.
- nper – the total number of payments for the loan.
- pv – the present value or the amount borrowed or the “principal of the loan.
- fv – future value – for a loan this will be 0. For loans this argument can be omitted.
- type – indicates when payments are due:
- “0″ (or omitted) – at the end of the period ie: end of the month.
- “1″ – at the beginning of the period ie: beginning of the month.
For instance, if you want to find EMI value for a loan amount of 10,00,000 which is payable in say 10 years (i.e., 120 monthly instalments) with an interest rate of say 12% p.a., the EMI can be calculated by placing the following formula in a cell in Excel spreadsheet:
=PMT(12%/12, 60, 100000, 0, 0);
Please note that the rate to used in the formula should be monthly rate (rate/12) i.e. 12%/12=1% or 0.01 in the above example. This formula applies to Excel 2007, Excel 2003, Excel XP and Excel 2000.
The Pmt function can also be used in VBA code. For example:
Dim LValue As Currency
LValue = Pmt(RATE, NPER, PV, FV, TYPE)
Mathematical EMI Formula
If you are looking for EMI formula to develop a EMI calcualtor, here is the exact EMI formula that can be used for calculating EMI amount for any given values of Principal, Interest Rate and Loan Period:
EMI = (P * R/12) * [ (1+R/12)^N] / [ (1+R/12)^N-1], where
- P = Principal (loan amount);
- R = Annual Interest Rate;
- N = No. of Monthly Instalments