PMT function

PMT syntax

Argument	Required	What it means
rate	Yes	The interest rate per period: the yearly rate divided by 12 for monthly payments.
nper	Yes	The number of payments: years × 12 for monthly.
pv	Yes	The loan amount. Enter it as negative to get a positive payment.
fv	No	What's left after the last payment. 0 for a loan paid off in full (the default).
type	No	0 for payments at the end of each period (the default), 1 for the start.

Examples

Monthly payment on a car loan

25,000 over 5 years at 6% a year. Rate and term are converted to months; the minus sign makes the payment positive.

B4fx=PMT(B2/12,B3*12,-B1)

Row	A	B
1	Loan amount	$25,000.00
2	Yearly rate	6.0%
3	Years	5
4	Monthly payment	$483.32

=PMT(B2/12,B3*12,-B1) → $483.32Copy

Total interest paid

Every payment times the number of payments, minus what was borrowed.

B5fx=B4*B3*12-B1

Row	A	B
1	Loan amount	$25,000.00
2	Yearly rate	6.0%
3	Years	5
4	Monthly payment	$483.32
5	Total interest	$3,999.20

=B4*B3*12-B1 → $3,999.20Copy

Practise it

New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.

Your task: In B4, return the monthly payment on the mortgage described above it.

Graded live

B4fx

Row	A	B
1	Loan amount	$240,000
2	Yearly rate	4.50%
3	Years	25
4	Monthly payment

Write the formula in the bar above, then press Check or Enter.

Hint

Monthly rate is B2/12, the number of payments is B3*12, and the loan B1 goes in with a minus sign.

Solution

=PMT(B2/12,B3*12,-B1) Any formula that returns the same result passes.

Check

Common mistakes

1. Yearly rate with monthly payments. Divide the rate by 12 and multiply the years by 12, both, or the payment is far too high.
2. A negative payment. Excel treats money you receive (the loan) and money you pay with opposite signs. Enter pv as negative, or put a minus before PMT.
3. Typing the rate as 6 instead of 6%. 6 means 600%. Format the cell as a percentage or type 0.06.

Try it with your own numbers

The loan payment calculator works out the payment, the total interest and the full repayment schedule for your own loan, and prints the PMT formula that gives the same payment.