Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


Home Loan Calculator in Excel Sheet with Prepayment Option: Create with Easy Steps

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.

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

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

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

Read More: EMI Calculator with Prepayment Option in Excel Sheet


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.

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

Note: The Interest Rate should be in the Percentage Number Format.

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


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.

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

  • 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-by-Step Procedures to Make a Home Loan Calculator in Excel Sheet with Prepayment Option


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

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

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.

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

  • After that, select Cell E7 and type the formula of Interest amount:
=$E$4/12*F7

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

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.

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

  • To find the Principal amount, type the below formula in Cell D7:
=C7-E7

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

  • Again, hit Enter to see the result.

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

  • Now, to calculate the Principal Remaining, type the below formula in Cell F8:
=F7-D7-G8

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

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.

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

  • The EMI will be fixed for the rest of the months.
  • So, type the formula below in Cell C8:
=$C$7

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


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.

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

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

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

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

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


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.


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.

Download Practice Calculator

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


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. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

6 Comments
  1. 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!

  2. hello,
    thank you for sharing wonderful template.
    can you please also help to add floating interest rate column, as maximum loans are in floating rates in India.

  3. I thought like number of emi’s will be reduced when we pay the principal amount can I get that?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 9:50 AM

      Dear DORABABU

      Thank you for your comment and your insightful observation. You are correct in pointing out that making prepayments towards the principal amount of a home loan can indeed result in a reduction in the total number of EMIs. This is an important consideration and can have a significant impact on the overall loan repayment timeline.

      To incorporate this feature into the home loan EMI calculator, you can make the following adjustments to the existing formula:

      Update Total Number of Months (N):

      Instead of fixing the total number of months as G4×12, you can dynamically adjust it based on the prepayments made. You can introduce a new variable, say M, representing the total number of months after considering prepayments. The formula for M would be: M=G4×12−Number of Prepayment Months

      Modify the PMT Function for EMI Calculation:

      Update the formula for EMI calculation (in cell C7) to reflect the adjusted total number of months (M): =ABS(PMT(E4/12,M,F7))

      By making these changes, the calculator will dynamically adjust the total number of EMIs based on the prepayments made, providing you with a more accurate representation of the impact of principal payments on the loan tenure.

      I hope this addresses your query. If you have any further questions or if there’s anything else you’d like to discuss, please feel free to ask.

      Best Regards

      Sumaiya Mirza

      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo