How It Works

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. This is the same calculation banks use for mortgages, car loans, and personal loans.

Syntax

=PMT(rate, nper, pv, [fv], [type])

rate: Interest rate per period (annual rate ÷ 12 for monthly)
nper: Total number of payments (years × 12 for monthly)
pv: Present value (loan amount) - use negative for positive result
fv: Future value (optional, usually 0)
type: When payments are due (optional, 0=end of period)

Example

Car Loan:

Principal: $25,000
Annual Interest Rate: 6.5%
Term: 5 years (60 months)

Formula: =PMT(6.5%/12, 60, -25000)

Result: $489.15 per month

Common Scenarios

Mortgage Payment

30-year mortgage for $350,000 at 7% interest:

=PMT(7%/12, 360, -350000)

Result: $2,328.56/month

Student Loan

$40,000 loan at 5% over 10 years:

=PMT(5%/12, 120, -40000)

Result: $424.26/month

Credit Card Payoff

$8,000 balance at 22% APR, pay off in 3 years:

=PMT(22%/12, 36, -8000)

Result: $305.96/month

Variations

Using Cell References

=PMT(B1/12, B2*12, -B3)

Where:

B1 = Annual interest rate (e.g., 6.5%)
B2 = Loan term in years (e.g., 5)
B3 = Loan amount (e.g., 25000)

Calculate Bi-Weekly Payments

=PMT(rate/26, years*26, -principal)

Divide rate by 26 and multiply years by 26 for bi-weekly payments.

Pro Tips

Use negative principal to get a positive payment result
Include extra payments: Add to PMT result for faster payoff
Compare loan options by calculating PMT for different rates/terms
Verify lender quotes - your calculation should match their disclosed payment

Understanding the Output

The PMT result includes:

Principal repayment
Interest charges

It does NOT include:

Property taxes
Insurance (PMI, homeowners)
HOA fees

For mortgages, your actual payment may be higher due to escrow.