EMI Formula in Excel

What is EMI?

The full form of EMI is Equated Monthly Instalments. It is related to the payment of a loan over a fixed period. The concept of EMI was introduced to help borrowers pay a fixed amount to the creditor every month. EMIs are usually calculated on the basis of the principal amount with a rate of interest over a set period. In today's Era, EMI has become a part of our lives and plays an important role in budget management, cost analysis, finance management, etc. Now, the question arises: how do you calculate your EMI using Excel? Microsoft Excel has a solution for this problem as well. Before moving ahead with the formula, let's understand a bit more about EMI, its working process, and important terms.

How to calculate EMI?

EMI can be calculated using two methods given below:

1. Flat rate Method
2. Reducing Balance Method

Flat Rate Method

In the flat rate method, the interest rate is calculated with the help of actual amount if the loan amount is increasingly paid. This method calculates the entire principal amount throughout the loan texture. The flat rate method calculated the loan interest using the actual principal amount, regardless whether the loan amount is progressively repaid or not.

The Formula for Flat Rate Method is given below:

Reducing Balance Method

In the Reducing Balance Method, interest is calculated on the unpaid loan amount at the end of each month. Therefore, after each EMI payment, the unpaid loan amount decreases, ultimately reducing the monthly interest rate. Thus, this method is deemed a fairer method of calculating EMIs when compared to the flat rate method.

The Formula for Reducing balance Method is given below:

Where,

P = Principal Loan amount

R = Rate of Interest paid per month

N = Loan Time Period in months

What are the factors present in EMI?

There are various factors the contributes in the calculation of EMI. Let's understand each one of them in this section:

• Principal amount:
  The principal or initial loan amount is the EMI amount that must be paid every month. The principal amount is high; the EMI amount should be paid higher. The EMI amount should be paid low if the principal amount is low.
• Interest rate:
  The EMI is charged on the basis of the interest rate. EMI are directly proportional to interest rate, which means higher the interest rate, higher the EMI amount is paid. If the interest rate is low, the EMI amount is paid lower.
• Loan tenure:
  The Total time for which you want to take the loan. It is generally given in years.
• Processing fee and other charges
  Every Bank charge some processing fee and other indicative charges to sanction your loan. These are compulsory to pay; otherwise, your loan won't be approved.

Types of EMI Loan

There are different types of EMI loans available, which are as follows:

1. Personal Loan: This loan type is the most common and frequently taken among customers. It is quite versatile as it allows the user to choose the preferred type. Throughout the loan period, the borrower pays the fixed EMI amount. One can take personal loans for any purpose, unlike debt consolidation, home interiors, medical payments, travel costs and other financial crises.
2. Home Loan: As the name suggests, a home loan allows the users to buy a home or property. It is usually taken for the long term and is available at competitive rates. The home loan amount includes principal repayment and interest components. The loan period extends up to 25 years or more, depending selected payment tenure.
3. Car Loan: Car loans are taken to buy cars and bikes. The EMI amount is calculated based on various factors such as the vehicle's cost, down payment method and loan tenure. Some car loan packages also provide balloon payment or residual value options.
4. Education Loan: Educational loans provide the necessary financial support to students. The loan repayment starts once the course is completed, and the students need to pay the EMI amount after that. The advantage of paying an education loan is that it comes with lower interest rates for educational institutions and higher studies.

Formula to calculate EMI in Microsoft Excel

Excel provides an inbuilt formula to calculate EMI. The formula used is as follows:

The explanation for the above parameters are given below:

• rate (required)- It indicates the interest rate paid for the loan
• nper (required)- This parameter represents the total number of payments paid for the loan.
• Pv (required)- PV or present value represents the total value of all loans. i.e. outstanding loan amount
• Fv-[optional] - fv stands for future value. It indicates the balance value, which the user must return after the final payment. If not specified, the default value of this parameter is set to zero.
• Type-[optional] -If the payment is due, the type is calculated ad 0 (at the end of the period) and 1 (towards the beginning of the period). If not specified, the default value of this parameter is set to 0.

NOTE: When calculating EMI in Excel, always make sure that the units for rate and nper are the same. For instance, if you want to calculate a monthly EMI, the specified rate should be the monthly interest rate, not the annual interest rate.

Let's understand the formula with the help of examples:

Example 1: Calculate the EMI for Loan amount of 40 lakh rupees at 8% interest per annum for 15 years.

Solution: For the above problem, we are given the following parameters:

Rate: 10%/4 per quarter

Npr= 20x4

The formula to calculate EMI in Excel will be as follows:

=PMT (8%/12, 15*12, 4000000, 00,000)

The steps to be followed are:

1. Open your Excel worksheet.
2. In cell B2, enter the principal amount
3. In cell B3, enter the given interest rate. Make sure to write the interest rate in %.
4. In cell B4, specify the given loan tenure (in years). Refer to the below image:
   
5. Select a cell namely B6, using the above details and enter the above formula as =PMT (B3/12, B4*12, -B2)
   
6. Once done, click on Enter. As a result, the EMI will be given. Refer to the below image:
   

It means if you take a loan of 4000000 at an interest rate 8% for 15 years, then your monthly EMI will be 38226, rupees.

Using the above details, you can easily calculate any EMI. But make sure to double check all the details and the parameter values, a small mistake will deviate you from the actual output. Thus, resulting in incorrect values and wrong Emi estimations.

Example 2: Calculate the EMI for Loan amount of 40 lakh rupees at 10% interest per annum for 15 years. The interest is repaid in quarterly installments.

The problem is the same as above. The only difference is that here, instead of annually, we have to pay the instalments quarterly. Therefore, there will be a slight change in the formula.

The formula to calculate EMI in Excel will be as follows:

The steps to be followed are:

1. Open your Excel worksheet.
2. In cell B2, enter the principal amount
3. In cell B3, enter the given interest rate. Make sure to write the interest rate in %.
4. In cell B4, specify the given loan tenure (in years).
   
5. Once done, click on Enter. As a result, the EMI will be returned. Refer to the below image:
   

Factors affecting the EMI amount

EMI depends on several factors. Let's explore them in this section:

• Loan Amount: The Loan amount is directly proportional to the EMI; the higher the loan amount, the higher the EMI.
• Interest Rate: EMIs largely depend upon the interest rate as well; the higher the rate of interest, the higher the EMI.
• Credit Score: Though Credit score don't have any Emi impact on EMI, but yes a good credit score will help you to get loan instantly, and with a lower interest rate. Therefore always try to maintain an optimal credit score.
• Loan term: If you opt for a shorter time span, the EMI increases, but if you opt for extended loan tenure period, the EMI amount decrease.
• Down payment: If you pay high down payment, it will reduce the monthly EMI and the loan amount.
• Credit Score: If the credit score is high, the interest rate is reduced to cheaper and more EMI.
• Credit Score: If the credit score is high, the interest rate is reduced to cheaper and more EMI.
• High income: If the user has a higher income, he may prefer a more significant loan amount with a higher EMI.

Points to Remember while calculating loan EMI

To calculate the EMI without any error, follow the below points:

1. EMI Payment Method
   The EMI payment contributes significantly to the principal and interest. At the beginning of the loan tenure, the borrower pays more interest than the principal. When the loan tenure reaches its end period, the EMI will contribute more to the total principal than the interest amount.
2. The EMI amount should not exceed more than 40%
   It is advised that a person should not take any type of EMI Loan, which is more than 40% of the income. Whether the person is self-employed or working in an IT company, they have a fixed income per month. If the person leaves the job or the income of the business is reduced, the person can't able to pay the EMI loan if it increases more than 40%.
3. Change in Interest Rate
   The interest rate will change based on income, credit history, amount borrowed, age, etc. The credit history is monitored based on the early repayment, past or current loans of the credit card bills. Lenders and banking institutions will offer customers more friendly personal loan interest, which is low credit risk. It offers loans to those who repay their loans and bills correctly on time.
4. Convert Annual interest Rate to Monthly interest rate
   The EMI formula in Microsoft Excel always takes the monthly interest rate as an input. So always make sure to convert your annual interest rate to a monthly interest rate by dividing the annual rate by 12.
5. Cross check the values
   Always double-check the parameter values, before calculating the EMI using Excel EMI calculator, because a small change in value, and temper the result. Thus, giving your incorrect data and it can significantly affect your budget planning.

Finally, while calculating the personal loan EMI, make sure that the EMI won't impact the person's monthly income.

Conclusion

Creating an EMI calculator using the above formula, is very simple and easy to use. It will help you to effectively calculate your monthly EMI instalments. Thus, you can easily make your monthly budget in advance. Always make sure to recheck the values of your personal loan after using the EMI calculator in an Excel sheet.