Excel FormulasPMT function in Excel
PMT works out the fixed payment for a loan from the interest rate, the number of payments and the amount borrowed.
PMT answers the question everyone asks before borrowing: "what will the instalment be?" Given the loan amount, the interest rate and the number of payments, it returns the fixed amount to be paid each period. It is the formula behind an EMI on a reducing balance.
This page explains the arithmetic of the function. It is not financial advice. A real loan may add fees, insurance and taxes, and lenders round and schedule payments in their own ways, so the figure on a loan offer can differ a little. Take the lender's own statement as the figure that counts.
Syntax
=PMT(rate, nper, pv, [fv], [type])| Argument | What it is |
|---|---|
rate | The interest rate for one payment period. For monthly payments, the yearly rate divided by 12. |
nper | The total number of payments. For monthly payments, years times 12. |
pv | The present value: the amount borrowed. |
fv | The balance wanted after the last payment. 0 if left out, which is right for a loan that is paid off. |
type | 0 or left out if payments are made at the end of each period, 1 if at the beginning. |
Examples
Each result below is what Excel itself showed for the formula on the sample sheet.
| A | B | |
|---|---|---|
| 1 | Loan amount | 500000 |
| 2 | Interest rate per year | 0.09 |
| 3 | Years | 5 |
=PMT(B2/12,B3*12,B1)Result: -10379.18The monthly instalment on 5,00,000 at 9 percent a year for 5 years. It is negative because it is money you pay out.
=-PMT(B2/12,B3*12,B1)Result: 10379.18A minus sign in front shows it as a positive figure.
=ROUND(-PMT(B2/12,B3*12,B1),0)Result: 10379Rounded to the nearest rupee.
=-PMT(B2/12,B3*12,B1)*B3*12Result: 622750.66The total paid over the five years: the instalment times 60.
=-PMT(B2/12,B3*12,B1)*B3*12-B1Result: 122750.66The total interest: everything paid, less the amount borrowed.
=-PMT(B2/12,10*12,B1)Result: 6333.79The same loan over 10 years: a smaller instalment.
=-PMT(B2/12,10*12,B1)*120-B1Result: 260054.64But much more interest in total.
=-PMT(B2,B3,B1)Result: 128546.23Yearly payments: the yearly rate and the number of years, with no dividing or multiplying.
The three figures, in matching units
The one rule that matters: the rate and the number of payments must be for the same period.
| Payments are | rate | nper |
|---|---|---|
| monthly | yearly rate / 12 | years * 12 |
| quarterly | yearly rate / 4 | years * 4 |
| yearly | yearly rate | years |
Using the yearly rate with a count of months is the classic mistake. It produces an absurdly large instalment.
Enter the rate as a percentage or a decimal: 9% or 0.09. Typing 9 means nine hundred percent.
Why the answer is negative
Excel's financial functions follow the direction of the money. The loan arrives in your pocket, so it is positive. The instalments leave it, so they are negative. Put a minus sign in front of PMT, or in front of the loan amount, to see a positive figure.
What the instalment is made of
Each instalment pays some interest and some of the amount borrowed. Early on, most of it is interest. As the balance falls, more goes towards the loan itself. Two companion functions split any single instalment:
=IPMT(rate, period, nper, pv)is the interest part of the instalment numberedperiod;=PPMT(rate, period, nper, pv)is the part that repays the loan.
The two always add up to PMT. A repayment schedule lists the period numbers down a column, with IPMT, PPMT and the remaining balance beside each.
Comparing options
The last examples compare five years with ten. A longer term lowers the instalment and raises the total interest, often by a great deal. A small table with terms down the side and PMT beside each, plus the total paid, makes the trade-off plain. Put the rate and amount in cells, not in the formula, so that you can change them and watch the whole table respond.
Working backwards
The same family of functions solves for any one unknown.
| To find | Use |
|---|---|
| the instalment | PMT |
| how much you can borrow for a given instalment | PV |
| how many payments it will take | NPER |
| the interest rate implied by an offer | RATE |
| what regular savings will grow to | FV |
Saving towards a target is PMT with the future value filled in: =-PMT(rate/12, months, 0, target) is the amount to set aside each month to reach the target, at an assumed steady rate of return.
Common mistakes
- A yearly rate with monthly payments, or the reverse.
- The rate typed as 9 instead of 9%.
- Surprise at the negative sign.
#NUM!when a figure is impossible, such as a negative number of payments.- Treating the result as the full cost of a loan, ignoring charges that PMT knows nothing about.
In Google Sheets
PMT is the same in Google Sheets, as are IPMT, PPMT, PV, FV, NPER and RATE.
Related functions
Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.