Steps to Calculate the EMI Online and Manage EMI Payments for Personal Loan

While it is easy to get a personal loan nowadays, it is best to remember that it comes with certain responsibilities. It is crucial you repay the loan amount on time to keep your CIBIL score intact. Many borrowers prefer taking a personal loan from SBI, as this public sector bank has the biggest network across India and offers personal loans at competitive rates. There is no restriction on how you can use this loan, so it is one of the preferred loans to take when you have an emergency or financial crunch. However, to avoid falling into a debt trap, it is essential that you calculate the EMI so that you can decide the loan tenure you should go with.

When you take an SBI personal loan, a part of the EMI is the principal and the remaining is the interest. As you keep repaying the loan, the principal component gets bigger while the interest component shrinks. When you make the final payment, usually it is just the remaining principal amount.

Factors that Affect EMI

EMI refers to the monthly payment you make to the lender for a predefined period. It helps to repay the loan amount along with the interest. The duration of the loan (tenure), the loan amount and the rate of interest are the three main factors that lenders, like SBI, use to determine EMI.

The higher the loan amount and interest rate for your personal loan, the greater will be the EMI. On the other hand, if loan tenure is long, the EMI amount reduces. While this may sound attractive, you will end up paying a lot of interest when you opt for a long tenure. Hence, if you opt for a shorter tenure you will pay less interest on the whole compared to a person who goes in for a longer personal loan tenure.

Calculating Your SBI Personal Loan EMI

It may come as a surprise to learn that SBI and all other banks actually use a mathematical formula to determine the EMI. Even an SBI personal loan interest rate calculator that you find online makes use of the same formula.

The formula is as follows:

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

Here,

P = The principal amount (the total loan amount without interest)
R = The monthly rate of interest that SBI levies on the loan amount
N = The tenure or the duration of the loan when you have to pay the monthly installments each month

This formula is used for all types of loans, including personal and housing loans.

SBI Personal Loan EMI Calculator in Excel

You can create your own SBI personal loan EMI calculator in Excel, as the application has in-built formulae for this purpose. To make your own EMI calculator in Excel follow the steps highlighted below.

Type “=pmt” in Excel
You will immediately see “PMT(rate,nper,pv,[fv],[type])” being displayed
Now get the monthly EMI by putting the necessary figures into the formula

Here is an example of how you can create SBI personal loan EMI calculator in Excel and get the monthly EMI for your loan along with other details.

 

1

AB

2

Particulars

Amount (Rs.)

3

Loan amount

200000

4

Tenure

3

5

No. of payments per year

=B4*12

6

Rate of interest

=18%/12

7

Monthly payment

=PMT(B6,B5,-B3,0)

‘B’ is the letter corresponding to the row. The final result that you will get will be as follows:

Thereafter, you can use Excel to create an amortization table. It will give you a clear idea how much is the EMI and from the EMI how much is interest and principal amount. Here is an example to help you out.

A = Total number of installments. Let’s assume that the loan tenure is 3 years. So, installments will be 3*12 = 36 installments

B = EMI, which will remain constant throughout the tenure of the loan

C = Interest component of the EMI, which you can calculate as highlighted below

An amortization table can be created using the result with the following parameters:

A= There will be 36 installments as the tenure is 3 years (i.e., 3*12=36)

B= EMI which remains constant throughout the column

C= Interest component which is calculated as follows:

 

excel sheet formula for emi calculation

The D part, or the principal component, can be calculated using the SBI personal loan EMI calculator in Excel in the following manner:

emi calculation excel

Finally, it is time to calculate the balance of the loan amount or the E component. You can do it in the following way:

Similarly, you can do the subsequent months’ calculations to get an amortization table, which will something like this:

emi calculation excel

When you reach the last month of the loan tenure, the balance (E) will automatically get copied into the principal (D) column if you have fed the formula into the Excel sheet. Furthermore, the difference between the principal amount and the monthly instalment will be highlighted in the interest (C) column. At the end of the loan tenure, you would have settled all your dues.

Using Online SBI Personal Loan Interest Calculator

Creating an SBI personal loan EMI calculator in Excel can be tedious and cumbersome, as you would have to be careful with the formula. One mistake and it will give you incorrect results that could wreak havoc with your monthly budget. That is it is prudent to use reliable online SBI personal loan eligibility calculator.

Such a calculator is not only reliable, but also quick and error-free. Within seconds you will get all the details that you need to make an informed decision. You can also play with the principal amount until you get an EMI that you can pay each month without any problem.

So, before you take a personal loan from SBI, use the SBI personal loan interest calculator to help you figure out the amount of loan you should take. It is a financially wise thing to do, as it will help you avoid the debt trap that many people get caught in without realising as they borrow more than they can afford.

Additional Read: Fulfill All Your Desires with SBI Bank Personal Loans

Author Since: Jan 17, 2019

Anoop Bansal, a professional Chartered Accountant based in the National Capital - Delhi. I have worked for the top-notch Indian and International banking firms for the past 10 years. Currently, I work at emi-calculator.loan blog and a financial consultant for different SMEs in India. Follow me on Facebook, Twitter and Linkedin for more information.

Related Posts