How To Calculate Personal Loan EMI Using Excel Formula?

Are you unsure if your lender is charging you a fair EMI? You can now easily calculate your personal loan emi using an excel formula. Read to know the steps here!

21 Jun,2022 10:31 IST 147 views
How To Calculate Personal Loan EMI Using Excel Formula?

When you need cash and don’t know how to fulfil your financial needs, you can reach out to a financial institution to apply for a personal loan with repayment through EMI (equal monthly installments). If you are wondering whether a personal loan is a right choice for your financial needs, you must estimate your EMI and assess if your current financial status and monthly income allow you to shell out that much money every month. This article details how to calculate these installments using an EMI formula in excel.

Calculating EMI using Excel

There are many ways to calculate the EMI for a personal loan. You will need to know the principal amount (i.e. your borrowing), the tenure of the loan (in months/years) and the interest rate charged by the financial institution.
While calculating using the EMI formula in excel, you must remember that the function for EMI is PMT. The formula that you will need to use is as follows:
        =PMT (RATE, NPER, PV, FV, TYPE)


This refers to the interest applicable on the loan. The value of the interest rate must be calculated by dividing the rate by 12. For example, a 15% interest is equal to 15%/12 = 1.25% = 0.0125


This refers to the number of EMI payments. You may also consider this as the number of months in your tenure. For example, if the tenure is 3 years, NPER will be 3*12 = 36.


This refers to the principal value to be repaid. You need to enter the amount that you intend to borrow here.


This refers to the future value or the remaining value left after the last payment. Since you are to repay the loan completely, you may enter 0 or leave it blank.


This value depends on the time of EMI payment. If you need to pay the EMI at the start of the month, the value for type would be 1. If it is to be paid at the end of the month, enter 0.
Let’s consider an example. For a loan of Rs 1,00,000 with a tenure of 2 years and an interest rate of 12%, the formula to be entered would be as follows:
                 =PMT (0.01,24,100000,0,0)
After entering this formula in excel, you will get the value 4,707. This is the EMI value for the specified loan.

Why Should You Calculate Your EMI?

Calculating your EMI before applying for a personal loan has various benefits:
  • You reduce your chances of default as you know exactly how much you need to pay every month.
  • It allows you to compare personal loans from various financial institutes.
  • You can choose your loan amount and tenure according to your requirement and repayment capability.
  • You can manage your loan better which will eventually lead to better credit history.

2 Things You Should Know About Loan EMI Calculation

1. The Principal Amount And Interest Are Both Included In Your EMI

This means that after successful payment of the EMI for the entire tenure, you will not have to pay any additional interest. The EMI calculation is structured in a way where the payable interest is already included in your monthly payments.

2. Your EMI Amount Should Not Be More Than 40% Of Your Monthly Income

A financially prudent borrower should not take a loan where the EMI is more than 40% of their monthly income. This is because with a fixed income there are other financial obligations and there may be unpredictability in income. Therefore, to be better safe than sorry, ensure that your EMI is not more than 40% of your monthly income.

Personal Loan with IIFL

IIFL Finance’s Personal Loan of up to 5 Lakh is designed to ensure that you get express disbursal into your account in just a few hours. You can take a personal loan for domestic or international vacations, weddings, buying the latest gadget, pursuing higher education, purchasing a vehicle or even home renovation. IIFL personal loan will help you realize your dream at an express speed in 3 quick steps.


Q.1 What Is The Excel Formula For EMI Calculation?
Ans. The formula that you will need to use is as follows:
              =PMT (RATE, NPER, PV, FV, TYPE)
              NPER = Total Number Of Payments
                   PV = Principal Value
                    Fv = Face Value
In the above formula, to calculate the PMT, you will need to assign values to all the other variables.

Q.2 How To Calculate The NPER From The Interest Rate?
Ans. To calculate the NPER, divide the interest rate by 12 and convert it into decimals by dividing the number by 100. For example, If the interest rate is 14%, the NPER will be:
                   14%/12 = 1.167% = 0.0116

Most Read

Franking and Stamping: What’s the difference?
14 Aug,2017 03:45 IST
Like 2020 2020 Likes
How To Get A Personal Loan For A Low CIBIL Score?
21 Jun,2022 09:38 IST
Like 157 157 Likes
What is equitable mortgage home loan?
8 Mar,2019 05:15 IST
Like 2291 2291 Likes
What Is The Meaning Of E-Mandate For Mutual Funds?
23 Oct,2018 04:15 IST
Like 1353 1353 Likes

Get in Touch