Method of EMI Calculation on Excel

EMI is a fixed amount which is returned by the borrower to the lender financial institute, every month. This amount will be directed to the principal loan amount and the interest rate applicable on loan. Payment of EMI is dispersed over the tenure of loan that is opted for by the borrower.

EMI Calculation Method

Calculating EMI has a Simple Formula:

EMI = (p X r/12) X [(1+r/12) ^n] / [(1+r/12) ^(n-1)]

where,

p is the principal loan amount, r is the interest rate applicable per annum and n is the loan tenure.

In case you do not wish to determine the EMI manually or through EMI Calculator that is suitable for your loan, an alternative is to use Excel. Calculation of EMI on Excel is straightforward and the formula used is very simple.

Loan Calculator Excel

On the Excel sheet, select a cell and enter the following formula:

=pmt(rate,nper,pv,fv,type)

Here,

'Rate' is the interest rate applicable to the loan amount annually, 'nper' is the loan tenure, 'pv' stands for the present value or the principal amount, 'fv' stands for the future value or the cash balance after the last payment has been made. Type can take the value of 0 or 1. If the payment is due at the end of the year, the type will take the value of 0 and if the payment is due at the end of the month then the type can be set as 1.

Factors that affect the EMI amount

The EMI amount towards a loan varies from person to person depending on the conditions, the factors that play a crucial role in determining the number of monthly installments are:

Loan Amount

It is the amount that an individual borrows from the financial institution or any NBFC. The greater the loan amount is, the higher is the EMI.

Repayment Tenure

EMIs are distributed equally through the loan tenure. With a longer tenure, the borrower is required to pay a smaller amount of EMI.

Rate of interest

The rate is directly proportional to the absolute debt, an increase in the interest rate results in an increase in the monthly installments payable.

Down payment amount

If the borrower makes a down payment then it reduces the amount of loan that an individual needs to borrow from the lender. With an increase in the quantum of down payment, the amount paid in the form of EMI also reduces.

Why should you calculate your EMI?

There are many benefits of estimating the EMI before applying for a loan

• It reduces the chances of default
• It can help you in managing the finances better
• It can give you a comparative analysis of different lenders in the financial market. From the analysis, you can choose the lender which suits your needs the best.
• You can also choose the loan amount and tenure
• There are lesser chances of your loan getting rejected if you estimate the EMI payable beforehand
• You can maintain a healthier credit history

Things to Know

In the total loan expense, charges such as taxes, late payment fees, prepayment charges and other processing charges are included and they aren't projected in the estimate.

The rate of interest is calculated by the lender every month and not on an annual basis. You can convert the annual interest rate into monthly interest rate by dividing the percentage by 12. If the annual interest rate is 12%, then the monthly interest rate is 0.01%

Percentage divided by 12. For example, if the annual rate of interest is 12%, then the monthly interest rate = 12%/12, i.e., 0.01.