ƒx SheetOps
ƒx

Excel PMT Formula

finance Difficulty:
One-Click Copy
=PMT(rate, nper, pv, [fv], [type])

Quick context

  • • Works in both Excel and Google Sheets.
  • • Use when you need consistent, auditable results.
  • • Copy the snippet above and adjust only the ranges.

When to use

Calculate loan payments with fixed interest rates.

Mastering the PMT Function

The PMT function is essential for calculating loan payments (mortgages, car loans). It assumes constant payments and a constant interest rate.

Syntax Breakdown

  • rate: The interest rate for the loan. (Divide by 12 for monthly payments!)
  • nper: Total number of payments for the loan.
  • pv: Present Value (the loan amount). Enter as positive, result/payment will be negative (cash outflow).

Pro Tip: Use =IPMT and =PPMT to split the principal and interest portions.

Disclaimer: While we strive for accuracy, these formulas are provided "as is" without warranty of any kind. Please verify all results before use.

Common Mistakes

  • ✕ Inconsistent rate and periods. If payments are monthly, you MUST divide the annual interest rate by 12. Using an annual rate for monthly payments creates huge errors.
  • ✕ Positive PV. The Present Value (loan amount) should usually be entered as a negative number, representing cash owed/outflow.

Best Practices

  • ✓ Divide annual rate by 12. Always verify your time units match: monthly rate for monthly payments, annual rate for annual payments.

Manual Risk Detected

Building a loan calculator? Don't reinvent the wheel. Use dedicated finance software.

See finance Alternatives