Equated monthly instalment (EMI) refers to the fixed amount that the borrower of a loan needs to pay to the lender at a specific date every month. The EMI includes the principal amount as well as the interest component on loan. The EMI calculation is made in a way that the borrower can pay an equal amount each month, as per the loan schedule. All loan products including Home Loan, Personal Loan, Education Loan, Medical Loan, and more can be borrowed at EMI repayment schedule.

EMI Calculator Excel

Knowing your EMI before applying for a loan is extremely useful as it helps you to prepare for upcoming financial obligations. 

You can use the EMI Calculators available online to calculate your loan EMIs. However, if you do not want to calculate the EMI using the online EMI Calculator or manually, another alternative for you is to use the EMI Calculator Excel. 

Here is how to calculate EMI in excel.

EMI calculation on Excel is very easy and it uses the following EMI formula in excel for calculation:

EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1].

Here,

P is the principal loan amount you wish to obtain

R is the rate of interest applicable on the loan per annum 

N is the number of monthly instalments or loan tenure.

Benefits of Excel Loan Calculator

• You can view the complete loan amortization schedule excel.
• You can use it as an EMI calculator excel sheet with prepayment option.
• You can opt for EMI calculator excel download for your reference.

Components of Loan EMI

The EMI’s have two major components related to it, which are namely the principal amount and the interest amount. The principal amount refers to the amount borrowed as the loan. The amount of interest on the loan is determined based on the principal amount. For instance, if you avail a loan of Rs. 10,000, at an annual charge of 5% interest, you need to pay a total amount of Rs. 500 additional to the loan amount for repayment of the loan.

The interest amount refers to the amount which the lender charges to the borrower for the loan. The interest can be calculated as simple interest or compound interest compounded monthly, quarterly, or yearly. The simple interest is based on the Principal Loan amount, and the compound interest is based on the principal amount and the interest which accumulates on it through previous periods.

Let’s take an example. If you avail a loan of Rs. 10,000, at an annual charge of 5% interest, you need to pay a total amount of Rs. 500 additional to the loan amount for repayment of the loan. Here the EMI can be calculated either using simple interest formula or Compound Interest Calculator.

The simple interest is calculated using the formula:

P x R x N

Here the ‘P’ refers to the Principal amount, R is the annual interest rate, and N implies the term of the loan calculated in years.

To calculate Compound Interest, the formula applied is:

P x (1+R) – 1

Here ‘P’ stands for the Principal Amount, ‘R’ stands for the annual interest rate and ‘T’ stands for the years for which the interest is applied.

When the loan tenor increases the interest amount tends to reduce, and the principal amount increases.

The Factors which Affects the EMI

There are mainly three factors which are responsible for affecting the EMI. These are:

The loan amount: The loan amount which you borrow is the primary factor which decides the EMI to be paid. The higher the amount of loan, the bigger is the EMI.

The Interest rate: The EMI is directly proportional to the rate of interest on the loan. The lenders calculate the interest rate based on various factors like Credit Score, the market situation, the income of the applicant, etc.

The tenor of the loan: The tenor of loan also plays a vital role in affecting the EMI. The longer the tenor, the higher is the overall outgo on interest.

The Change of EMI during the Tenure of the Loan

Usually, under normal circumstances, the EMI may not change throughout the loan tenure period. However, there are certain unavoidable circumstances which lead to the changes in the EMI. These are:

• The floating interest rate

The floating interest rate changes as per the dynamics of the market. Hence, when the interest rates fall, so does the EMI and vice versa.

• Prepayment of the loan

The EMI may change as you prepay a part of the loan. This can bring down a substantial amount of principal amount.

• The progressive EMI

Few lenders let you pay off the EMIs progressively on a long term basis. This indicates that as your salary increases, the loan is paid off faster.

Why is it Necessary to Calculate the EMI?

There are various benefits attached to calculating EMI before you apply for a loan. These include:

• It reduces errors in financial management.
• It helps you to manage finances better when you use the Personal Loan EMI Calculator.
• You can choose the amount basis loan, tenure, and your repayment capacity.
• You can maintain a good CIBIL Score by managing your loan in the most optimum way.
• You can choose the rates offered on loan by different lenders and choose the best one.

Calculating the EMI Using Excel Sheet Calculator

If you do not want to calculate the EMI manually or have trouble doing so, you can opt for an loan EMI calculator excel. It is easier to use and brings accurate and faster results than manual calculations.

Excel Loan Calculator

In an EMI calculator excel sheet you need to choose a cell and enter the following PMT formula in excel:

=PMT (RATE, NPER, PV, FV, TYPE)

In this formula:

• PMT means Payment in finance. PMT Excel function helps in calculating the total periodic payments done against a loan or investment at a constant rate of interest for a specific number of periods. 
• RATE refers to the rate of interest which is applicable to the loan amount
• NPER in the above-mentioned EMI calculation formula in excel refers to the total number of monthly instalments you need to pay or the loan tenure period. 
• PV in the above excel loan calculator stands to represent the principal loan amount.
• FV refers to the future value or the balance left once all the payments have been made.

{Type} is optional augment which is used to define the time when the payment is credited. The figure 0 indicates the function which states that the payment is to be made at the beginning of the month.

The example stated below indicates how an home loan EMI calculator excel sheet can be used for the monthly payment for a home loan.

Since we need the monthly payment, we need to express the function augment in months.

The function can be set up as follows on an home loan calculator excel: 

For example, $200,000 of loan is availed for 30 years at an interest rate of 4%.

=PMT (.04/12,30*12,200000)

Where,

.04/12 stands for the annual interest rate, which is divided by 12 so that it can be expressed for a monthly rate.

30*12 refers to the duration of the loan

200000 refers to the amount which is borrowed

We should place the basic loan values into the cells, and then use the cell references into the formula as shown in the screenshot below:

In this excel sheet

B1/12 is the annual interest rate, which is divided by 12 to convert into a monthly rate.

B2*12 refers to the number of years which is multiplied by 12 to express in months

B3 is the final loan amount which is borrowed

You can also use Loan Against Property Interest Rate Calculator to find out how much you will save on interest and how it affects your Loan EMI.