PMT, IPMT and PPMT: Calculate EMI and Build a Loan Schedule

Q: Why is my PMT result negative?

Excel shows payments as negative. Use -B2 for the loan amount or put a minus sign before PMT.

By Ravindra Bagale, Excel & Data Analytics trainer LinkedIn Published: 6 October 2026 Last updated: 6 October 2026

Salman wants a ₹5,00,000 loan for 3 years at 10% a year. The bank quotes an EMI, but he wants to see how much of each month's payment is interest and how the balance falls. One PMT formula gives the EMI; a small table with IPMT and PPMT shows all 36 months.

PMT(rate, nper, pv) returns the fixed payment for a loan. Use the monthly rate (yearly rate ÷ 12) and the number of months. IPMT gives the interest part of a chosen month and PPMT the principal part; together they always add up to the EMI.

Download the practice file (pmt-emi-loan-schedule.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

Loan in B2 (500000), yearly rate in B3 (10%), years in B4 (3).
EMI: =PMT(B3/12,B4*12,-B2). The minus sign makes the result positive.
Interest in month 1: =IPMT($B$3/12,A8,$B$4*12,-$B$2) with the month number in A8.
Principal in month 1: =PPMT($B$3/12,A8,$B$4*12,-$B$2).
Balance: previous balance minus principal. Copy down to month 36; it ends at 0.

=PMT(B3/12,B4*12,-B2)
=IPMT($B$3/12,A8,$B$4*12,-$B$2)
=PPMT($B$3/12,A8,$B$4*12,-$B$2)

The EMI

=PMT(10%/12,36,-500000) = ₹16,133.59 a month. Over 36 months Salman pays ₹580,809, so the total interest is ₹80,809.

rate must match the payment period: monthly payments → yearly rate ÷ 12.
nper is the number of payments: 3 years × 12 = 36.
pv is the loan amount. Excel treats money you receive as positive and payments as negative, so type -B2 (or put a minus in front of PMT) to get a positive EMI.

First months of the schedule

Month	Interest (IPMT)	Principal (PPMT)	Balance
1	₹4,166.67	₹11,966.93	₹4,88,033.07
2	₹4,066.94	₹12,066.65	₹4,75,966.42
3	₹3,966.39	₹12,167.21	₹4,63,799.21
4	₹3,864.99	₹12,268.60	₹4,51,530.61
…	…	…	…
36	₹133.34	₹16,000.26	₹0.00

Early EMIs are mostly interest; later EMIs are mostly principal. Interest + principal is the same EMI in every row.

In A8:A43 type months 1 to 36 (type 1, then Home > Fill > Series, Step 1, Stop 36).
B8: =IPMT($B$3/12,A8,$B$4*12,-$B$2).
C8: =PPMT($B$3/12,A8,$B$4*12,-$B$2).
D8: =$B$2-SUM($C$8:C8) (loan minus principal paid so far).
Copy B8:D8 down to row 43. D43 shows 0.

Useful variations

Total interest: =PMT(B3/12,B4*12,-B2)*B4*12-B2.
Loan you can afford for an EMI: =PV(B3/12,B4*12,-EMI).
Months needed to repay with a given EMI: =NPER(B3/12,-EMI,B2).
Real loans may use a different day count, fees or a changing rate. Treat the sheet as a planning tool and use the lender's statement for exact figures.

Ravindra Bagale's Tip

Put the rate, years and loan in clearly coloured input cells and never type numbers inside PMT. When the bank changes the rate by 0.25%, you change one cell and the whole schedule is right again.

Practice task

Open the Loan sheet. Check B5 shows the EMI.
Check the schedule: B8 + C8 equals the EMI.
Check D43 is 0 and the interest total in B45 matches B6.
Change the rate to 9.5% and watch the EMI and total interest fall.
Find the months needed if Salman pays ₹20,000 a month, with NPER.

Learn it properly

Course lessons:

Frequently asked questions

Why is my PMT result negative?

Excel shows payments as negative. Use -B2 for the loan amount or put a minus sign before PMT.

Why is my EMI far too big?

You used the yearly rate with monthly periods. Divide the rate by 12 and multiply the years by 12.

How do I add a prepayment?

Build the schedule with formulas (interest = balance × rate/12) instead of IPMT, and subtract the prepayment from the balance in that month.

Is this EMI exactly what my bank charges?

It is the standard reducing-balance EMI. Banks may round differently or add fees, so small differences are normal.