Quick Navigation
PMT Function
Summary
The PMT function calculates the periodic payment for a loan or investment based on constant payments and a fixed interest rate. It's essential for financial planning, helping users determine affordable loan payments or required savings contributions.
Syntax
PMT(rate, nper, pv, [fv], [type])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| rate | Number |
Yes | Interest rate per period (e.g., annual rate/12 for monthly payments) |
| nper | Number |
Yes | Total number of payment periods (e.g., years*12 for monthly) |
| pv | Number |
Yes | Present value or principal amount - positive for loans, negative for investments |
| fv | Number |
No | Future value (default 0) - target balance after final payment |
| type | Number |
No | 0=end of period (default), 1=beginning of period |
Using the PMT Function
PMT is perfect for personal finance calculations like mortgage payments, car loans, or student loans. Use positive PV values for loans (money received) and negative for investments (money saved). Always match rate and nper time units.
Common PMT Examples
Monthly Car Loan Payment
=PMT(A2/12,A3,A4)Calculates monthly payment for $10,000 loan at 8% annual rate over 10 months. Result: ($1,037.03) - negative indicates cash outflow.
Payment Due at Period Start
=PMT(A2/12,A3,A4,,1)Same loan but payments due at beginning of each month. Result: ($1,030.16) - slightly lower due to earlier payments.
Monthly Savings for Future Goal
=PMT(A9/12,A10*12,0,A11)Monthly savings needed at 6% annual rate to accumulate $50,000 in 18 years. Result: ($129.08).
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Non-numeric arguments or rate=0 with nper>1
Solution: Verify all inputs are numbers and rate>0 or nper=1
#NUM! error
Cause: Invalid rate/nper combination (rate<0 or nper<1)
Solution: Ensure positive rate and nper values
Wrong payment amount
Cause: Mismatched rate/nper units (monthly rate with yearly nper)
Solution: Use consistent units: annual_rate/12 with years*12
Notes
- PMT includes principal + interest only (no taxes/insurance)
- Cash flow sign convention: +PV=loan received, -PV=money invested
- Total loan cost = PMT result * nper * -1
- Excel 2007+ compatible across all versions
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+