How to calculate EMI in Excel?
EMI value can be calculated in Excel using PMT function, which has the following syntax:
=PMT(RATE,NPER,PV,FV,TYPE)
For instance, if you want to find EMI value for a loan amount of 100,000 which is payable in say 5 years (i.e., 60 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(0.01,60,100000,0,0);
It must be noted here that the rate to used in the formula should be monthly rate i.e. 12%/12=1% or 0.01 in the above example. You may also note that the last two parameters in the function (FV and TYPE) are optional and if ignored they are assumed to be 0; You may refer to Excel help on this function for further explanation on this.
EMI Formula
For the mathematically curious minded, 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
That is the EMI maths; So, EMI of a loan can be derived using the above formula with the help of just an electronic calculator device!
Online EMI Calculator
EMI Calculation Excel Spreadsheet Model
|
|