Blogs

A woman took a home loan of Rs 30 lakh and was paying EMIs on time. One day her friend calculated her instalment and told her she had been paying more interest than the lender had offered at the time of loan agreement. The lady didn’t believe her friend and paid whatever she was asked to pay, thinking why would a lender cheat its borrowers. She overpaid because she didn’t know about how to calculate the components (principal + interest) of an EMI.

But, can you convince yourself? Or is it ok for you to pay an excessive amount as interest?

No, not at all.

Everybody wants to save even a single penny of their hard-earned money, but they don’t know how to do it.

Usually, they don’t calculate EMIs prior to loan application and fall in the trap.

However, lenders never restrict you from using the EMI calculator. In fact, many lenders provide an online EMI calculator so you can calculate your monthly instalments easily. Yet the tool is offered by the lender only which you can’t trust blindly, and hence you need a neutral device.

If you’re interested in a third-party EMI calculator, then this blog is for you. In this blog, you will learn to make your personal EMI calculator on excel sheet, so that in future you can calculate EMI for any kind of loan even before applying for the same. 

But before we jump into the step-by-step process of making an EMI calculator on excel, let’s first understand more about EMI and the process behind its calculation.

What is EMI?

When you take a loan, you repay it in Equated Monthly Instalments, popularly known as EMIs, for a fixed number of years. Your monthly instalment includes two components: the principal and the interest. In simple words, each EMI comprises the principal amount and interest. 

Assume you take a personal loan of Rs 5 lakh at an interest rate of 11% for a tenure of five years, then you will pay Rs. 10,871 each month for up to five years to pay off your loan. 

You may wonder how we have come up with the exact EMI amount.

Well, calculating EMI is not rocket science as it is done with a mathematical formula. If you also want to know the EMI calculation formula, then look at….

EMI Calculation Formula

E = P*r*(1+r) ^n/((1+r) ^n – 1)

Here P stands for the principal loan amount.

r stands for the interest rate applicable on the loan.

n stands for the number of years to repay the loan.

E stands for the amount to be paid each month (EMI).

Well, calculating the EMI with this formula is not a child’s play. If you are from the analytical background, then you can find it useful, otherwise, it’s better to make your own EMI calculator on excel.

Step-by-Step Process to Make EMI Calculator on Excel Sheet

Making the Interface of EMI Calculator on Excel

• Open the MS Excel in on your desktop or laptop.
• Make an interface like mentioned in the image.

Exploring EMI Calculation Formula in Excel

• Keep the cursor in the adjacent column of the EMI section in the B5 column.
• Now, navigate to the ‘Formulas’ tab and click on it.
• Then click on the ‘Financial’ menu under the ‘Formulas’ tab.
• From the drop-down menu, find the PMT formula and click on it.

Inserting EMI Calculation Formula in The Excel Sheet

• Now a new window will appear.
• Enter the inputs as B3/12 in the ‘Rate’ section, B4*12 in the ‘Nper’ section and B2 in the Pv section.  
• In the function library, you can see the complete formula written as =PMT (B3/12, B4*12, B2).
• Don’t forget to insert a ‘minus’ sign before the PMT in the above formula.

Final Testing

• Now your EMI calculator is ready to use.
• Enter the loan amount, interest rate, and the tenure in the required sections.
• Move the cursor in the EMI column and hit the enter.

Bonus Tip

After entering all the details in the excel EMI calculator, you can see the result as EMI showing as the amount of Rs. 22,244.45. 

Online EMI Calculator Showing Exact Results Matching with The Excel EMI Calculator

Now, if you have any doubt about your calculator or the formula you have used in this calculator, you can easily verify it with an online EMI calculator.

Note: The interest rate is calculated on a monthly basis and not an annual basis. Hence, you need to convert the annual interest on loan to monthly interest by dividing the annual percentage by 12. For example, if the annual interest rate is 11.25%, then the monthly interest rate will be = 11.25%/12, i.e. 0.0093.