The PMT function
The monthly payment of a fixed-rate loan: the amount, the rate, the term, and Excel does the rest.
Syntax
=PMT(rate, nper, pv, [fv], [type])
In a French Excel: =VPM(taux; npm; va; [vc]; [type]).
Arguments
- rate: the rate per period; an annual rate is divided by 12 for monthly payments.
- nper: the number of periods (months).
- pv: the amount borrowed.
- fv: the balance wanted at the end; 0 by default.
An example
=-PMT(3.5%/12,20*12,200000)
The monthly payment of a 200,000 loan at 3.5% over 20 years, made positive by the minus sign.
Pitfalls
- The annual rate passed as is gives a huge payment: divide by 12.
- The result is negative: it is money going out; a leading minus makes it positive.
Going further
Frequently asked questions
- How do I get the interest part of a payment?
- IPMT:
=-IPMT(3.5%/12,1,240,200000)for the first payment.