How to Calculate EMI in Excel With the PMT Formula
Published 13 October 2026 · 6 min read · Clean EMI Editorial
If you keep your own loan sheet, Excel’s PMT function returns the monthly EMI in one line. The formula is short, but three inputs cause most errors: the monthly rate, the number of monthly instalments and the sign of the loan amount. This guide builds one sheet for a single example, so you can test your own version against a known answer before relying on it.
Part of the Home Loan guide series.
The PMT formula in one line
The function reads =PMT(rate, nper, pv). The rate is the annual rate divided by 12, nper is the tenure in months, and pv is the loan amount entered as a negative number, so that the EMI appears as a positive figure. For ₹35 lakh over 20 years at an illustrative rate of 8.5% p.a. (not a bank quote), the formula is =PMT(8.5%/12, 20*12, -3500000). It returns ₹30,374 a month, rounded to the rupee.
Set up the sheet step by step
- In cell B1, enter the loan amount as 3500000.
- In B2, enter the annual rate as 8.5%. Format the cell as a percentage.
- In B3, enter the tenure in years, which is 20.
- In B4, calculate the monthly rate with =B2/12.
- In B5, calculate the months with =B3*12, which gives 240.
- In B6, enter =PMT(B4, B5, -B1) to get the EMI.
Keeping each input in its own cell means you can change one number and see the result at once, which helps when you compare offers. The EMI calculator on this site can confirm the same answer.
Check the answer before you trust it
Multiply the EMI in B6 by the months in B5 to get the total paid. For this example, ₹30,374 × 240 comes to about ₹72,89,760. Subtract the ₹35 lakh loan and you get total interest of about ₹37,89,760. If your sheet shows a different total, look at the rate and the months before anything else. Then test a one-year loan, where the answer is easy to check by hand.
Common mistakes
- Typing 8.5 where the formula expects 8.5% (0.085). Excel then reads the rate as 850% a year, and the EMI becomes very large.
- Using the annual rate directly in the formula instead of dividing by 12.
- Entering the tenure in years where the formula expects months.
- Entering the loan amount as a positive number, which turns the EMI negative.
- Ignoring rounding. Banks round the EMI to the rupee, so expect small differences.
Using the sheet with a bank offer
A sheet gives a useful estimate, but the bank’s figure can differ because of rounding, the disbursement date and how the bank applies the rate. Ask the lender for its own EMI schedule and compare it with your sheet line by line. The home loan page explains the general process, and the bank’s profile is at UCO Bank. The final decision rests with the lending bank.
Frequently Asked Questions
Why does my Excel EMI differ from the bank’s figure?
Small differences usually come from rounding, the date the loan starts, or the exact rate the bank applies. Ask the bank for its schedule and compare the two.
Do I have to enter the loan amount as a negative number?
In the PMT function, yes. Entering it as a negative value makes the EMI come out positive. A positive loan amount gives a negative EMI.
Should I type the rate as 8.5 or 8.5%?
Type 8.5% with the percent sign, or divide by 100. Typing 8.5 means 850% a year to Excel, which gives a wrong answer.
Can I use the same sheet for any loan?
The structure works for any reducing balance loan with a fixed rate and equal monthly instalments. Change the inputs for each case, and check the result against the lender’s figures.
Useful free tools
- Free Credit Analyzer — Upload your CIBIL report and see which loans you may be eligible for, with an indicative EMI.
- Check my Loan Eligibility — Answer a few questions and see how your application would be routed to a PSU bank.
- Home Loan Balance Transfer Calculator — Compare your current EMI with a lower-rate loan over the same remaining tenure.
- Credit Score Enhancer — Practical steps to improve your CIBIL score before you apply for a loan.
Ready to check your eligibility?
No fee to apply. CIBIL score of 700 or above required.