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 290 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.

Zaroorat aapki. Personal Loan Humara
Apply Now


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 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

Zaroorat aapki. Personal Loan Humara
Apply Now

Disclaimer: The information contained in this post is for general information purposes only. IIFL Finance Limited (including its associates and affiliates) ("the Company") assumes no liability or responsibility for any errors or omissions in the contents of this post and under no circumstances shall the Company be liable for any damage, loss, injury or disappointment etc. suffered by any reader. All information in this post is provided "as is", with no guarantee of completeness, accuracy, timeliness or of the results etc. obtained from the use of this information, and without warranty of any kind, express or implied, including, but not limited to warranties of performance, merchantability and fitness for a particular purpose. Given the changing nature of laws, rules and regulations, there may be delays, omissions or inaccuracies in the information contained in this post. The information on this post is provided with the understanding that the Company is not herein engaged in rendering legal, accounting, tax, or other professional advice and services. As such, it should not be used as a substitute for consultation with professional accounting, tax, legal or other competent advisers. This post may contain views and opinions which are those of the authors and do not necessarily reflect the official policy or position of any other agency or organization. This post may also contain links to external websites that are not provided or maintained by or in any way affiliated with the Company and the Company does not guarantee the accuracy, relevance, timeliness, or completeness of any information on these external websites. Any/ all (Gold/ Personal/ Business) loan product specifications and information that maybe stated in this post are subject to change from time to time, readers are advised to reach out to the Company for current specifications of the said (Gold/ Personal/ Business) loan.

Most Read

Franking and Stamping: What’s the difference?
14 Aug,2017 03:45 IST
Like 2909 2909 Likes
Personal Loan With Low CIBIL Score
21 Jun,2022 09:38 IST
Like 575 575 Likes
Difference Between 24 Karat and 22 Karat Gold
8 Dec,2022 09:26 IST
Like 165 165 Likes
What is equitable mortgage home loan?
8 Mar,2019 05:15 IST
Like 3204 3204 Likes

Get Personal Loan