MS Excel: PMT function to calculate the fixed payment

PMT stands for "Payment". It calculates the fixed payment required to completely pay off a loan or investment over time, based on constant payments and a constant interest rate.

PMT tells you how much you need to pay each period (month, year, etc.) to pay off a loan or achieve an investment goal.

✅ Need to find loan payments ➔ Use PMT

✅ Need to plan investment contributions ➔ Use PMT

Whether you're buying a house, financing a car, or setting up a savings plan, knowing your regular payment is critical for financial planning.

✅ Personal budgeting

✅ Business loan structuring

✅ Investment goal setting

Syntax

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

Argument	Description
rate	Interest rate per period (monthly, annual, etc.).
nper	Total number of payment periods.
pv	Present value (loan amount or principal).
fv	[Optional] Future value. Default is 0.
type	[Optional] 0 = payment at end of period (default), 1 = payment at beginning.

Example

Imagine:

• You take a $20,000 car loan.

• Annual interest rate is 6%.

• Loan term is 5 years.

• Payments are monthly.

  
  

Step-by-Step:

1. Monthly Rate = 6% ÷ 12 = 0.5% = 0.005

2. Total Periods = 5 × 12 = 60 months

Excel Formula:

=PMT(0.005, 60, -20000)

Result: Your monthly payment is approximately $386.66.

(The present value is entered as negative because it's an outgoing payment.)

Unique PMT Scenarios

Scenario	Formula	What Happens
Mortgage	=PMT(0.004167, 360, -250000)	Find monthly house payment
Investment Goal	=PMT(0.006, 40, 0, 10000)	Find monthly savings needed to reach $10,000
Car Loan	=PMT(0.005, 48, -15000)	Find monthly car loan payment

Secrets to Master PMT Usage

Tip	Why It Matters
Always align rate and nper	Monthly rate ↔ monthly periods, annual rate ↔ annual periods
Use negative PV or FV	Correct cash flow direction matters
Understand payment timing	'type' argument changes payment timing (beginning vs end)
FV can be used for investments	Planning for future goals

PMT vs Other Financial Functions

Function	Purpose	Key Difference
PMT	Calculate payment amount	Payment amount for loan/investment
NPER	Calculate number of periods	Find how long it takes
RATE	Calculate interest rate	Find what rate is needed
FV	Calculate future value	Find accumulated amount

Summary Table

Feature	Details
Purpose	Find regular payment amount
When to use	Loans, mortgages, savings plans
Key Inputs	Interest rate, periods, principal or future value
Critical Insight	Cash flow direction matters (positive vs negative)

Knowing how much you need to pay or save each month is the foundation of smart financial planning.PMT eliminates guesswork and gives you a clear, actionable number — whether you're repaying debt or building wealth.

Want to budget your home loan easily? Use PMT.

Want to reach a savings goal in 3 years? Use PMT.

Want to evaluate multiple financing options? Use PMT.

Always double-check whether your loan is compounded monthly, quarterly, or annually — and match your rate and periods accordingly. A wrong assumption here can change your payment amount by hundreds of dollars!