PMT Function in Excel & Sheets
The PMT function calculates periodic payments for loans with constant payments and interest rate. Master loan calculations with examples.
Excel
Google SheetsExcelGoogle Sheets=PMT(rate, nper, pv, [fv], [type])Real-World PMT Examples
Monthly Mortgage Payment
Calculate monthly payment for a $300,000 home loan at 4.5% for 30 years
Car Loan Payment Calculation
Monthly payment for a $25,000 car loan at 3.5% for 5 years
Monthly Savings Goal
Calculate monthly savings needed to accumulate $50,000 in 8 years at 6% annual return
Business Equipment Loan
Quarterly payments for $100,000 equipment financing at 7% for 4 years
Education Loan with Beginning Payments
Monthly payment for $40,000 student loan at 5.5%, payments start immediately
Common PMT Errors and Solutions
Non-numeric values in PMT parameters
Text values, cell references to text, or formulas returning non-numeric results in rate, nper, or pv parameters
1. Check all parameters are numbers 2. Verify cell references contain numeric values 3. Use VALUE() function to convert text numbers 4. Remove any currency symbols ($) from input cells
Use data validation to ensure numeric-only input in parameter cells
Example:
Invalid calculation parameters
Mathematical impossibility such as negative interest rate, zero periods, or extremely large numbers causing overflow
1. Ensure interest rate is positive 2. Verify number of periods > 0 3. Check for reasonable loan amounts 4. Use realistic interest rates (0.1% to 30%)
Add input validation: rate between 0-1, periods > 0, principal ≠ 0
Example:
Division by zero in rate calculation
Interest rate of exactly 0%, or rate calculation resulting in zero when dividing annual rate by periods
1. For 0% interest loans, use simple division: =pv/nper 2. Check rate cell isn't empty or zero 3. Verify annual rate division (rate/12) isn't zero
Use conditional logic to handle zero-interest scenarios separately
Example:
Missing required parameters
One of the three required parameters (rate, nper, pv) is missing, blank, or references an empty cell
1. Verify all three required parameters are provided 2. Check cell references point to cells with values 3. Ensure no blank cells in parameter range 4. Use ISBLANK() to check for missing values
Build input validation that requires all three parameters before calculation
Example:
Payment shows as positive when expecting negative
Present value entered as negative instead of positive, or misunderstanding of cash flow direction
1. For loans: Enter loan amount as positive 2. For investments: Enter initial value as negative 3. Use minus sign before PMT to flip sign if needed 4. Remember: Outgoing payments are negative
Document cash flow direction clearly and use consistent sign conventions
Example:
PMT Best Practices and Advanced Tips
The most critical rule: your interest rate and payment periods must use the same time unit. For monthly payments, divide annual rate by 12. For quarterly, divide by 4.
Instead of hardcoding values, reference cells to create interactive loan calculators. This allows easy scenario analysis by changing inputs.
Combine PMT with PPMT and IPMT functions to create full amortization schedules showing principal and interest breakdown for each payment.
PMT calculates only principal and interest. For complete budgeting, add property taxes, insurance, HOA fees, and other costs separately.
Use Excel/Sheets data tables to analyze how different interest rates or loan terms affect payments. Create a matrix showing payment variations.
Wrap PMT in IFERROR to handle invalid inputs gracefully and provide user-friendly error messages for loan calculators.
Multiply PMT result by number of periods, then subtract principal to find total interest cost over the loan life.
Need Help with PMT Function in Excel & Sheets?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula: