Spreadsheet Practice

How to calculate a loan payment in Excel

Formula (Excel and Google Sheets)

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

Use PMT with the monthly rate, the number of months and the amount: =PMT(B3/12,B4*12,-B2) returns the monthly payment.

Result on the example
$489.15 in B5
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

PMT needs the rate per period and the number of periods in the same unit as the payments. Payments are monthly, so the annual rate is divided by 12 and the years multiplied by 12. PMT treats money you receive as positive and money you pay as negative, so the loan amount goes in as -B2 to get a positive payment.

The payment covers interest and principal; it doesn't include fees, taxes or insurance.

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

Result489.1537055

  1. B36.50%

    The value in B3.

  2. B3/120.005416666667

    The / operator divides.

  3. B45

    The value in B4.

  4. B4*1260

    The * operator multiplies.

  5. B2$25,000

    The value in B2.

  6. -B2-25000

    Makes the value negative.

  7. PMT(B3/12,B4*12,-B2)489.1537055

    PMT: Payment per period for a loan or annuity.

    • rateB3/120.005416666667
    • nperB4*1260
    • pv-B2-25000

In Google Sheets

The same formula works in Google Sheets: =PMT(B3/12,B4*12,-B2). We ran it in Google Sheets mode on the same data and it returns $489.15.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=-PMT(B3/12,B4*12,B2)$489.15Flip the sign of the result instead of the amount. Same payment.
=B2*(B3/12)/(1-(1+B3/12)^(-B4*12))$489.15The annuity formula PMT uses, written out. Useful to check a result by hand.

Common mistakes

  • Using the annual rate as is, PMT(B3,B4*12,-B2), charges 6.5% a month and gives a payment of over $1,600.
  • Forgetting to multiply the years by 12 gives the payment for a 5-month loan.
  • A negative result isn't wrong, it's PMT's sign convention: payments go out. Put a minus in front of the amount or the function.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate a loan payment

In B5, calculate the monthly payment for the loan: amount in B2, annual rate in B3, term in years in B4.

Questions

How do I calculate the total cost of the loan?

Multiply the payment by the number of payments: =B5*B4*12. Subtract B2 for the total interest.

How do I calculate weekly or biweekly payments?

Use 52 or 26 periods a year: =PMT(B3/26,B4*26,-B2).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.