PMT Function in Excel: Loan & Mortgage Payment Calculator
Calculates the payment for a loan based on constant payments and a constant interest rate.
How to Calculate Loan Payments with PMT in Excel
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax & Arguments
=PMT(rate, nper, pv, [fv], [type])
- rate: Interest rate per period (Annual Rate / 12 for monthly payments)
- nper: Total number of payment periods (Years * 12)
- pv: Present value or principal loan amount
Example Formula
=PMT(5%/12, 360, -300000) calculates the monthly payment on a $300,000 30-year fixed mortgage at 5% interest ($1,610.46/mo).
Common Errors & Fixes
Payment seems too high or too low
Causes:- Annual rate not converted to monthly rate.
- Number of payments not matching the rate period.
Fixes:- Convert annual rate: rate/12 for monthly payments.
- Match nper to rate: 12 months × years for monthly payments, or use 12 for 12 monthly payments.
Frequently Asked Questions
What does PMT calculate?
PMT calculates the constant periodic payment for a loan, such as the monthly payment for a mortgage or car loan.
How do I convert an annual rate to a monthly rate for PMT?
Divide the annual rate by 12. For example, 6% annual becomes 6%/12. Also multiply the number of years by 12 for the nper.
Why is the PMT result negative?
PMT returns a negative number by convention because it represents an outgoing payment (cash outflow). To get a positive result, use -PMT(...) or negate the loan amount.
Related Formulas
Explore related formula generators to solve similar problems
🛠️ Related Tools
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More