Finance formula
PMT in Excel and Google Sheets
Calculate the monthly payment for a loan with a fixed rate.
Syntax
=PMT(rate, nper, pv, [fv], [type])- rate
- Interest per period, e.g. annual rate / 12.
- nper
- Number of payments.
- pv
- Loan amount. Use a minus sign for a positive result.
Example
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Loan | Annual rate | Years | Monthly payment |
| 2 | 300,000 | 0.05 | 10 |
D2 =PMT(B2/12,C2*12,-A2)
Result: 3181.97
Monthly payment on 300,000 at 5% over 10 years.
Ready-to-copy formulas
Monthly savings to reach a goal
=PMT(B2/12,C2*12,0,-A2)
Tips
- Keep the rate and the number of periods in the same unit (both monthly).
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.