The formula
Monthly paymentExcelGoogle Sheets
=PMT(B1/12,B2*12,-B3)In E1 → $1,896.20
Total paid over the loanExcelGoogle Sheets
=PMT(B1/12,B2*12,-B3)*B2*12In E2 → $682,633.47
Total interestExcelGoogle Sheets
=PMT(B1/12,B2*12,-B3)*B2*12-B3In E3 → $382,633.47
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Annual rate | 6.5% | $1,896.20 | ||
| 2 | Years | 30 | $682,633.47 | ||
| 3 | Loan amount | 300,000 | $382,633.47 |
How it works
- B1/12
- Monthly interest rate (annual rate ÷ 12).
- B2*12
- Number of monthly payments.
- -B3
- Loan amount, negative so the payment comes out positive (money out vs in).
Common errors and fixes
Negative payment?
Normal Excel sign convention. Put a minus before the loan amount or before PMT.
Payment far too high?
You used the annual rate with monthly periods. Divide the rate by 12.
Why does the formula return #NUM!?
Number of periods is 0 or negative.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.