PMT
Calculate the fixed periodic payment for a loan or annuity from the rate, the number of periods and the amount borrowed.
Syntax
PMT(rate, nper, pv, [fv], [type])| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. For monthly payments on an annual rate, divide by 12. |
nper | Required | The total number of payments. A 30-year monthly mortgage is 360. |
pv | Required | The present value — the amount borrowed. |
fv | Optional | The balance to leave at the end. Defaults to 0, a loan paid off in full. |
type | Optional | 0 (the default) if payments fall at the end of each period, 1 if at the start. |
Examples
Every example below was executed against VisiGrid engine 0.51.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=PMT(0.05/12,360,200000) | -1073.64 | A 200,000 mortgage at 5% over 30 years. Negative because it is money going out. |
=PMT(A2/12,B2*12,C2) | -1073.64 | The same loan with its terms in cells, converted to months inside the formula. |
=-PMT(0.06,10,100000) | 13586.80 | An annual payment, negated to read as a positive amount. |
=PMT(0.05,10,0,10000) | -795.05 | Saving rather than borrowing: what to put away each year to reach 10,000. |
=PMT(0,12,1200) | -100 | At zero interest it is simply the amount divided by the number of payments. |
Match the rate to the period
The most common PMT mistake is an annual rate against monthly periods.
PMT(0.05,360,200000) charges 5% a month for 360 months and returns a
payment of about 10,000. Divide the rate by 12 and give the term in months, and
both arguments describe the same period:
=PMT(0.05/12,360,200000) → -1073.64Why the answer is negative
The financial functions use the sign of cash flow. The loan arrives as money in
(positive pv), so the payments are money out, and come back negative. It is a
convention, not an error, and it is what lets PMT, PV and
FV be chained without sign juggling. To display the payment
as a positive amount, negate the result, or enter the loan as a negative pv.
Interest and principal
The payment is constant, but what it pays for changes: early payments are mostly
interest, later ones mostly principal. IPMT and
PPMT split any single payment, and
CUMIPMT totals the interest over a stretch of them.
Excel compatibility
Matches Excel, including the sign convention and the optional fv and type arguments.