Financialadvanced
Calculate internal rate of return with IRR
=IRR(B2:B7)
Excel how-to guide
How to pmt loan payment in Excel
Calculate monthly mortgage payments, car loan instalments, or any amortising loan payment from the interest rate, term, and principal.
Step-by-step instructions
How to pmt loan payment — 3 steps
1
Divide the annual interest rate by 12 to get the monthly rate (B1/12).
2
Multiply the loan term in years by 12 to get the total number of monthly payments (B2*12).
3
Enter the loan principal as a negative number — PMT treats outflows as negative, so negating the principal returns a positive payment.
Example data
Worked example
| Parameter | Value |
|---|---|
| Annual Rate | 6% |
| Term (years) | 30 |
| Loan Amount | 300000 |
| Monthly Payment | =PMT(6%/12,30*12,-300000) |
Common mistakes
Errors to watch out for
PMT returns a negative number
PMT returns a negative value when the principal (pv) is positive because it represents a cash outflow. Negate the principal (-B3) or wrap the result in ABS() to get a positive payment amount.
Wrong payment amount
Make sure the rate and nper are in the same time unit. For monthly payments, the rate must be monthly (annual rate / 12) and nper must be total months (years × 12).
FAQ
Frequently asked questions
How do I calculate an annual payment instead of monthly?
Use the annual rate directly and the total years as nper — =PMT(B1, B2, -B3) — without dividing or multiplying.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Financialadvanced
Calculate net present value with NPV
=NPV(B1,B3:B7)+B2