You can use Excel spreadsheet to calculate your EMIs (Equated Monthly Installments), or use various apps available on the internet to understand your home loan repayment schedule and how EMI is calculated.
EMI comprises principal and interest charged on the outstanding loan amount. Although EMI remains constant throughout the repayment period, the interest and principal components vary from month to month.
Equated Monthly Installment, as the name suggests, remain constant throughout loan repayment period, if interest rate remain the same (fixed rate loan). In case of floating rate loans, the EMI will change as and when interest rate is changed. Alternatively, keeping the same EMI, the balance loan tenure can be altered. If interest rate goes up during repayment tenure, the balance tenure increases, if EMI is not increased. Increasing the tenure is feasible, until EMI amount is higher than interest amount charged, as per increased interest rate. Similarly in case of reduction in interest rate, by keeping the same EMI, balance repayment tenure can be reduced.
EMI calculation by mathematical formula:
Where ‘EMI’ is equated monthly instalment in Rupees, ‘L’ is Loan amount in Rupees, ‘r’ is rate of interest and ‘n’ is loan tenure
Normally EMIs are calculated on monthly rests (monthly reducing balance method).
For example, if interest rate is 8.5% pa, ‘r’ should be taken as (8.5/100)/12 = 0.007083 and for tenure of 20 years, ‘n’ should be taken as 20x 12 = 240.
EMI Calculation using Excel Spreadsheet:
Nowadays there is no need to juggle with the above formula as PMT function in Excel spreadsheet in your PC can help you to calculate EMIs very easily.
If you are using Excel 2007, you need to shift from home to formulas tab and select fx (insert function). The drop box appears, in which you need to select PMT function. PMT function calculates payment for a loan based on constant payments and a constant interest rate.
The input parameters are, Rate refers to interest rate in % or in decimals, Nper refers to number of payments, Pv refers Present Value (Loan Value), Fv refers to Future Value, Type refers 0 or 1, i.e., when payments are due (0 – when payments due at the end of period and 1 – payment due at the beginning of the period). PMT refers to payment, i.e., EMI amount.
Let us calculate EMI for a loan of Rs. 10 lakh at interest rate of 8.5% and repayment tenure of 20 years.
On the PMT screen of Excel, please enter the parameters as below:
Rate: 8.5%/12 (Rate of interest 8.5% pa divided by 12, to make monthly interest)
Nper: 240 (No. of EMIs)
Pv: – 10,00,000 (loan amount)
PMT: 8,678.23 = Rs. 8,679 (rounded off) (EMI amount)
There are a number of apps available free on the internet/Google Play store, which can be downloaded on your mobile/PC, which can offer immediate results for all repayment terms such as EMI, amortization schedule etc.
Rajendra Deshpande, Managing Director, PropSeva®
Chartered Engineer Mortgage Banker