Have you ever taken a loan in your life? Do you ever wish to take a loan? Whether it is a personal loan, a house loan, or a car loan, one of the most important things that a person has to understand before taking a loan is the EMI. EMI is the way to repay the loan systematically. Meaning equated monthly payments gives you an option to pay every single month with a fixed amount of money, including two interest and principal components. However, in the beginning, the interest component contribution is more in the EMI, whereas in the end, the principal component dominates the contribution of EMI.
In this article, I am going to explain in detail how EMI works and the easiest way to calculate an EMI for any kind of loan. Understanding this calculation will help you reduce the risk and uncertainty of deciding whether you have the capability to afford the loan.
What is EMI?
Basically, the EMI is made of two components: one is the principal repayment, the other is the interest payment. Every year you pay an equated monthly instalment for your loan, in which the principal component goes towards the principal amount of the loan, and the rest goes towards the Interest acquired.
Why should you use Excel for EMI calculation?
Many people are thinking about checking the EMI payment on various insurance and loan websites, where the calculator doesn’t show exact information. On the other hand, when you use Excel, you have more control over input and output.
For example, you can change the loan amount instantly, adjust interest rates, and change 10 year to see whether the interest or the principal component changes every month. In total, you can build your own financial model based on your own expectations.
This is exactly what companies and finance professionals do.
EMI formulas in Excel
EMI is primarily calculated by a built-in function in Excel called PMT.
In this formula, there are three components.
PMT(rate,nper,pv)
Where:
Rate = Interest rate per month
Nper = Number of months
PV = Loan amount
Example:
A letter assumes that you have taken a loan of 5 million, at an annual interest rate of 10% per annum, to be repaid in five years of time.
Loan Amount = 5,000,000
Interest rate = 10%
Loan tenure = 5 Years
While using the PMT function, make sure that you convert the yearly interest rate to a monthly rate and the duration to the number of months.
PMT(10%/12,5*12,5000000)
This gives you a value that you have to pay every single month to clear the loan in 5 years. The magic here is that you have a constant payment every single month.
If you are a finance student working towards a finance certification and dreaming of getting into the finance industry, do not stop at EMI; try to build an EMI amortisation schedule in Excel, which gives you a breakdown of every single month, including interest and principal repayment, including opening and closing balance.
If you want to learn more about EMI, please watch the video below.
Common mistakes need to be avoided while calculating EMI.
Many students calculate EMI based on an annual interest rate, which gives them a wrong projection of EMI. Instead, convert the annual interest to a monthly interest rate.
Moreover, people sometimes do not convert the number of years to the number of months, as we are calculating equated monthly payments, so you should make sure that every input is based on every month.
Do not try to use a negative sign for the loan amount because taking a loan is a positive amount of money coming in. As a result, PMI is projected negatively, as it is money going out.
Learning to calculate EMI in Excel is a very basic but very powerful thing to begin with to learn finance in Excel. However, most people do not learn practical concepts; they limit themselves to bookish knowledge and theoretical concepts.
If you are an aspiring finance graduate, you must convert your theoretical knowledge into practical knowledge by using Excel.
This makes you a better decision maker and helps you understand the loan concept. As this is a core concept throughout the finance industry.
If you want to learn Excel from basics to advanced (with real-world finance examples), I conduct live training sessions where I teach step-by-step.
You can also check more practical Excel tutorials on my page.
