We’ve taken a *Loan Amount* of $100,000 for 1-year tenure at an 8% interest rate. The reimbursement system is monthly. We have to repay the loan as **EMI**. We made a prepayment of $5,000 while taking the loan.

We want to calculate all these entities in the column header of the blank cells. These are the *EMI, Interest, Principal, *and the *Remaining Principal *amount.

### Step 1 – Calculate the EMI Amount of Home Loan Calculator with Prepayment Option in Excel Sheet

The *EMI*, or *Equated Monthly Instalment*, is one of the equally divided monthly payments that must be settled in order to pay off an outstanding loan within a predetermined period of time.

- Select cell
**F9**and use the formula below, then hit the**Enter**button.

`=D4-D5`

**D4** and **D5** represent the cell references of *Loan Amount* and *Prepayment* respectively.

This *Principal Remaining* amount is at the beginning of the first month. The *EMI* will be calculated based on this amount.

- Use the formula below in cell
**C10**and hit**Enter.**

`=PMT($F$6/$F$5,$F$4*$F$5,$F$9)`

**F6** is the *Interest Rate*, **F5** is the *Payments Per Year*, **F4** is the *Tenure* and **F9** is the *Remaining Principal* amount. We used the dollar **($) **sign for the absolute reference of a cell. In this formula, we used **the PMT function,** which determines the loan payment using fixed payments and a fixed interest rate.

We’ll convert this into a positive value.

- Select cell
**C10**and edit the formula:

`=ABS(PMT($F$6/$F$5,$F$4*$F$5,$F$9))`

- Use the
**Fill Handle**tool and drag it down to cell**C21**to get the same*EMI*amount as other months.

### Step 2 – Count the Interest Per Month

- Select cell
**D10**and use the formula below, then hit the**Enter**button.

`=ABS(IPMT($F$6/$F$5,B10,$F$4*$F$5,$F$9))`

We used the same arguments and cell references as in Step 1. The new thing that hasn’t been used before is the cell reference **B10**. It denotes the number of *Months*. For this particular cell, it’s the first month.

**Read More: **How to Create Loan Calculator with Extra Payments in Excel

### Step 3 – Determine the Paid-up Capital Per Month

- Select cell
**E10**and use the formula below, then hit the**Enter**button.

`=C10-D10`

The *EMI* consists of the monthly *Interest* and the monthly paid-up *Principal* amount.

### Step 4 – Determine the Remaining Principal Amount

- Select cell
**F10**and use the formula below, then hit the**Enter**button.

`=F9-E10`

**F9** and **E10** represent the *Principal Remaining* of last month and the *Principal* of the present month, respectively.

- Use the
**Fill Handle**tool and drag it down to cell**F21**.

### Step 5 – Evaluate the Total Amount to be Paid

- Select cell
**D6**and use the formula below, then hit the**Enter**button.

`=SUM(C10:C21)`

**The SUM function** sums up the *EMI*s in the range **C10:C21**.

## Advantages of Making a Prepayment

With a prepayment, the monthly paid-up *Principal* is increasing continuously, because our *EMI* is constant over the period of 12 months.

Without making any kind of prepayment, our loan status would be like the image below.

The *Total Amount to be Paid* here is greater than with a $5,000 *Prepayment*.

**Download the Template**

## Related Articles

**<< Go Back to Loan Calculator | Finance Template | Excel Templates**