### Method 1 – Calculate EMI

We can use **the PMT function** to calculate EMI and then produce the EMI calculator. To do this, we take some values. With these values, we calculate our EMI, and at the same time, we produce the EMI calculator.

- We can easily calculate the monthly interest as we have our principal amount, interest, and tenure.
- Take a single worksheet where you would like to calculate the EMI.
- Create row headers, including EMI, interest, principal, and principal remaining.
- The principal remaining is the total amount of the loan that you take.
- After 12 months, the principal remaining must be zero. Thatâ€™s the ultimate target.

- Select cell
**C6**. - To calculate the EMI, write down the formula using the PMT function.

`=-PMT($I$5/12,$I$6,$I$4,0,0)`

** Note**

**Â**As you need to pay this money every month until you finish the loan payment, the PMT function will give you a negative result. Thatâ€™s why we use negative before the function to get a positive value.- Divide the interest rate by 12 to get the monthly interest rate. It will be calculated annually.

- 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 to cell**C17**. Our EMI for that specific loan amount and interest rate.

### Method 2 – Estimate Interest

- Select cell
**E6**. - Write down the following formula.

`=$I$5/12*F5`

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

- Drag the
**Fill Handle**icon down the column. - Donâ€™t worry about having zero because it will get updated when you estimate the other columns.

### Method 3 – Calculate Principal Amount from EMI in Excel

- Select cell
**D6**. - Write down the following formula.

`=C6-E6`

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

- Drag the
**Fill Handle**icon down the column. - This is not the final principal amount because estimating the remaining principal amount will give you the actual result.

### Method 4 – Calculate Principal Remaining Amount

- Select cell
**F6**. - Write down the following formula.

`=F5-D6`

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

- Drag the
**Fill Handle**icon down the column.

- Get actual values of interest and principal amount in EMI. Get the remaining loan amount as zero which implies the calculation is correct.

- It produces a calculator. The whole table will alter accordingly if you change the loan amount, interest rate, or duration.
- We set the loan amount to $60000 and the interest rate to 12%. We will get the following results.

## Things to Remember

- After splitting the principal and interest amount, we have to get the remaining loan amount as zero. Otherwise, the calculation is counted as incorrect.
- Before calculating EMI and interest amount, you have to make sure that you take the monthly interest by dividing the annual interest rate by 12.

**Download Practice Workbook**

Download the practice workbook below.

**Related Articles**

- How to Perform Actual 360 Interest Calculation in Excel
- How to Calculate GPF Interest in Excel
- How to Use Cumulative Interest Formula in Excel
- Perform Carried Interest Calculation in Excel
- How to Calculate Interest Between Two Dates in Excel
- Calculation of Interest During Construction in Excel

**<< Go Back to Calculate Interest In Excel | ****Excel for Finance**** | ****Learn Excel**