Personal Loan EMI Calculator Excel Format (2 Suitable Examples)

When you take a big amount of loan for your personal problem or other problem, you have to repay it through a monthly installment process. The monthly installment can be defined as EMI. In Microsoft Excel, we can create a personal loan EMI calculator by using some built-in function or any conventional formula. This article will mainly focus on how to create a personal loan EMI calculator in Excel format. I hope you find this article really informative and gain lots of knowledge regarding this issue.

Overview of EMI

EMI means the equated monthly installment. The equated monthly installment is a payment that can be paid by the borrower to the lender at a specific date. EMI is also applied interest and principal each month so that over a certain period of time it can be paid off. So, when someone takes a loan for a personal purpose, he/she must have paid this over a certain period of time. But, when we consider the monthly payment that is the EMI. To calculate EMI, you can consider the following equation.

Here,

P = Original Loan Amount or Principal

R= Rate of Interest per Annum

N= The Number of Monthly Installments

How to Create Personal Loan EMI Calculator Format in Excel: 2 Suitable Examples

To create a personal loan EMI calculator format in Excel, we have found two different methods. One is basically by using the conventional method and the other is by using the PMT function in Excel. Both of the methods provide a smart solution to create an EMI calculator. If you consider the same loan amount, rate of interest, and loan tenure, then both of the methods give similar solutions. That means both of them are equally applicable.

1. Applying Conventional Formula

Our first method is basically utilizing the conventional formula of EMI. In this method, we will use the basic formula for EMI calculation and finally, produce an EMI calculator in Excel format. To do this, we take some values. By using these values, we calculate our EMI and at the same time, we produce the EMI calculator.

To apply the conventional formula, follow the following steps carefully.

Steps

As we have our principal amount, interest, and tenure, we need to calculate the monthly interest.
First, select cell C7.

As we know, the monthly interest is the ratio of the interest rate and the total monthly.
So, write down the following formula in the formula box.

=C5/C6

Then, press Enter to apply the formula.

Now, take this into a single worksheet where you would like to create the EMI calculator.
At first, create some row headers including EMI, interest, principal, and principal remaining.
Primarily, the principal remaining is the total amount of loan that you take.
After 12 months, the principal remaining must be zero. That’s the ultimate target.

To calculate the EMI in the first month, select cell C6.

To calculate the EMI, write down the conventional formula in the formula box.

=$I$4*$I$5/12*(1+$I$5/12)^$I$6/((1+$I$5/12)^$I$6-1)

Then, press Enter to apply the formula.

Now, the most important thing to remember is that you need to have the same EMI for every value. So, if you look at the formula, you will find all the cells are made constant by using the ($) sign.
Then, drag the Fill handle icon down the column up to cell C17. That is our EMI for that specific loan amount and the specific interest rate.

After that, we need to calculate the interest.
At first, select cell D6.

Then, interest is equal to the product of monthly interest and the previous principal remaining.
Write down the following formula

=$I$7*F5

After that, press Enter to apply the formula.

This interest will go to the bank. We need to calculate the principal amount which can be subtracted from the principal loan amount.
To calculate the principal, select cell E6.

The principal amount can be calculated by subtracting the interest value from EMI.
Then, Write down the following formula.

=C6-D6

After that, press Enter to apply the formula.

Now, we need to calculate the principal remaining.
Primarily, select cell F6.

Then, to calculate the principal remaining, we need to subtract the principal value from the previous principal remaining value.
Here, the first principal remaining value is equal to the amount of loan you take.
Then, write down the formula using the ABS function to avoid any negative value.

=ABS(F5-E6)

After that, press Enter to apply the formula.

After calculating the interest, EMI, and principal, you must have a clear idea that the interest and principal depend on the previous principal remaining.
So, at first, drag the Fill Handle icon of the interest column from D6 to D17.
Don’t be afraid, some of the interest will be zero because it depends on the previous principal remaining. But in the 3rd month, the principal remaining cell is blank.

Then, drag the Fill Handle icon of the principal column from cell E6 to E17.

After that, drag the Fill Handle icon of the principal remaining column from F6 to F17.

Then, you will see the change in the total dataset. Because when you have the previous principal remaining amount, the interest value can be calculated. By using the new interest the principal amount will be changed. This will happen in every cell until the principal remaining becomes zero.
Finally, we get the desired result when the principal remaining becomes zero which means the loan is paid perfectly.

After that, we need to calculate the total interest.
At first, select cell I9.

Here, we need to add all the interest paid in the 12 months using the SUM function.
Write down the formula.

=SUM(D6:D17)

Then, press Enter to apply the formula.

Then, we need to calculate the total amount paid which is the summation of total interest and principal.
Select cell I10.

Write down the following formula to calculate the total amount paid using the SUM function.

=SUM(I4,I9)

Then, press Enter to apply the formula.
There we have our final personal loan EMI calculator using the conventional method.

Now, if you change the principal and interest rate, the whole dataset will change according to it.
For example, if we take a principal loan of $5000 and an interest rate of 12%.
Then, we have the following output, See the screenshot.

2. Using PMT Function

Apart from the conventional method, we can use the PMT function to calculate EMI and then produce the EMI calculator. This process is easier than the previous method. To do this, we take some values. By using these values, we calculate our EMI and at the same time, we produce the EMI calculator.

To apply the conventional formula, follow the following steps carefully.

Steps

As we have our principal amount, interest, and tenure, we need to calculate the monthly interest.
First, select cell C7.

As we know, the monthly interest is the ratio of the interest rate and the total monthly.
So, write down the following formula in the formula box.

=C5/C6

Then, press Enter to apply the formula.

Now, take this into a single worksheet where you would like to create the EMI calculator.
Then, create some row headers including EMI, interest, principal, and principal remaining.
Primarily, the principal remaining is the total amount of loan that you take.
After 12 months, the principal remaining must be zero. That’s the ultimate target.

To calculate the EMI in the first month, select cell C6.

To calculate the EMI, write down the formula using the ABS and PMT functions.

=ABS(PMT($I$7,$I$6,$F$5,0,0))

Then, press Enter to apply the formula.

Now, the most important thing to remember is that you need to have the same EMI for every value. So, if you look at the formula, you will find all the cells are made constant by using the ($) sign.
Then, drag the Fill Handle icon down the column up to cell C17. That is our EMI for that specific loan amount and the specific interest rate.

After that, we need to calculate the interest.
At first, select cell D6.

Then, interest is equal to the product of monthly interest and the previous principal remaining.
Write down the following formula

=$I$7*F5

After that, press Enter to apply the formula.

This interest will go to the bank. We need to calculate the principal amount which can be subtracted from the principal loan amount.
To calculate principal, select cell E6.

The principal amount can be calculated by subtracting the interest value from EMI.
Then, Write down the following formula.

=C6-D6

After that, press Enter to apply the formula.

Now, we need to calculate the principal remaining.
Primarily, select cell F6.

Then, to calculate the principal remaining, we need to subtract the principal value from the previous principal remaining value.
Here, the first principal remaining value is equal to the amount of loan you take.
Then, write down the formula.

=F5-E6

After that, press Enter to apply the formula.

After calculating the interest, EMI, and principal, you must have a clear idea that the interest and principal depend on the previous principal remaining.
So, at first, drag the Fill Handle icon of the interest column from D6 to D17.
Don’t be afraid, some of the interest will be zero because it depends on the previous principal remaining. But in the 3rd month, the principal remaining cell is blank.

Then, drag the Fill Handle icon of the principal column from cell E6 to E17.

After that, drag the Fill Handle icon of the principal remaining column from F6 to F17.

Then, you will see the change in the total dataset. Because when you have the previous principal remaining amount, the interest value can be calculated. By using the new interest the principal amount will be changed. This will happen in every cell until the principal remaining becomes zero.
Finally, we get the desired result when the principal remaining becomes zero which means the loan is paid perfectly.

After that, we need to calculate the total interest.
At first, select cell I9.

Here, we need to add all the interest paid in the 12 months using the SUM function.
Write down the formula.

=SUM(D6:D17)

Then, press Enter to apply the formula.

Then, we need to calculate the total amount paid which is the summation of total interest and principal.
Select cell I10.

Write down the following formula to calculate the total amount paid using the SUM function.

=SUM(I4,I9)

Then, press Enter to apply the formula.

There we have our final personal loan EMI calculator using the PMT function.

Now, if you change the principal and interest rate, the whole dataset will change according to it.
For example, if we take a principal loan of $5000 and an interest rate of 12%.
Then, we have the following output, See the screenshot.

Things to Remember

To calculate the principal remaining, we need to subtract the principal rather than EMI because we have to pay the interest to the bank. The principal amount is calculated by subtracting interest from EMI.
While using the conventional method, you need to apply the bracket carefully. Otherwise, there will be a change in overall calculation.

Download Practice Workbook

Download the practice workbook.

Personal Loan EMI Calculator.xlsx

Conclusion

We have two different methods to create a personal loan EMI calculator in Excel format. The methods mainly utilize conventional formulas and the PMT function. Both of the methods give you a complete solution in terms of EMI. I hope you enjoy this article thoroughly. If you have any questions, feel free to ask in the comment box.

<< Go Back to EMI Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!