IntermediateFinance
Syntax
=PMT(rate, nper, pv, [fv], [type])| Argument | What it means |
|---|---|
rate | Interest rate per period. For a yearly rate paid monthly, divide by 12. |
nper | Number of payments. |
pv | The loan amount today. Use a negative number to get a positive payment. |
fv | Optional. A balance left at the end. Usually 0. |
type | Optional. 0 for payments at the end of the period, 1 for the start. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Examples that need no data
=ROUND(PMT(0.05/12,60,-20000),2)Monthly payment on 20,000 over 5 years at 5%
=ROUND(PMT(0.05/12,60,-20000)*60-20000,2)Total interest paid over the life of the loan
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use PMT at work
- Finance and accounting
Compare a 3 year and 5 year loan.
=ROUND(PMT(0.06/12,36,-15000),2) - Personal budgeting
What a car loan will cost each month.
=ROUND(PMT(0.045/12,48,-30000),2)
Common mistakes and tips
- The rate and nper must use the same unit: monthly rate with number of months.
- PMT gives a negative number if the loan amount is positive. Put a minus before the loan amount or before PMT.
Works in
All versions of Excel, and Google Sheets.
Lessons that use it
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.