How EMIs are calculated

In Understanding EMI, we explained the basics of an Equated Monthly Installment and what it consists of.

Today, we tell you how the EMI is mathematically determined. Though this is a complicated formula, all you have to do is figure it out on MS Excel. 

Here we give you step-by-step directions.

Loan details

Let's say these are the basic details of your loan

Amount = Rs 1,00,000
Tenure = 10 years
Rate of interest = 8.75% per annum

Using these figures, we shall explain how you can calculate the EMI for any particular amount.

What is my EMI?

• Open Microsoft Excel
• Go on to an Excel sheet
• Click on the fx option; you will find it on the menu at the top of the page
• You will get Function Category, click on Financial
• On the right hand side, you will get Function Name, click on PMT
• Click OK

A box will appear.

Rate: Insert the interest rate here. When adding the interest rate, add the interest rate figure. Don't just put 8.75, put 8.75%. Always divide by 12 to indicate the monthly payments.

Nper: This is the number of months you will take to repay the loan. Do not put in the number of years, but number of months. So 10 years would be 120 months.

Pv: This is the total loan amount. Do not put any commas here. So do not write it as 1,00,000 but as 100000.

You will see Formula result, which is the answer. Here it is -1253.267504. Or, if you click OK, you will get $1253.27.
Of course, we go by rupees so your EMI will be Rs 1,253.27 (rounded off to Rs 1,254)

What is the interest component of my EMI?

Let's say you are repaying your loan but want to know how much is interest payment and how much is the principal amount. You can find this per EMI.

Here's how.

• Open Microsoft Excel
• Go on to an Excel sheet
• Click on fx option, you will find it on the menu at the top of the page
• You will get a Function Category, click on Financial
• On the right hand side, you will get Function Name, click on IPMT
• Click OK

A box will appear.

Rate: Insert the interest rate here. When adding the interest rate, add the interest rate figure. Don't just put 8.75, put 8.75%. Always divide by 12 to indicate the monthly payments.

Per: This is the installment you are referring to. Let's say it is the 16th installment of the loan.

Nper: This is the number of months you will take to repay the loan. Do not put number of years, but number of months. So 10 years would be 120 months.

Pv: This is the total loan amount. Do not put any commas here. So, do not write it as 1,00,000 but as 100000.

You will see Formula result, which is the answer. Here it is –668.8227439. Or, if you click OK, you will get $668.82.

Since we go by rupees, the interest component of your Rs 1,254 EMI is Rs 668.82.

The balance is principal repayment. But, if you want to directly check the principal amount, we show you how.

What is the principal component of my EMI?

• Open Microsoft Excel
• Go onto an Excel sheet
• Click on fx option; you will find it on the menu at the top of the page
• You will get Function Category, click on Financial
• On the right hand side, you will get Function Name, click on PPMT
• Click OK

A box will appear.

Rate: Insert the interest rate here. When adding the interest rate, add the interest rate figure. Don't just put 8.75, put 8.75%. Always divide by 12 to indicate the monthly payments.

Per: This is the installment you are referring to. Let's say it is the 16th installment of the loan.

Nper: This is the number of months you will take to repay the loan. Do not put number of years, but number of months. So 10 years would be 120 months.

Pv: This is the total loan amount. Do not put any commas here. So do not write it as 1,00,000 but as 100000.

You will see Formula result, which is the answer. Here it is -584.4447605. Or, if you click OK, you will get $584.44. Since we go by rupees, the interest component of your Rs 1,254 EMI is Rs 584.44.

Tomorrow we conclude this series by telling you how to arrive at the right EMI for youself when taking a loan.