formulastreak

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.

Loan, savings and a project · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = $352.28
ABCDE
1InputValue
2Loan amount$15,000.00Monthly payment$352.28
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flow
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$7,000.00

Total interest

All the payments minus what you borrowed.

Loan, savings and a project · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = $1,909.22
ABCDE
1InputValue
2Loan amount$15,000.00Total interest$1,909.22
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flow
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$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.

Loan, savings and a project · edit E6 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE6 = $36,812.45
ABCDE
1InputValue
2Loan amount$15,000.00
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00Savings$36,812.45
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flow
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$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.

Loan, savings and a project · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = $75.00
ABCDE
1InputValue
2Loan amount$15,000.00Month 1 interest$75.00
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flow
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$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.

Loan, savings and a project · edit E10 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE10 = $2,856.10
ABCDE
1InputValue
2Loan amount$15,000.00
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flowNPV$2,856.10
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$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
Select E2 and type = to start its formula (in the cell or the formula bar)
ABCDE
1InputValue
2Loan amount$15,000.00Monthly payment
3Annual rate6.0%
4Years4
5
6Monthly saving$250.00
7Saving rate4.0%
8Saving years10
9Discount rate8%
10YearCash flow
110-$20,000.00
121$4,500.00
132$5,200.00
143$6,000.00
154$6,400.00
165$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.

Practise it now