Cellwise

Finance function

Excel PMT function

PMT works out the regular payment for a loan with a fixed interest rate and a fixed number of payments. Use it to compare car loans, mortgages, or equipment financing before you sign.

fx=PMT(rate, nper, pv, [fv], [type])

Arguments

rate
The interest rate per period. Divide an annual rate by 12 for monthly payments.
nper
The total number of payments, like years times 12.
pv
The loan amount, or present value.
fv
Optional. A balance left after the last payment. Usually 0.
type
Optional. 0 for payments at the end of each period (default), 1 for the start.

PMT examples

Every result below was calculated live from this sample sheet.

ABCD
1LoanAmountAnnual rateYears
2Car250000.065
3Mortgage3000000.06530
4Equipment180000.083
  1. fx=ROUND(PMT(C2/12,D2*12,B2),2)

    Result: -483.32

    The car loan costs 483.32 a month. It is negative because the money leaves you.

  2. fx=ROUND(PMT(C3/12,D3*12,-B3),2)

    Result: 1896.2

    Make the loan amount negative to get a positive payment: 1,896.20 a month.

  3. fx=ROUND(-PMT(C4/12,D4*12,B4)*D4*12-B4,2)

    Result: 2305.96

    Total interest on the equipment loan: all payments minus the amount borrowed.

How to use PMT

  1. Click the cell for the payment.
  2. Type =PMT( and the annual rate divided by 12.
  3. Add the number of payments, years times 12.
  4. Add the loan amount. Put a minus in front if you want a positive result.
  5. Press Enter.

Common PMT mistakes

Using the annual rate for monthly payments

Divide the rate by 12 and multiply the years by 12 so both are in months.

Being confused by the negative result

PMT shows money paid out as negative. Use -PMT(...) or a negative loan amount to flip the sign.

Typing 6 for 6%

Excel reads 6 as 600%. Type 6% or 0.06.

PMT FAQ

How do I calculate a monthly loan payment in Excel?

Use =PMT(annual_rate/12, years*12, -loan_amount). For example, =PMT(6%/12,60,-25000) gives about 483.32.

Why is my PMT result negative?

Excel treats money you pay out as negative. Put a minus before PMT or before the loan amount to show it as positive.

Does PMT include taxes and insurance?

No. PMT covers principal and interest only. Add property tax, insurance, and fees separately.

Practice PMT in these lessons

Guides that use PMT

Related functions

Updated 2026-10-31