EMI value can be calculated in Excel using PMT financial function. this function can be used for future value calculation 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 installments) 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.
VBA Code
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 calculator, 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 Installments