Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .