Method of EMI Calculation on Excel

EMI or Equated Monthly Installments is a fixed amount that is paid by the borrower to the financier, on a monthly basis. This amount will contribute to the principal loan amount and the interest applicable on the loan. Payment of EMI is spread over the loan tenure that is opted for by the borrower.

EMI Calculation Methods

Calculating EMI has a Simple Formula, Which is As Follows:

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


P is the original loan amount or principal, R is the rate of interest that is applicable per annum and N is the number of monthly installments/ loan tenure.

In case you do not wish to calculate the EMI manually or using online EMI Calculator applicable on your loan, another alternative is to use Excel. EMI calculation on Excel is very easy and the formula used is very simple.

Loan Calculator Excel

In the Excel sheet, choose a cell and enter the following formula:



  • Rate stands for rate of interest applicable on the loan
  • NPER stands for total number of monthly installments/ loan tenure
  • PV stands for present value/ loan amount/ principal amount
  • FV stands for future value or cash balance once last payment has been made. This can be omitted and the value will be counted as zero (0).
  • Type is zero (numerical 0) or 1 – this indicates when the payment is due. If payment is due at the end of the period, the type will be equal to zero. If the payment is due at the commencement of the month, then the type will be set as 1.
Calculate your EMI

Factors that affect the EMI amount

While the amount EMI towards a loan varies from person to person depending on the circumstances, the major factors that play a key role in determining the number of monthly installments are:

  • Loan amount: This is the amount that an individual borrows from the lender. Therefore, the greater this amount is, the higher the amount for EMI will be.
  • Repayment tenure: The EMIs are equally distributed throughout the entire loan repayment tenure. Hence, with a longer tenure, the borrower will have to pay a smaller amount as EMI.
  • Rate of interest: The interest rate of a loan is directly proportional to the total debt that a borrower has to pay. Therefore, an increase in the rate of interest translates to an increase in the payable monthly installment amounts.
  • Down payment amount: Making a down payment reduces the quantum of loan that an individual needs to borrow from the lender. Hence, with an increase in the amount of down payment, the EMI also reduces.

Why should you calculate your EMI?

There are a number of benefits to calculating your EMI amount before applying for a loan:

  • It can reduce your chances of defaulting by helping you manage your finances better.
  • You can compare the rates from different lenders and choose the one that best suits your needs.
  • You will be able to choose the loan amount and tenure depending upon your requirement and repayment capability.
  • There will be fewer chances of your loan getting rejected due to exceeding your repayment capacity.
  • You can maintain a better credit history by managing your loan in an optimum way.

Things to Know :

  • Other charges such as taxes, late payment fees, prepayment charges, processing charges, etc. that might be included in the total loan expense will not be projected in this calculation.
  • The rate of interest will be calculated on a monthly basis and not on an annual basis. Thus, the annual interest on loan should be converted to monthly rate of interest using the following method: Annual Interest Percentage divided by 12. For example, if the annual interest rate is 12%, then the monthly interest rate = 12%/12, i.e., 0.01.

News About Method of EMI Calculation on Excel

  • In order to prevent a currency rout, RBI may increase rates by 50 basis points

    While India has stopped itself from increasing the cost of funds and joining the likes of Indonesia or Argentina, the country might now increase the rates by 50 basis points in order to prevent a rupee rout. Rupee extended the losses to the eight consecutive day and hit a record low of 72.11 on Thursday. The overnight interest rate swap has also increased to a three year high now, indicating that the Reserve Bank of India might increase the rates in back-to-back policy meeting that will be held in October and December. Gauge with 1 year maturity has increased by 35 basis in the past three or four weeks. It hit a rate as high as 7.30 percent, the highest rating recorded in almost three years. All this points to a drastic increase in the rate to cope up with the falling rupee. It is expected that interest rates might increase by at least 50 basis points in the next two policies.

    12 September 2018

  • RBI may hold on rates after inflation rates eased in July

    The Reserve Bank of India may hold on to the interest rates at its review in October. This is after the retail inflation in the country eased in the month of July. The Reserve Bank of India raised the rates for the second straight time in the meeting held on August 1. Consumer prices in the month of July rose by 4.17 percent from a year before. This is comparison to the revised 4.92 percent for the month of June. The next policy decision of the Reserve Bank of India is on October 5.

    17 August 2018

  • Commercial operations launched by Jana Small Finance Bank

    Jana Small Finance Bank after getting the in-principle approval from the Reserve Bank of India three years back has announced that it will be launching commercial operations for the banking operations offered by the company. Jana Small Finance Bank, which was earlier called Janalakshmi Financial Services encountered huge losses during the demonetisation but are now hopeful they can be profitable by the end of fiscal year 2019. The bank was the among the worst affected at the time of demonetisation, reporting losses of Rs.3,400 crore by the end of March 2018. The delay in the launch of commercial operations even though they received the final licence last year was due to the delay in the infusion of Rs.2,000 crore capital into the company. The capital adequacy ratio of the bank at the end of March 2018 was at 35%. Because of the losses incurred by the company due to weak collection efficiency and lower disbursement, rating agency Crisil has reduced the ratings on debt instruments and bank loan facilities. The bank is now hoping to make improvements in the risk management processes. In the next three years, Jana Bank is looking to expand its loan book with 20% accounting for agriculture loans, 30% for small and medium enterprise loans and 50% for individual loan business. The bank will look to offer basic banking services and also lend to some of the underserved sections of the customers.

    20 July 2018

Essential EMI Calculator Pages
EMI Calculator Product Pages
Informative EMI Calculator Pages

Display of any trademarks, tradenames, logos and other subject matters of intellectual property belong to their respective intellectual property owners. Display of such IP along with the related product information does not imply BankBazaar's partnership with the owner of the Intellectual Property or issuer/manufacturer of such products.

This Page is BLOCKED as it is using Iframes.