PMT: loan payments (and FV, NPV, IRR)
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=PMT(annual_rate/12, years*12, loan) is the monthly payment (negative = money out). FV(rate, periods, -payment) grows savings, NPER counts payments, NPV(rate, years 1+) + year 0 values a project and IRR(all flows) is its return.
Spreadsheet finance functions all follow two rules: use the rate per period (monthly rate for monthly payments), and money paid out is negative.
The monthly payment
PMT(rate/12, years*12, amount) is the monthly payment. It's negative because it's money going out; put a minus in front for a positive figure.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | Monthly payment | $352.28 | |
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | |||
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | |||
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Total interest
All the payments minus what you borrowed.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | Total interest | $1,909.22 | |
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | |||
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | |||
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Savings growth: FV
FV(rate/12, months, -monthly_saving) is what regular saving grows to. For a single deposit, give a present value instead of a payment: FV(rate/12, months, 0, -deposit), the same as deposit*(1+rate/12)^months.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | |||
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | Savings | $36,812.45 | |
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | |||
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Months to repay, and where each payment goes
NPER(rate/12, -payment, amount) counts the monthly payments a loan takes. IPMT(rate/12, month, years*12, amount) is the interest part of one month's payment and PPMT(…), with the same arguments, the principal part; together they make PMT. Early payments are mostly interest.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | Month 1 interest | $75.00 | |
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | |||
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | |||
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Is a project worth it? NPV and IRR
NPV(rate, cash flows from year 1) plus the year-0 amount gives today's value; positive means it beats the rate. IRR(all cash flows) is the project's own return.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | |||
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | |||
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | NPV | $2,856.10 | |
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Check yourself
One real exercise from the “Money and finance” unit. Checked on our server; no account needed.Show the monthly payment as a positive amount in E2.
- Use PMT
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Loan amount | $15,000.00 | Monthly payment | ||
| 3 | Annual rate | 6.0% | |||
| 4 | Years | 4 | |||
| 5 | |||||
| 6 | Monthly saving | $250.00 | |||
| 7 | Saving rate | 4.0% | |||
| 8 | Saving years | 10 | |||
| 9 | Discount rate | 8% | |||
| 10 | Year | Cash flow | |||
| 11 | 0 | -$20,000.00 | |||
| 12 | 1 | $4,500.00 | |||
| 13 | 2 | $5,200.00 | |||
| 14 | 3 | $6,000.00 | |||
| 15 | 4 | $6,400.00 | |||
| 16 | 5 | $7,000.00 |
Common mistakes
Using the yearly rate with monthly periods (or the other way round).
Putting the year-0 investment inside NPV, which discounts it a year too many.
Mixing up signs: deposits and payments out are negative.
Questions
Why is PMT negative?+
Finance functions follow the cash: what you pay out is negative, what you receive is positive.
Do these functions work in Google Sheets?+
Yes, Google Sheets has PMT, FV, PV, NPER, RATE, NPV, IRR, IPMT and PPMT.
Why doesn't my total interest match the bank's?+
Lenders round each payment to cents. Round the payment with ROUND(…, 2) before multiplying by the number of payments.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.