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.
To know how to go about calculating your personal loan EMIs and set up a loan calculator in Excel, follow these steps.
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
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.
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
This variable denotes the principal loan amount or the present value. Here, you will need to add the amount you intend to borrow.
This variable denotes the future value or balance remainder after the last payment. You may omit it and it will not affect the results.
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.
What did you dislike?
What did you dislike?
What did you like?
What did you like?
What did you like?