How to Calculate Your Personal Loan EMI Using Excel

How to Use the EMI Formula Derivation in Excel

The function to calculate EMI on Excel is PMT and not EMI. Keep this in mind and follow this guide.

The formula you need to use is:
=PMT(RATE,NPER,PV,FV,TYPE)

Besides PMT, all the other elements in this formula are variables and need assigned values. These correspond to your loan particulars and must be entered carefully to get an accurate result. Here is a detailed breakdown of these variables.

Additional Read: CIBIL Score Calculation

1. RATE

Interest rate applicable on the loan. The value for this variable must be converted by dividing the rate by 12. For example, 12% annual interest is 12%/12 = 1% or 0.01.

2. NPER

This variable denotes the number of EMIs applicable for the tenor. You can get the value by multiplying the number of years into 12.
For example, for a loan of 5 years, NPER is 5X12

3. PV

This variable denotes the principal loan amount or the present value. Here, you will need to add the amount you intend to borrow.

4. FV

This variable denotes the future value or balance remainder after the last payment. You may omit it and it will not affect the results.

5. TYPE

This variable denotes when the payment is due and the values can be either a 0 or 1. If the payment is due at the end of the period, then TYPE=0. However, for EMI payments at the start of the month, TYPE=1.

Consider the following example. For a sanction of Rs.5 lakh with an interest rate of 14.60% taken over a tenor of 48 months, the values for the excel formula are:
=PMT (0.01216666, 48, 500000, 0, 0)

These will yield the result-13,814, which is the accurate EMI amount.

Additional Read: Improve your CIBIL Score

You may have noticed that using the EMI calculator for Excel leaves room for error and miscalculations. These can negatively affect your ability to make sound financial decisions and possibly lead you to either miss out on a good offering. Thus, calculating an accurate figure using the EMI calculator formula in Excel may not always be the best approach. Avoid this risk by using an online personal loan EMI calculator instead.

This handy, free-to-use tool automatically computes your interest outgo, total debt and monthly instalments without the need of any manual calculations. All you have to do is fill in the required fields and the calculator will do the rest.

Now that you’re aware of a sure-fire way to access this key piece of information, take a look at a few important reasons why you should know your EMIs beforehand.

All of the above reasons are key to borrowing efficiently and highlight the importance of knowing your instalments beforehand. One way to ensure your loan terms are pocket-friendly is to take a competitive loan such as the Bajaj Finserv Personal Loan. Not only do you get access to a nominal interest rate on a collateral-free sanction of personal loan up to Rs.25 lakh, you also enjoy flexible repayment and other value-added features. You can repay your loan on a tenor of 12 to 60 months and enjoy the benefit of an instant approval.

Further, you can have the entire sanction disbursed to you within 24 hours, making it a viable option for emergencies. Their Personal Loan is also easy to qualify for and completely transparent, thereby safeguarding you from any hidden charges.