How to calculate your personal loan EMI using excel

Among the particulars of a personal loan, the monthly instalments or EMIs are those 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 essential 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, so it requires a level of proficiency.

Must Read: 4 top factors that led to the growth of NBFCs in India

Follow these steps to know how to calculate your personal loan EMIs and set up a loan calculator in 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

The interest rate applicable on loans. 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 by 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.

Additional Read: What is compound interest & simple interest: How to calculate it

5. TYPE

This variable denotes when the payment is due, and the values can be 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 precise 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 for any manual calculations. All you have to do is fill in the required fields, and the calculator will do the rest.

Additional Read: What is Annual Percentage Rate (APR): Understanding how APR is calculated

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
• Help compare loan offerings
• Let’s you decide on a suitable tenor

The above reasons are vital 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, but 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 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.