Search: The Web Rediff

Home > Get Ahead > Money > Borrow

How EMIs are calculated

July 26, 2006

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 8.75%/12 Nper 120 Pv 100000 Fv 0 Type 0

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 8.75%/12 Per 16 Nper 120 Pv 100000 Fv 0

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 8.75%/12 Per 16 Nper 120 Pv 100000 Fv 0

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.

 What do you think about the story? Read what others have to say: Number of User Comments: 89 Sub: Great article I know that EXCEL is really powerful . Its great to use those functions and with such examples its nice to learn the new things ... Posted by Divyakanth M.C. Sub: very good stuff Dear Sir/Mam, Thanx for these type of stuffs posting in website.That's why Im visiting Rediff site frequently. Regards, Ranjan ... Posted by Desathi Radharanjan Sub: EMI Computation: Monthly or Annually I would like to know as to whether it considers Monthly or Annual Reducing or Zero Reducing while calculating the EMI Posted by Ruchi Sub: useful Sub: EMI CALCULATION good,it would have been better if u had explained formula & logic behind that. ok Posted by Hariharan Sub: Nice Tanx for the articile, and the coming days pls include the other functions of excel Posted by Rajiv

Disclaimer

 Article Tools