Search:



The Web

Rediff







Home > Get Ahead > Money > Borrow

Your daily predictions:

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.




Share your comments


 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
Email this article
Top emailed links
Print this article
Contact the editors
Discuss this article






Copyright © 2006 Rediff.com India Limited. All Rights Reserved.