Money in bank in 24 hours

Apply Now

How to Calculate Your Personal Loan EMI Using Excel

  • Highlights

  • Calculate EMIs using the PMT function on Excel

  • Use this formula =PMT(RATE,NPER,PV,FV,TYPE)

  • These variables need to be computed & may lead to errors

  • Use the online EMI calculator to avoid manual errors

Among the particulars of a personal loan, the monthly instalments or EMIs are those that you should pay maximum attention to. They dictate your interest outgo throughout the tenor and the overall cost of your loan. This is why it is important to know how to calculate your EMIs well before you borrow, as it helps you plan your repayment effectively. Since it is a mathematical calculation, you can use the EMI formula in Excel and output the results manually. This process will require you to also calculate the values for a few variables and so, it does require a level of proficiency.

Check your cibil score for free
To know how to go about calculating your personal loan EMIs and set up a loan calculator in Excel, follow these steps.

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.

  • Allows you to plan your repayment in advance

  • Aids in identifying a loan amount that suits your capabilities

  • Helps compare loan offerings

  • Lets you decide on a suitable tenor

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.

DISCLAIMER:
The content of this document is meant merely for information purposes. The personal loan features mentioned in this article are subject to updation, completion, revision, verification and the same may change materially based on policy revisions. For more details, please visit our Personal Loan terms and conditions page here.

How would you rate this article

 Please let us know why?

What did you dislike?

What did you dislike?

What did you like?

What did you like?

What did you like?