**Example 1 – Applying Conventional Formula**

We have our principal amount, interest and tenure to calculate the monthly interest.

**Steps:**

- Select cell
**C7**.

- The monthly interest is the ratio of the interest rate and the total monthly.
- Enter the following formula in the formula box.

`=C5/C6`

- Press
**Enter**to apply the formula.

- Take this into a single worksheet where you would like to create the EMI calculator.
- Create some row headers including EMI, interest, principal and principal remaining.
- 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, enter 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)`

- Press
**Enter**to apply the formula.

- The most important thing to remember is that you need to have the same EMI for every value. If you look at the formula, you will find all the cells are made constant by using the
**($)Â**sign. - 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.

- To calculate the interest, select cell
**D6**.

- Interest is equal to the product of monthly interest and the previous principal remaining.
- Enter the following formula.

`=$I$7*F5`

- 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.
- Enter the following formula.

`=C6-D6`

- Press
**Enter**to apply the formula.

- Calculate the principal remaining.
- Select cell
**F6**.

- To calculate the principal remaining, we need to subtract the principal value from the previous principal remaining value.
- The first principal remaining value is equal to the amount of loan you take.
- Enter the formula using
**the ABS function**to avoid any negative value.

`=ABS(F5-E6)`

- Press
**Enter**to apply the formula.

- After calculating the interest, EMI and principal, the interest and principal depend on the previous principal remaining.
- Drag the Fill Handle icon of the interest column from
**D6**to**D17**. - 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.

- Drag the Fill Handle icon of the principal column from cell
**E6**to**E17**.

- Drag the Fill Handle icon of the principal remaining column from
**F6**to**F17**.

- 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.
- We get the desired result when the principal remaining becomes zero which means the loan is paid perfectly.

- To calculate the total interest, select cell
**I9**.

- Add all the interest paid in the 12 months using
**the SUM function**. - Enter the following formula.

`=SUM(D6:D17)`

- Press
**Enter**to apply the formula.

- To calculate the total amount paid which is the summation of total interest and principal, select cell
**I10**.

- Enter the following formula to calculate the total amount paid using the
**SUMÂ**function.

`=SUM(I4,I9)`

- Press
**Enter**to apply the formula. - We now have our final personal loan EMI calculator using the conventional method.

- If you change the principal and interest rate, the whole dataset will change according to it.
- If we take a principal loan of
**$5000**and an interest rate of**12%**. - We have the following output.

**Read More: **Home Loan EMI Calculator with Reducing Balance in Excel

**Example 2 – Using PMT Function**

**Steps**

- Select cell
**C7**.

- The monthly interest is the ratio of the interest rate and the total monthly.
- Enter the following formula.

`=C5/C6`

- Press
**Enter**to apply the formula.

- Take this into a single worksheet where you would like to create the
**EMI.** - Create some row headers including EMI, interest, principal and principal remaining.
- The principal remaining is the total amount of loan that you take.
- After 12 months, the principal remaining must be zero.

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

- To calculate the EMI, enter the formula using the
**ABS**and**PMTÂ**functions.

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

- Press
**Enter**to apply the formula.

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

- Calculate the interest.
- Select cell
**D6**.

- Interest is equal to the product of monthly interest and the previous principal remaining.
- Enter the following formula

`=$I$7*F5`

- 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.
- Enter the following formula.

`=C6-D6`

- Press
**Enter**to apply the formula.

- Calculate the principal remaining.
- Select cell
**F6**.

- To calculate the principal remaining, we need to subtract the principal value from the previous principal remaining value.
- The first principal remaining value is equal to the amount of loan you take.
- Enter the following formula.

`=F5-E6`

- Press
**Enter**to apply the formula.

- After calculating the interest, EMI, and principal, the interest and principal depend on the previous principal remaining.
- Drag the Fill Handle icon of the interest column from
**D6**to**D17**. - 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.

- Drag the Fill Handle icon of the principal column from cell
**E6**to**E17**.

- Drag the Fill Handle icon of the principal remaining column from
**F6**to**F17**.

- 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.
- We get the desired result when the principal remaining becomes zero which means the loan is paid perfectly.

- To calculate the total interest, select cell
**I9**.

- Enter the following formula.

`=SUM(D6:D17)`

- Press
**Enter**to apply the formula.

- To calculate the total amount paid which is the summation of total interest and principal, select cell
**I10**.

- Enter the following formula to calculate the total amount paid using the
**SUMÂ**function.

`=SUM(I4,I9)`

- Press
**Enter**to apply the formula.

- You will get the personal loan EMI calculator using the
**PMT**function.

- If you change the principal and interest rate, the whole dataset will change according to it.
- If we take a principal loan of
**$5000**and an interest rate of**12%**. - We have the following output.

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

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

## Related Articles

- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- Reducing Balance EMI Calculator in Excel Sheet
- How to Create Reverse EMI Calculator in Excel

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