Sheet ReserveSpreadsheets worth keeping.

Spreadsheet skills

How do I calculate a loan payment with PMT?

Short answer

Use =PMT(rate/12, years*12, -loan_amount) for a fixed-rate loan with monthly payments. Divide the annual rate by 12, multiply the years by 12, and enter the loan amount as a negative number so the payment comes out positive. For 250,000 at 6.5 percent over 30 years, the payment is 1,580.17 per month.

Updated

PMT returns the constant periodic payment that pays off a loan with a fixed interest rate. It exists in Excel, Google Sheets, LibreOffice Calc and Numbers, and takes the same first three arguments in all of them.

The syntax

=PMT(rate, nper, pv, [fv], [type])
Argument Meaning
rate Interest rate per period, not per year
nper Total number of payment periods
pv Present value, which is the amount borrowed
fv Optional balance remaining after the last payment. The default is 0
type Optional. 0 means payments at the end of each period (default), 1 means at the beginning

For monthly payments on a loan quoted with an annual rate, the rate and the periods must both be converted to months.

Step by step

  1. Put the loan amount in B2, the annual rate in B3 and the term in years in B4. Enter the rate as a percentage, such as 6.5%, or as 0.065. Typing 6.5 in a cell that is not formatted as a percentage means 650 percent.
  2. In B5, enter:
=PMT(B3/12, B4*12, -B2)
  1. Format B5 as currency, or round it with =ROUND(PMT(B3/12,B4*12,-B2),2) if you need a payment in whole cents.

The minus sign in front of B2 matters. PMT follows a cash-flow convention: money you receive is positive and money you pay is negative. Without the minus sign, the result is negative, which is correct but usually unwanted in a table.

Worked example

A loan of 250,000 at 6.5 percent annual interest, repaid monthly.

Term nper Monthly payment Total paid Total interest
30 years 360 1,580.17 568,861.20 318,861.20
15 years 180 2,177.77 391,998.60 141,998.60

The monthly rate is 0.065 / 12 = 0.0054167. In the first month of the 30-year loan, interest is 250,000 × 0.0054167 = 1,354.17, so only 226.00 of the 1,580.17 payment reduces the balance. The shorter term costs 597.60 more per month and saves 176,862.60 in interest. Total interest equals payment × nper minus the amount borrowed.

Variations

  • Biweekly or quarterly payments: use 26 or 4 for the periods per year in place of 12, in both the rate and the term.
  • A balloon payment: put the amount owed at the end in fv as a positive number. For example, =PMT(B3/12,B4*12,-B2,B6) where B6 is the balloon.
  • Payments at the start of the period, as in many leases: set type to 1.
  • To see how each payment splits between interest and principal, use IPMT and PPMT with the same arguments plus the period number. The post on loan amortization with PMT, IPMT and PPMT walks through it.

Pitfalls

  • Forgetting to divide the annual rate by 12 or multiply the years by 12 is the most common error. The result looks plausible but is far too large or too small.
  • A rate typed as 6.5 rather than 6.5% or 0.065 gives a nonsense payment.
  • PMT covers principal and interest only. A mortgage payment also includes property taxes and insurance when those are held in escrow, and some loans add mortgage insurance.
  • Lenders round the payment to cents, so the final payment differs slightly from the others.
  • The formula assumes a fixed rate and equal payments. It does not fit adjustable-rate loans, interest-only periods or irregular extra payments.

A full month-by-month table built on this formula is in what an amortization schedule is, and the loan amortization schedule template has the formulas already set up.