Spreadsheets

EMI calculator in Excel using the PMT function, step by step

Build an EMI calculator in Excel with the PMT function: three input cells, the monthly rate and sign explained, a total-interest line and a worked example.

Editorial illustration for EMI calculator in Excel using the PMT function, step by step

To calculate an EMI in Excel, put the loan amount, annual rate and tenure in months in three cells and use =PMT(rate/12, months, -amount). PMT returns the fixed monthly payment for a reducing-balance loan. Multiply the EMI by the number of months and subtract the loan amount to see the total interest.

What are the three inputs?

Set up a small block:

  • B1 Loan amount, for example 500000
  • B2 Annual interest rate, for example 10% (type 10% or 0.1)
  • B3 Tenure in months, for example 60

If the loan is quoted in years, multiply by 12 in the tenure cell, for instance =5*12. Excel needs everything in the same unit, and EMIs are monthly.

What does the PMT formula look like?

In B4 write:

=PMT(B2/12,B3,-B1)

PMT takes arguments in this order: rate per period, number of periods, present value. Three points explain the formula.

  1. Rate per period: the quoted rate is annual and payments are monthly, so divide by 12. Typing 10% in B2 and dividing by 12 gives 0.8333 percent a month.
  2. Number of periods: the tenure in months, 60 in the example.
  3. Present value with a minus sign: PMT follows cash-flow signs. Money you receive (the loan) is positive and money you pay is negative. If you enter the loan as a positive number, the EMI comes out negative. Writing -B1 flips the sign so the answer is a positive EMI.

If you prefer, leave the loan positive and put a minus in front of the whole function: =-PMT(B2/12,B3,B1). Both give the same number. Pick one style and use it everywhere so you never mix the signs.

What do I get for a worked example?

Take a round loan of ₹5,00,000 at 10% for 60 months. These are sample numbers, not a quote. The formula gives an EMI of about ₹10,623.52.

LineFormulaResult
EMI=PMT(B2/12,B3,-B1)₹10,623.52
Total payable=B4*B3₹6,37,411
Total interest=B4*B3-B1₹1,37,411
Interest as share of loan=B6/B1about 27.5%

Check by hand: 10,623.52 x 60 = 6,37,411.20, and subtracting 5,00,000 leaves 1,37,411.20. To see the effect of the rate alone, change B2 to 9% and the EMI becomes about ₹10,379.18, which is ₹244 less every month and about ₹14,660 less over the full 60 months. Changing one input at a time is a good way to see what matters.

How do I show the total interest and the interest share?

In B5 write =B4*B3 for total payable, in B6 =B5-B1 for total interest, and in B7 =B6/B1 formatted as a percentage. Round the display with the Decrease Decimal button rather than ROUND if you want to keep full precision in later formulas. If you prefer whole rupees in the EMI itself, use =ROUND(PMT(B2/12,B3,-B1),0).

Can I make it easier to read?

Yes. Add a label in column A for each cell, format the rate as Percentage and the amounts with the Indian rupee symbol using Format Cells > Currency, and apply a Data Validation rule on B3 allowing only whole numbers. If you want to compare two offers, copy the block into columns D and E and enter the second offer's figures.

Next steps

  • For the month-by-month split of each EMI, see the amortisation approach, where each row separates interest from principal.
  • To see how a part payment changes the tenure, use NPER on the outstanding balance.
  • Once you take the loan, add the EMI to your regular records; if you import bank statements into SpendixAI, you can file EMIs under their own category and compare them with the schedule.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports