PMT
Calculates the periodic payment for a loan or investment based on constant payments and a constant interest rate.
PMT(rate, nper, pv, [fv], [type])Arguments
rateInterest rate per period (e.g. annual rate / 12 for monthly)nperTotal number of payment periodspvPresent value (loan amount)fvFuture value (default 0)(optional)type0 = end of period, 1 = beginning of period(optional)
=PMT(5%/12, 360, 200000)Monthly payment on a $200K loan at 5% over 30 years
=PMT(3%/12, 60, 25000)Monthly payment on a $25K car loan at 3% over 5 years
=PMT(6%/12, 120, 0, 100000)Monthly savings needed to reach $100K in 10 years at 6%
- •Divide annual rate by 12 for monthly payments
- •Result is negative because it represents money going out
- •Use ABS() to get a positive number if needed
- •Mixing up the sign convention - PMT returns a negative number representing a cash outflow, and forgetting this leads to confusing subtraction errors when combining it with other cash flow figures
- •Forgetting to divide an annual interest rate by the number of payments per year (and multiply the term by the same number) when payments are monthly - using the raw annual rate with a monthly term produces a wildly wrong payment amount
- •Omitting the type argument when payments are due at the beginning of the period instead of the end, which changes the exact payment amount, especially for leases or rent calculations
PVCalculates the current value of a loan or investment, the present-value counterpart to PMT's periodic payment calculation.FVCalculates the future value building up from regular payments, using the same rate/nper/pmt structure as PMT.RATESolves for the interest rate instead of the payment amount, useful when the payment, term, and loan amount are already known.Why does PMT return a negative number?
PMT follows the convention that money paid out is negative and money received is positive. If your loan amount (pv) is entered as positive, the resulting payment comes back negative to represent an outflow.
Why is my PMT result way too high?
You're likely using an annual interest rate with a monthly term. Divide the annual rate by 12 and multiply the number of years by 12 to match monthly payment periods.
What does the type argument control in PMT?
It sets whether payments are due at the end of each period (0, the default) or the beginning (1) - the small difference in timing changes the exact payment amount.
Need to translate a formula using PMT?
Use our translator to convert your complete formula