SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option

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.

Download Template

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

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.

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.

• Then, press Enter to see the result.

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

• Further, press Enter to see the result.

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.

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

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

• Hit the Enter key to complete the process.

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

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

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

• 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 100% 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