### Method 1 – Calculate EMI

Use **the PMT function** to calculate EMI and then produce the EMI calculator, taking some values. With these values, calculate the EMI, and at the same time and produce the EMI calculator.

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

- Pay this money every month until you finish the loan payment; the PMT function will give you a negative result. 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.

- You need to have the same EMI for every value. Find all the cells that 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 the 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 the actual values of interest and principal in EMI. The remaining loan amount should be zero, which implies that the calculation is correct.

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

## Things to Remember

- After splitting the principal and interest amount, we must get the remaining loan amount to zero. Otherwise, the calculation is incorrect.
- Before calculating the EMI and interest amount, you must ensure 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 ****Excel for Finance**** | ****Learn Excel**