In this article, we will learn to create a **home loan EMI calculator with a prepayment option in an Excel sheet**. Many people take home loans while buying their house and they can track the loan with an **EMI** calculator very easily. Today, we will demonstrate step-by-step procedures to make a home loan **EMI calculator with the prepayment option**. So, without any delay, let’s start the discussion.

## Download Calculator

You can download the home loan **EMI** with the prepayment option calculator from here.

## What Is Home Loan EMI?

The **EMI **stands for **Equated Monthly Installment**. **EMI **is the amount of money that you need to pay for the home loan you have taken. The formula of **EMI **can be written as:

`EMI=[PxRx(1+R)^N]/[(1+R)^N-1]`

Here,

- P = Principal Amount
- R = Rate of Interest
- N = Repayment Periods (Total Number of Months)

So, we can see that **EMI **depends on the interest rate (**R**), tenure years (for how many years you will take the loan(**N**)), and also on your income.

The **EMI **has two main parts: **Principal Amount **and **Interest Amount**. The principal amount is less initially but increases with tenure. And the interest amount is high at the beginning and decreases with time. For this reason, you should try to make pre-payments during the initial months.

## Step-by-Step Procedures to Make Home Loan Calculator in Excel Sheet with Prepayment Option

### STEP 1: Create Dataset for Home Loan EMI

- First of all, you need to create a dataset.
- In the dataset, create a section for
**Principal**,**Interest %,**and**Tenure in Years**. - After that, create columns for
**Months**,**EMI**,**Principal**,**Interest**,**Principal Remaining**, and**Pre-Payments**like the picture below.

- Also, create a section for all the total amounts including
**Total Interest**,**Updated Interest**,**Total Amount**,**Total Savings**, and**Updated Amount**.

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

### STEP 2: Insert Home Loan with Interest

- Secondly, type the amount of the loan in
**Cell C4**, the percentage of interest in**Cell E4**, and tenure in years in**Cell G4**. - In our case, the home loan is
**$4,000,000**, interest is**7%**, and tenure is**20**years.

**Read More: ****Excel Simple Interest Loan Calculator with Payment Schedule**

### STEP 3: Fill Down Months and Principal Remaining Amount

- In the third step, we will fill down the
**Months**column at first and then, the**Principal Remaining Amount**. - We need to enter
**20*12 = 240**months in our sheet starting from**0**. Here,**20**is the**Tenure in Years**. - To fill down months, select
**B7**and type**0**. - After that, drag the
**Fill Handle**down to**Cell B246**.

- In the following step, select
**Cell F7**and type:

`=C4`

- Press
**Enter**to see the result. - It will show the value of
**Cell C4**which is**$4,000,000**.

### STEP 4: Apply Necessary Formulas in Dataset

- In the fourth step, we need to apply the necessary formula in the dataset.
- First of all, we will apply the
**EMI formula**. - For that purpose, select
**Cell C7**and type the formula below:

`=ABS(PMT(E4/12,G4*12-B7,F7))`

In this formula, we have used **the PMT function **to calculate the **EMI**. Also, took the absolute value of this result with **the ABS function**.

- The first argument (
**E4/12**) is the**Monthly Interest Rate**. - The second argument (
**G4*12-B7**) denotes the**Number of Months Remaining**. - And the third argument (
**F7**) represents the**Remaining Principal Amount**.

- Hit
**Enter**to see the result.

- After that, select
**Cell E7**and type the formula of**Interest**amount:

`=$E$4/12*F7`

In this formula, we have divided the **Yearly Interest Rate **by **12 **to get the **Monthly Interest Rate**. And then, multiplied it by the **Remaining Principal Amount **to get the **Interest Amount**.

- Press
**Enter**to see the result.

- To find the
**Principal**amount, type the below formula in**Cell D7**:

`=C7-E7`

- Again, hit
**Enter**to see the result.

- Now, to calculate the
**Principal Remaining**, type the below formula in**Cell F8**:

`=F7-D7-G8`

To get the **Remaining Principal Amount**, you need to subtract the **Pre-Payments **and **Paid Principal **from the **Previous Remaining Principal**.

- Once again, press
**Enter**to see the result.

- The
**EMI**will be fixed for the rest of the months. - So, type the formula below in
**Cell C8**:

`=$C$7`

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

**Similar Readings**

**How to Make Two Wheeler Loan EMI Calculator in Excel (2 Easy Ways)****Personal Loan EMI Calculator Excel Format (2 Suitable Examples)****How to Calculate EMI for Bike Loan in Excel (2 Easy Methods)****Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)**

### STEP 5: Use Fill Handle to Complete Dataset

- After inserting the formulas, we need to apply them to the whole sheet.
- To do so, select
**Cell C8**and put the cursor on the bottom right side of the cell. - The cursor will change into a
**small black plus****(+)**sign. - Double-click on the
**small black plus (+)**sign or drag it down to**Cell C246**.

- You need to repeat the same procedure for the rest of the formulas.

- You can add freeze panes to see all the rows keeping the headers.
- For that purpose, select
**Cell B7**and then, go to the**View**tab and select**the Freeze Panes icon**. - Finally, you will get results like the picture below. You can scroll down to the bottom.

### STEP 6: Update Total Amount and Interest in Dataset

- In the sixth step, you need to apply formulas to update the
**Total Amount**and**Interest**in the dataset. - First, we will apply the formula for the
**Updated Interest**. - Select
**Cell J8**and type the formula:

`=SUMIF(E7:E246,">0")`

In this formula, we have used **the SUMIF function **to calculate the **Updated Interest **amount. This formula will show results when the result is greater than **0**.

- Press
**Enter**to see the result.

- Then, type the value of the
**Updated Interest**in**Cell J7**.

- Now, select
**Cell J9**and type the formula:

`=C4+J7`

- It denotes the
**Total Amount**.

- For
**Total Savings**, type the below formula in**Cell J10**:

`=J7-J8`

- Finally, for
**Updated Amount**, type the below formula in**Cell J11**:

`=C4+J8`

### STEP 7: Use Prepayment Option to See Changes

- In the last step, add prepayments in
**Cell G10**and**G12**.

- After entering the pre-payments value, you will see the changes in the
**Updated Interest**,**Total Savings**, and**Updated Amount**.

From the results, we can say that after making prepayments in the initial months the **Total Savings **increase, and also the **Updated Interest **decreases.

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

## Things to Remember

There are certain things you need to remember while you are working with the home loan **EMI **calculator.

- In the above calculator, if you add tenure years of more than
**20**years, then you need to create more**Rows**for them to get different values. - Also, when you are calculating the
**EMI**, be careful with the arguments you enter inside the formula.

## Conclusion

In this article, we have demonstrated step-by-step procedures to create a **Home Loan EMI Calculator in Excel Sheet with a Prepayment Option**. I hope this article will help you to create an EMI calculator easily. Moreover, you can use the calculator we have used here. To do so, download the workbook. We have added the workbook at the beginning of the article. Also, you can download it to test your skills. Visit **the ExcelDemy website** for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Thank you very much for sharing this,

could you share, how to print the expected end date dynamically after each part payment.

Hello, Abu!

Thanks for your comment. Unfortunately, it is not possible to show the expected end date dynamically after each pre-payment. Because the dataset changes dynamically after a pre-payment. But you can find the expected payment date after each month using the formula below. For the dataset, we have used in this article, you can type the formula in Cell H8:

=DATE(YEAR(H7),MONTH(H7)+(12/$J$15),DAY(H7))

Here, Cell H7 contains the loan starting date and Cell J15 contains the number of yearly payments.

You can also take a look at this article https://www.exceldemy.com/student-loan-payoff-calculator-with-amortization-table-excel/ for an explanation of the formula. I hope this will help you to solve your problem.

Thanks!