Having discussed the full form, it's meaning and detailed explanation of the term EMI in the previous article, "What is EMI?", we will now learn how to calculate the EMI for a given amount of loan with a given interest rate and for a given repayment period. We can calculate the EMI in two ways. One is by using spread sheet like Microsoft Excel and the other is by using a formula. We will discuss both the ways so that you can use whichever is convenient to you.
By using Microsoft Excel: The function used in Excel for calculating the EMI is called PMT. PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
1. First open the Excel 2007 by StartÃ ProgramsÃ Microsoft OfficeÃ Microsoft Excel 2007.
2. Now select a cell in the work sheet to get the result and place the cursor in that cell.
3. Click the function button just above the grid portion of the work sheet or select FarmulasÃ FinancialÃ PMT function from the menu. If you click the function button, select the category as Financial, then select the function as PMT, and then click OK button. The function will need 3 compulsory values (arguments) and two optional values to be entered to get the result. The compulsory arguments are in bold face while the optional arguments are not in bold face. The detailed description of the arguments are
a. Rate is the interest rate per period in percentage.
b. Nper is the total number of payments for the loan or periods. It is generally in months.
c. Pv is the present value; the total amount that a series of future payments is worth now. Here it is the principal amount of the loan.
d. Fv is the future value, or a cash balance you want to attain after the last payment is made. This is optional and will be taken as zero, if omitted. In case of loan it is zero.
e. Type is a logical value: payment at the beginning of the period=1; payment at the end of the period or omitted=0. This is also optional and in our case it is zero.
4. Then a small window (function arguments window) will open prompting you to enter the values of the above mentioned function arguments.
5. Enter the values of Rate, Nper, and Pv. Click OK. Please make sure that the interest rate is per the period, i.e., if the period is month the interest rate should also be per month. Normally the interest rate is mentioned per year. In that case divide it by 12 to get the rate for the formula.
6. Your EMI value will appear in the selected cell.
- The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.
By using the Formula: It may not always be possible for you to access computer or Excel. In such situations also EMI can be calculated using a formula. Here is the formula for calculating EMI.
The formula for calculation of EMI given the loan, term, and interest rate is:
EMI = [(p*r) (1+r)^n ] / [ (1+r)^n â 1 ]
Where p = principal (amount of loan)
r = rate of interest per month (annual rate/12) and
n = no. of instalments in the tenure.