The largest debt we will likely ever incur in our life is a mortgage. For the great majority of individuals who aren’t involved in business and don’t need business loans, at least. While buying a house, many of us take out a home loan. With the help of a** home loan EMI calculator with a prepayment option,** we can easily keep track. And, the **SBI** home loan calculator will help Indian people, as this is the **State Bank of India**. This article will demonstrate the procedures to create an SBI home loan EMI calculator in an Excel sheet with a prepayment option.

**Table of Contents** hide

Download Template

What Is SBI?

What Is Home Loan EMI?

Step-by-Step Procedures to Create SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option

Step 1: Insert Loan Amount, Interest Rate, and Tenure in Years

Step 2: Set Months and Principal Remaining

Step 3: Calculate Equated Monthly Installment (EMI)

Step 4: Compute Interest Amount

Step 5: Utilize Monthly Principal and Principal Remaining

Step 6: Use Fill Handle to Complete Dataset

Step 7: Update Total Interest and Amount

Step 8: Use Prepayment Option to See Modifications

Step 9: Insert Chart for Better Visualization

Final Template

Things to Keep in Mind

Conclusion

Related Articles

**Download Template**

You can download the **Home Loan EMI Calculator** with the prepayment option and use the template for your work.

**SBI Home Loan EMI Calculator with Prepayment Option.xlsx**

**What Is SBI?**

**State Bank of India** is formally known as **SBI**. It is a governmental organization for payment institutions and a global public service bank with its headquarters in Mumbai, Maharashtra. **SBI **is a worldwide enterprise and a government financial organization. With a **23% market** share by resources and a **25** percent share of the whole loan and savings industry, it is a banking institution and the biggest bank in India.

**What Is Home Loan EMI?**

A **home loan** is an amount of money borrowed to buy a house from a bank or financial organization. Home loans include a fix or a variable interest rate as well as payment periods. A house mortgage is a loan provided by a bank, mortgage business, or other financial institution for the acquisition of a primary or second home. A house loan is a secured loan that is acquired for the purpose of buying a property by pledging the asset as security.

EMI stands for **Equated Monthly Installment**. It includes repayment of the principal amount and payment of the interest on the outstanding amount of your home loan. The formula for calculating the home loan **EMI** is.

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

Here,

**P = Principal Loan Amount**

**N = Loan Tenure in months**

**R = Rate of Interest**

As a result, it is clear that the EMI is influenced by the interest rate (**R**), the loan’s length (**N**) years, as well as income.

The **Principal Amount** and **Interest Amount **are the two basic components of the **EMI**. The initial principal amount is lower, but it rises over time. Additionally, the interest charge is large initially before declining over time. This is why we want to make an effort to pay in advance throughout the first few months.

**Step-by-Step Procedures to Create SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option**

**Home Loan EMI Calculator** assists in the calculation of the loan installment. And, **EMI** towards the home loan. It is an easy-to-use calculator and acts as a financial planning tool for a home buyer. It is a quick and easy process to create a home loan calculator in Excel as Excel has multiple tools and built-in functions. Let’s follow the instructions to make the **SBI** home loan **EMI **calculator in an Excel sheet with a prepayment option.

**Step 1: Insert Loan Amount, Interest Rate, and Tenure in Years**

To begin with, first, we need to put the basic information for further calculation.

- Firstly, we insert the information on the
**Loan Amount**. For example, we put our loan amount at $500,000. - Secondly, set the
**Interest**rate. An interest rate indicates how expensive lending is or how lucrative conserving is. In our case, the percentage is**9**. - Thirdly, put the
**Tenure in Years**. A home loan tenure is referred to as a home loan payback term. This is the amount of time that we have to return our total home loan, including interest. In this case,**5**years.

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

**Step 2: Set Months and Principal Remaining**

As the tenure is **5** years, so the month for the loan payment will be **5*12 = 60** months. So, now we will set up the months and the principal remaining.

- In the first place, we will create columns for
**Months**,**EMI**,**Principal**,**Interest**,**Principal Remaining**, and**Prepayments**. - Then, input the numbers of months in the
**Months**column. - To fill down months, choose cell
**B7**and type**0**. - Further, drag the
**Fill Handle**down to cell**B67**.

- Next, we will put the first
**Principal Remaining**amount which is our**Loan Amount**.

**Step 3: Calculate Equated Monthly Installment (EMI)**

We already know that the **EMI** is equal to **P ***** r * (1 + r)n/((1 + r)n – 1))**, where **P** is the loan amount, **R** is the interest rate, and **N** is the term in months. To calculate the **EMI**, we are combining the **ABS** function and the **PMT** function in excel. The Excel ABS Function returns a number’s absolute value. And the PMT function is one of the financial functions, that determines the loan payment based on fixed installments and a fixed interest rate.

- In the beginning, select cell
**C7**and put the formula to calculate EMI.

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

- Then, press
**Enter**to see the result.

🔎 **How Does the Formula Work?**

The **Monthly Interest Rate** is the first input (**E4/12**). Then, The number of months left is indicated by the second input (**G4*12-B7**). After that, The **Remaining Principal Amount** is represented by the third parameter (**F7**).

- The
**EMI**for other months will be the same. This will be fixed for the rest of the month. So, we put the previous cell in for the first month but with the absolute reference.

**Read More:** How to Calculate Interest Rate from EMI in Excel (with Easy Steps)

**Step 4: Compute Interest Amount**

Now, we have to compute the interest amount.

- Consequently, select cell
**E7**and insert the formula into that selected cell.

`=$E$4/12*F7`

- Further, press
**Enter**to see the result.

🔎 **How Does the Formula Work?**

To obtain the monthly interest rate in this calculation, we divided the annual interest rate by **12**. Afterward, to calculate the interest amount, multiply it by the remaining principal amount.

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

**Step 5: Utilize Monthly Principal and Principal Remaining**

At this moment, we will calculate the monthly principal with the help of a simple subtraction method.

- Thus, to calculate the monthly principal. Select a cell and put the formula into that cell.

`=C7-E7`

- Afterward, hit
**Enter**on the keyboard.

Here, we substrate the **Interest Amount** from **Equated Monthly Installment **(**EMI**) to get the **Principal Amount**.

- Now, we have all the information to calculate the principal remaining.
- Subsequently, select the first month of principal remaining.
- Then, insert the formula into that select cell.

`=F7-D7-G8`

- To finish the computation, hit the
**Enter**key.

Here, we subtract the **Pre-Payments** and **Principal** from the **Previous Remaining Principal** to get the **Remaining Principal Amount**.

**Similar Readings**

- Student Loan Payoff Calculator with Amortization Table in Excel
- Excel Simple Interest Loan Calculator with Payment Schedule

**Step 6: Use Fill Handle to Complete Dataset**

As we can see, we insert all the necessary formulas into the dataset. Now, we have to apply those formulas to the whole dataset.

- To do so, select the cell with the formula and put the mouse cursor bottom ride side. Immediately, the cursor will change into the
**plus**(**+**) symbol. - Now, drag the
**Fill Handle**down to duplicate the formula over the range. - Alternatively, to
**AutoFill**the range, double-click on the plus (**+**) symbol.

- Repeat the same process for the rest of all the columns to the duplication of the formulas.

**Step 7: Update Total Interest and Amount**

In this step, we will update the total interest and amount.

- Firstly, we will put the
**Total Interest**which is in our case,**$3442,870**.

- Now, to update interest we will use a built-in function in Excel which is the SUMIF function. So, choose the cell
**J8**and type the formula into that cell.

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

- Hit the
**Enter**key to complete the process.

🔎 **How Does the Formula Work?**

The **SUMIF** function determines if a condition is true or false before adding the values in a range. When the result is larger than** 0**, this formula will provide results.

- Further, we utilize the formula using addition for computing the
**Total Amount**. - As a result, select cell
**J9**and put the addition formula into that cell.

`=C4+J7`

- After that, press the
**Enter**key to see the result of the total amount.

Here, by adding **Loan Amount **and **Total Interest** we can just get the **Total Amount**.

- Furthermore, to calculate the
**Total Savings**, we are using the subtraction formula. - Thus, select cell
**J10**and type the formula.

`=J7-J8`

- Press
**Enter**.

Here, by subtracting **Updated Interest **from **Total Interest** we can acquire the **Total Savings**.

- Now, the final calculation for this step is the
**Updated Amount**. For this, select cell**J11**and insert the addition formula into that cell.

`=C4+J8`

- Then, press the
**Enter**key on your keyboard.

We add the **Loan Amount** and the **Updated** **Interest** we can obtain the **Updated** **Amount**.

**Step 8: Use Prepayment Option to See Modifications**

Now, we will add the prepayments to see changes.

- Add prepayment in cells
**G10**and**G12**and you will be able to see the changes in the whole row of those both cells.

- You will notice changes in the
**Updated Interest**,**Total Savings**, and**Updated Amount**after entering the prepayment value.

- As a consequence of the findings, we can conclude that after making the first prepayments, both the
**Total Savings**and the**Updated Interest**decline.

**Step 9: Insert Chart for Better Visualization**

Finally, we will insert a chart for better understanding and visualization. In Excel, a chart is a tool we may use to visually convey data. Typically, charts are used to examine trends and patterns in large data sets.

- Firstly, select the whole dataset and go to the
**Insert**tab from the ribbon. - Secondly, in the
**Charts**category, click on the**Insert Line or Area Chart**drop-down menu. - Thirdly, select
**1****00% Stacked Line**from the drop-down menu.

- This will appear in the chart.
- Then, click on the
**Chart Filters**, and check mark**EMI**,**Principal**, and**Interest**.

- Lastly, we can visualize the
**EMI Chart**.

**Read More:** How to Calculate EMI for Bike Loan in Excel (2 Easy Methods)

**Final Template**

This is the final template of the **SBI** home loan **EMI** calculator in an Excel sheet with a prepayment option and a chart for visualizing the data.

**Things to Keep in Mind**

While using the house loan **EMI calculator**, there are a few factors you need to keep in mind.

- If you enter a tenure of more than 5 years into the calculator above, you will need to add additional Rows in order for the figures to be different.
- Be cautious while entering your reasoning into the calculation when calculating the
**EMI**. - The
**Interest Rate**should be in the**Percentage****Number Format**.

**Conclusion**

The above procedures will assist you to **Create a Home Loan Calculator in Excel Sheet with Prepayment Option**. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can have a glance at our other articles in the ExcelDemy.com blog!

## Related Articles

- Create Loan Amortization Schedule with Moratorium Period in Excel
- How to Make Two Wheeler Loan EMI Calculator in Excel (2 Easy Ways)
- Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)
- Personal Loan EMI Calculator Excel Format (2 Suitable Examples)
- Car Loan Amortization Schedule in Excel with Extra Payments