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.

## Download Practice Workbook

Download the practice workbook.

## 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

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

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 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 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.

**Read More:** **SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option**

### 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**.

`=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 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.

**Read More:** **How to Calculate EMI for Bike Loan in Excel (2 Easy Methods)**

## 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.

## 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, and donâ€™t forget to visit our** ExcelDemy** page.