fx
FunctionPedia
Quick Answer

PMT Function

PMT tells you how much each payment will be on a fixed-rate loan. Give it the interest rate, number of payments, and loan amount — it returns the payment per period (monthly, yearly, etc.).

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

ParameterDescriptionRequired
rateThe interest rate per period. For a 6% annual loan with monthly payments, use 6%/12 = 0.5%.Required
nperTotal number of payment periods. For a 30-year mortgage with monthly payments, use 30×12 = 360.Required
pvPresent value — the total amount of the loan (the principal). Enter as a positive number.Required
fvFuture value — the balance after all payments. Usually 0 (loan fully paid off). Default is 0.Optional
typeWhen payments are due: 0 = end of period (default), 1 = beginning of period.Optional

Basic Example

Calculate monthly payment for a $200,000 mortgage at 6% annual rate over 30 years

=PMT(6%/12, 360, 200000)
Result$1,199.10

6%/12 converts the annual rate to monthly. 360 is 30×12 monthly periods. 200000 is the loan amount. The result is the monthly payment.

Advanced Examples

Example 1: Car loan payment calculation

Auto financing

Calculate monthly payment for a $35,000 car loan at 4.5% APR over 5 years

=PMT(4.5%/12, 60, 35000)
Result: $652.66
4.5%/12 = monthly rate. 60 = 5×12 monthly periods. 35000 = loan amount. Monthly payment is $652.66.

Example 2: Investment goal — how much to save monthly

Retirement planning

Calculate how much to save monthly to reach $500,000 in 20 years at 8% annual return

=PMT(8%/12, 240, 0, -500000)
Result: $1,057.81
pv=0 (no starting balance), fv=-500000 (goal amount, negative because you're paying into it). You need to save ~$1,058/month.

Example 3: PMT with beginning-of-period payments

Lease/annuity

Calculate lease payment when payments start at the beginning of each month

=PMT(5%/12, 36, 25000, 0, 1)
Result: $745.32
type=1 means payments at the beginning of each period (common for leases). This results in a slightly lower payment.

How PMT Works

PMT uses the standard annuity formula to calculate equal periodic payments. The formula balances three forces: (1) the principal grows over time due to interest, (2) each payment reduces the balance, and (3) after all payments, the remaining balance equals the future value (typically 0). The key trick is matching your rate and nper to the same period — if you pay monthly, both rate and nper must be monthly. The result is negative by convention (representing money flowing out), so you can wrap it in ABS() for positive display.

1
Convert annual rate to period rate
Divide the annual rate by the number of periods per year: annual_rate/12 for monthly, annual_rate/4 for quarterly.
2
Convert years to total periods
Multiply years by periods per year: years×12 for monthly, years×4 for quarterly.
3
Enter the loan amount (pv)
The present value is the total amount borrowed. Enter as a positive number.
4
Set future value and type
fv=0 for fully amortized loans. type=0 for end-of-period (default), type=1 for beginning-of-period.
5
Enter the formula
Type =PMT(rate, nper, pv, fv, type). Wrap in ABS() if you want a positive result.

Important Notes & Limitations

  • PMT assumes constant interest rate and equal payments — it doesn't handle variable-rate loans.

  • The rate and nper must use the same period unit (both monthly, both annual, etc.).

  • PMT returns a negative number by convention (cash outflow). Use ABS() to display as positive.

  • PMT doesn't include fees, taxes, or insurance — it's purely the principal+interest payment.

Common Errors & Fixes

Wrong payment amountMismatched rate and nper periods — e.g., annual rate with monthly periods

Fix: Always match periods: monthly rate = annual/12, monthly nper = years×12. Using 6% with 360 gives a tiny payment because 6% is treated as the monthly rate.

Negative resultPMT returns negative values by convention (payment is cash outflow)

Fix: Wrap in ABS(): =ABS(PMT(6%/12, 360, 200000)) to display as a positive number.

Much higher payment than expectedUsing annual rate without dividing by periods per year

Fix: A 6% annual rate for monthly payments must be entered as 6%/12 (0.5%), not 6%.

Download Practice File

Practice PMT with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the PMT function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

Loan Payment Calculator (PMT)

Calculate your monthly loan payment instantly and understand the Excel PMT function behind it. Works for mortgages, car loans, and personal loans.

PMT Function Loan Payment

Master the PMT function loan payment formula in Excel and Google Sheets: calculate monthly mortgage or car payments and split interest from principal.

Frequently Asked Questions

Why does PMT return a negative number?
By Excel's cash flow convention, payments are negative (money going out) and received amounts are positive. Use ABS() or multiply by -1 to display as positive.
How do I calculate just the interest portion of a payment?
Use IPMT for the interest portion and PPMT for the principal portion of each specific payment period.
Can PMT handle variable interest rates?
No. PMT assumes a constant rate throughout. For variable rates, you'd need a custom amortization table or iterative calculations.
What if I want to know the total cost of the loan?
Multiply PMT by nper: =ABS(PMT(6%/12,360,200000))*360 gives total payments ($431,676). Subtract the principal to find total interest paid.
Does PMT work for savings/investments?
Yes. Set pv=0 and fv=your goal amount (negative for savings): =PMT(8%/12, 240, 0, -500000) calculates how much to save monthly to reach $500K.