Personal loans are a popular choice for borrowers looking for a quick source of funds to meet their immediate financial needs. However, before applying for a personal loan, it is important to know the amount of Equated Monthly Installment (EMI) that you will have to pay. An EMI refers to the fixed amount that you have to repay to your lender each month until the loan has been fully repaid. Calculating your loan EMI before taking a personal loan can help you plan your finances better and ensure that you can repay your loan comfortably.
In this article, we will show you how to calculate your personal loan EMI using Excel. You can easily use the EMI formula in Excel to make this calculation accurately.
EMI Formula in Excel:
The EMI formula can be complicated, mainly due to the different variables involved. However, with the help of Excel, you can calculate your loan EMI with ease. Before we look into the steps involved, let us first understand the EMI formula:
EMI = [P x R x (1+R)^N]/[(1+R)^N-1]
Where,
P = Principal amount of the loan
R = Rate of interest per month
N = Number of monthly installments
Now, let us see how you can use this formula in Excel:
Step 1: Open a blank Excel sheet and enter the following details in the cells:
– Loan amount (principal) in cell A1
– Rate of interest (per annum) in cell A2
– Loan tenure (in months) in cell A3
Step 2: To calculate the monthly interest rate, divide the rate of interest (per annum) by 12.
=M2/12
(In this formula, M2 is the cell that contains the rate of interest).
Step 3: To calculate the total number of installments, multiply the loan tenure (in years) by 12.
=C3*12
(In this formula, C3 is the cell that contains the loan tenure)
Step 4: Calculate EMI formula in Excel, using the PMT function as follows:
=PMT(B2,B3,-B1)
(In this formula, B2, B3, and B1 are the cells that contain the monthly interest rate, total number of installments, and loan amount, respectively)
Step 5: The result displayed will be your monthly EMI payment.
Let us understand each formula step by step.
Step 1: Entering the loan, the rate of interest, and the tenure
To calculate your loan EMI in Excel, you need to start by entering the following details in the appropriate cells:
Loan amount (principal) – In this example, let’s assume that you want to take a personal loan of $10,000, which is entered in cell A1.
Rate of interest (per annum) – The rate of interest usually ranges from 10% to 20% per annum. Assume that you want to take a personal loan with an interest rate of 12% per annum. This value is entered in cell A2.
Loan tenure (in months) – Personal loans usually have a repayment tenure ranging from 12 to 60 months. Assume that you want to repay your loan in 36 months, this value is entered in cell A3.
Step 2: Calculate the monthly interest rate
Now that you have entered the details of your loan amount, the rate of interest, and the tenure, you need to calculate the monthly interest rate to calculate the EMI. You can calculate it by dividing the annual interest rate by 12.
For example, if the annual interest rate is 12%, the monthly interest rate would be:
=12%/12
=1%
You can enter this formula in an empty cell, say cell A4, and use the percentage format to display it as a percentage.
Step 3: Calculate the total number of installments
The next step is to calculate the total number of installments or loan tenure in months. You have already entered your loan tenure in years, so you need to multiply it by 12 to convert it into months.
For example, if your loan tenure is 3 years, the total number of installments would be:
=3*12
=36
You can enter this formula in an empty cell, say cell A5.
Step 4: Calculate the EMI using the PMT function
You are now ready to calculate your loan EMI using the PMT function in Excel, also known as the EMI calculator. The PMT function calculates the periodic payment required to repay a loan, which in this case is the loan EMI. The PMT formula in Excel takes the following arguments:
=PMT(rate, nper, pv, [fv], [type])
Here,
Rate – The monthly interest rate calculated in Step 2.
Nper – The total number of monthly installments calculated in Step 3.
Pv – The present value or loan amount entered in cell A1.
Type – [Optional] This argument specifies whether payments are due at the beginning (0) or at the end of the period (1) and is usually set to 0 or 1.
For example, enter the following PMT function in an empty cell, say cell A6:
=PMT(A4,A5,-A1)
This formula calculates the periodic payment required to repay the loan principal of $10,000 at a monthly interest rate of 1% over 36 months. The result displayed in cell A6 will be your monthly EMI payment, which is $332.14.
Step 5: Displaying the EMI as currency
The final step is to format the result displayed in cell A6 in currency format. Select the cell and click on Format cells. In the Format cells dialog box, go to the Number tab and select Currency. You can now see your loan EMI displayed in currency format.
Conclusion:
Calculating your personal loan EMI using Excel is simple and easy. By knowing the EMI amount, you can plan your finances better and ensure that you can repay your loan comfortably. Excel is a versatile tool that helps you customize formulas, apply functions to cells, and display results in different formats. This article shows you how to calculate your loan EMI using Excel in just a few simple steps.
