ExcelInsider Team
When someone takes a loan from a financial institution, they need to calculate the EMI or Equated Monthly Installment (EMI) to plan how to settle the loan in time. It’s easy to create a calculation sheet in Microsoft Excel that calculates the EMI for you. There are essentially two ways to do this.
In this article, we will learn how to calculate EMI in Excel. We will show you exactly how to do it using functions and formulas that require the input and calculate the EMI with different loan amounts, interest rates, and loan tenures.
➤ Write this formula in your desired cell:
=PMT(B2/12,C2*12,-A2)
➤ Replace B2 with the interest rate, C2 with the loan tenure, and A2 with the loan amount.
➤ Press Enter and autofill other cells in your column.
If you are struggling with the calculation, we suggest that you learn the inner workings of the formula and other methods as well to understand the concept properly. Read the full tutorial to grasp everything there is to know about calculating EMI in Excel.
What Is an EMI?
When someone takes a loan from a bank or some other financial institution, the bank will eventually require the money back. However, it will not be possible for the borrower to pay the loan immediately. Taking the loan back as a lump sum amount in the future is riskier for the lender.
That is why the lender usually asks the borrower to give the money back in monthly installments. It is easier for the borrower to give money back monthwise, and the lender gets the assurance of receiving regular payments. This money that the borrower has to pay monthly is called the Equated Monthly Installment or EMI.
Using PMT to Calculate EMI in Excel
For this tutorial, we have a dataset of different loan amounts. The annual interest rates of those loans differ, the tenures differ, and we have to calculate the EMI of all of those loans. In this method, we will use the PMT function provided by Microsoft Excel itself. We don’t need to know any kind of mathematical formulas to use this method. Instead, we just insert the values, and Excel does the calculation for us. Follow the steps below:
➤ First, we are choosing another column to calculate the EMI. In column D, we write the formula after the heading (In the D2 cell):
=PMT(B2/12,C2*12,-A2)
➤Use the autofill handler of D2 to fill the rows till D11.
➥The second parameter is the loan tenure. We are multiplying C2 by 12 because we have to make a payment 12 times a year. The final parameter, -A2, is the loan amount, which is in negative format because otherwise, Excel will output results in negatives, as loan payments are cash outflows.
Calculating EMI Manually in Excel
Instead of using the function Microsoft Excel has given us, we can calculate the EMI the way we do it on paper. There is a formula for calculating EMI manually that we can use to calculate in Excel as well. Here is how to do that:
➤Write the following formula in the D2 cell (where we want the EMI to show up):
=(A2 * (B2/12) * (1 + (B2/12))^(C2*12)) / ((1 + (B2/12))^(C2*12) – 1)
➤Press Enter and autofill the rest of the cells in the column.
EMI = P * r/m * (1+r/m)n*m/(1+r/m)n*m -1
Here, P is A2, r/m is B2/12, and n*m is C2*12.
Frequently Asked Questions
Why do we calculate EMI?
Calculating EMI allows you to grasp how much money you would need per month to complete the loan payment in the loan tenure. You can prepare the amount of money you will pay each month by managing your finances if you know the EMI amount.
How to calculate the rate in Excel?
Use the following formula to do so:
=RATE(A1,B1,C1)
You need to replace A1 with the number of payments, B1 with the payment amount, and C1 with the present value. For the rate of EMI, you need to multiply A1 by 12, replace B1 with the EMI, and C1 with the loan amount.
What is cost-free EMI?
In general, there is a fixed interest rate for the EMI. However, there could be cases where the lender will give you a loan without any interest. EMI for that loan is called cost-free EMI. You would still have to pay the money back, but with no extra cost.
How to calculate no-cost EMI?
The formula for calculating no-cost EMI would be as follows:
=A1/B1/12
Here, A1 would be the loan amount, and B1 would be the loan terms. As there are no interest calculations, no complex formula is needed.
How to calculate loan interest?
The formula for simple loan interest is as follows:
=A1*B1*C1
Here, you will have to replace A1 with the loan amount, B1 with the rate of interest, and C1 with the loan terms.
Can I convert EMI to full payment?
You can contact the bank or your lender to do so. They will calculate the remaining loan amount you need to pay, and you can get rid of the loan sooner.
Wrapping Up
In this article, we have learned how to calculate EMI in Excel. The workbook used in this article is free for you to download. You can practice using the workbook, and if you have any questions, leave them in the comment section below. We will see you in another tutorial.
