How To Calculate Loan EMI on Excel: Formula & Important Factors

Personal Finance

Equated Monthly Installment (EMI) is the payment you make towards your loan each month. EMIs may take a toll on your monthly budget if you do not plan and calculate it before deciding the monthly instalment amount. Thankfully, there are many ways to calculate EMIs easily and maintain your EMI record. This post discusses how to calculate EMI in excel. Read on!

What is a Personal Loan EMI Calculator? 

Navi provides a personal loan EMI calculator that you can use to determine your monthly instalments accurately. The EMI calculator is a simple financial tool that applies the standard EMI formula once you have entered the requested values. This includes the interest rate, the loan amount, and the tenure of the loan.

Calculating EMI on Excel Formula

An EMI calculator excel sheet applies the following formula.

=PMT(RATE,NPER,PV,FV,TYPE)

All you have to do is choose a cell and enter the formula to calculate EMI in excel.

In this formula,

• R stands for the Rate of Interest. Make sure you enter the monthly rate of interest when using the loan EMI calculator excel. The rate of interest provided by the bank or financial institution is usually for the whole year. Divide this value by 12 and then enter.
• NPER is the tenure of the loan or the number of instalments
• PV is the current principal amount payable.
• FV is the future value or the balance after making the previous payment.
• Type is a numerical value. It can be 0 or 1. In case your payment is due at the beginning of the month, set the type as 1. If it is due at the end of the month, set the type as 0.

Manual Calculation of  EMI

Financial institutions use a simple EMI calculation formula to estimate the monthly payments to make towards a loan.

The formula is

EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1]

In this EMI calculation formula, 

P is the principal amount or the actual loan amount.

R is the rate of interest on the loan that you have applied for

N is the loan repayment tenure.

You have borrowed a loan amount of Rs.100000 for 12 months at a 10% interest rate. Remember that the rate of interest provided in your bank sanction letter refers to the annual rate. After applying this formula, the EMI payable each month is Rs. 8792.

Calculating the EMI manually is a challenging task. You can request your loan provider to give you a detailed amortization chart or schedule. This chart gives you the exact monthly payment for your loan over the loan tenure. It includes important information like EMI payable, interest rate, tenure of repayment, and the outstanding loan amount. You will notice that the EMI gradually reduces as you make regular payments. This is because the principal amount also reduces over a period.

This information is essential when you plan to foreclose a loan.

You have alternative options like using EMI calculators online or applying the EMI calculation formula in excel. The latter is a handy and straightforward option that lets you keep detailed accounts of your monthly instalments.

How to Use an Online Personal Loan EMI Calculator? 

When you apply for a loan, you can use an EMI calculator to check if the plan is affordable for you. A slider lets you set a value of the EMI that is comfortable for you to repay. The loan amount and the tenure are adjusted accordingly. The interest rate, however, is standardized as per the eligibility criteria of the borrower.  Once these values have been entered, your EMI is calculated as per the mathematical formula to give you an accurate figure.

Also Read: Home Loan Repayment Calculator: Best Ways To Repay Home Loans

Factors Affecting EMI 

As you can see in the EMI calculator excel sheet, different variables must be considered when calculating the EMI. The EMI may increase or decrease depending on:

• Loan Amount

  This is the principal amount on which you will be paying interest. The higher the loan amount, the higher the EMI.

• Repayment Tenure

  When you use the EMI calculator excel, you have to enter the number of instalments or the tenure. The longer the tenure, the lesser the EMI. However, you will be making a higher payment towards the interest. Therefore, it is highly recommended that you try to repay the loan at the earliest.

Also read: How to plan your loan repayment: Different loan repayment options

• Interest Rate

  The interest rate varies from one person to another. This depends upon several eligibility criteria, including income, credit score, the security provided, and more.

• Margin Amount

  The margin amount or down payment is a part of the loan amount that the borrower must pay. The higher the margin, the lower the principal amount that the EMI is calculated on. Therefore, the total value of the EMI reduces.

Get Instant Personal Loans on Navi

Download the Navi app on Android or iOS to use our easy EMI calculator tool. You can even customize your loans based on the EMI that you can repay each month comfortably.

Things To Know About Calculating EMI 

• Remember that the EMI calculator excel sheet does not take factors like prepayment charges, processing fees, or late payment fees into account. There is also room for human error.
• Whenever you are calculating EMI, make sure that you take the monthly interest rate into account.
• In case there is any change in the interest rate, make sure you calculate your EMI accordingly. The interest rate is subject to change as per the eligibility criteria and government regulations from time to time.
• Do not set an EMI amount that’s more than 40% of your monthly income. This will ensure that paying EMI does not become a burden. You must keep your monthly expenses in mind and enough margin for emergency expenses before deciding your EMI amount.

Also Read: What Is No Cost EMI And How Does it Work?

Final Word

An EMI calculator excel sheet is a useful tool, no doubt. However, when it comes to accuracy, there may be some discrepancies. Therefore, you should get in touch with the lending institution or use a simple online EMI calculator for better results.

Before you go…

Looking for instant 🚀 personal loans 24*7 anywhere, anytime? Get personal loans up to ₹20 lakh starting at 9.9% p.a. Install the Navi app now!

Disclaimer: This article has been prepared on the basis of internal data, publicly available information and other sources believed to be reliable. The information contained in this article is for general purposes only and not a complete disclosure of every material fact. It should not be construed as investment advice to any party. The article does not warrant the completeness or accuracy of the information, and disclaims all liabilities, losses and damages arising out of the use of this information. Readers shall be fully liable/responsible for any decision taken on the basis of this article. 

Explore Personal Loan Offers in Your City