PMT function
PMT calculates the level payment needed to pay off a loan at a fixed interest rate. It is the classic tool for mortgage, car and personal loan quotes.
Example
Read it as a sentence: find the monthly payment on a 10000 loan paid off in 10 monthly payments at 8% a year, which gives -1037.03208935915.
A
B
C
D
1
-1037.03208935915
2
3
Syntax
PMT(rate, nper, pv, [fv], [type])
Arguments in [square brackets] are optional.
-
rate(required) - Interest rate per period.
-
nper(required) - Total number of payments.
-
pv(required) - Loan amount today.
-
fv(optional) - Balance wanted after the last payment; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
Returns: Fixed payment per period, negative for a positive loan.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=PMT(0.05/12,60,20000) | ($377.42) | Loan |
=PMT(0.05/12,60,20000,1000,1) | ($390.50) | Fv and type 1 |
=PMT(0,10,1000) | ($100.00) | Rate 0 |
=PMT(0.05,-10,1000) | $79.50 | Nper negative |
=PMT(0.05,10,1000,0,2) | ($123.34) | Type 2 (nonzero treated as 1?) |
=PMT(0.05,10,1000,0,-1) | ($123.34) | Type -1 |
=PMT(0.05,0,1000) | #NUM! | Nper 0 |
=PMT(-1,10,1000) | #NUM! | Rate -1 |
Watch out for
- Divide annual rates by 12 and use months for nper when paying monthly.
- The answer is negative; wrap in a minus sign to show a positive.
Where it works
- Excel
- PMT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .