SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


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

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.

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


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.

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

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

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

Read More: EMI Calculator with Prepayment Option in Excel Sheet


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

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

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

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


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: Home Loan EMI Calculator with Reducing Balance in Excel


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.


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.

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

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.

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


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.

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

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

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

  • Lastly, we can visualize the EMI Chart.


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.

Download Template

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


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.


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.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo