Calculate a Loan Payment (PMT)

Excel Formulas › Financial

All versionsPMT

The PMT function calculates the fixed periodic payment on a loan — a car loan, a mortgage — from the rate, the number of payments, and the amount borrowed. The one trick everyone trips on: the rate must match the payment period.

Quick formula: for an annual rate in B1, years in B2, and loan amount in B3:
=PMT(B1/12, B2*12, B3)
Divide the annual rate by 12 and multiply the years by 12 so everything is in months. The result is negative (money leaving you).

Functions used (tap for the full reference guide):

PMTIPMTPPMT

The example

A $25,000 loan at 6% annual interest over 5 years.

AB
1Annual rate6%
2Years5
3Loan amount$25,000
4Monthly payment-$483.32

The formula

The monthly payment:

=PMT(B1/12, B2*12, B3) // 6%/12, 5×12 months, $25,000 → -$483.32

How it works

PMT’s three core arguments must all speak the same time unit:

  1. Rate per period: B1/12 turns the 6% annual rate into 0.5% per month.
  2. Number of periods: B2*12 turns 5 years into 60 monthly payments.
  3. Present value: B3 is the amount borrowed, $25,000.
  4. PMT returns -483.32 — negative because it’s cash flowing out. Wrap in -PMT(…) or ABS() if you want it positive.

Split a payment into interest and principal with the matching functions: =IPMT(rate, period, nper, pv) for the interest portion of a given payment, and =PPMT(…) for the principal.

Try it: interactive demo

Live demo

Adjust the loan; see the monthly payment.

Monthly payment:

Variations

Show the payment as a positive number

Negate it:

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

Include a final balloon / future value

The 4th argument is the remaining balance at the end:

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

Payments at the start of each period

Set the 5th argument to 1 for payments due at the beginning:

=PMT(B1/12, B2*12, B3, 0, 1)

Pitfalls & errors

Wildly wrong payment. Almost always a rate/period mismatch — using the annual rate B1 with monthly periods. Convert: rate /12 and periods ×12 for monthly.

The result is negative on purpose. PMT follows cash-flow sign conventions: money you pay out is negative. Use -PMT() for a positive figure.

Rate is a decimal. 6% must be entered as 0.06 (or a cell formatted as 6%), not the number 6.

Practice workbook

📊
Download the free Calculate a Loan Payment (PMT) practice workbook
The loan calculator with live PMT, the positive and balloon variants, and IPMT/PPMT, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a loan payment in Excel?
Use =PMT(rate/12, years*12, amount) for a monthly payment, e.g. =PMT(6%/12, 5*12, 25000). Convert the annual rate and term to months so the periods match.
Why is my PMT result negative?
PMT uses cash-flow sign conventions, so a payment you make is shown as negative. Put a minus sign in front, =-PMT(...), to display it as positive.
How do I split a payment into principal and interest?
Use PPMT for the principal portion and IPMT for the interest portion of a specific payment number, with the same rate, nper, and pv arguments.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Future value of savings · Percent change

Function references: PMT · IPMT · PPMT