Financial

PMT Function in Excel

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

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

Arguments

  • rate (required): Interest rate per period
  • nper (required): Total number of payment periods
  • pv (required): Present value (loan amount)
  • fv (optional): Future value (default 0)
  • type (optional): 0=end of period, 1=beginning

Examples

  • =PMT(5%/12, 360, -200000) - Monthly payment for 30-year mortgage at 5% - Result: $1,073.64
  • =PMT(6%/12, 48, -25000) - Monthly car payment for 4 years at 6% - Result: $587.13

Excel PMT function documentation

  • PMT calculates the periodic payment for a loan or annuity at a constant interest rate.
  • Syntax: =PMT(rate, nper, pv, [fv], [type]). Part of Excel's financial function set.
  • Returns negative value for cash outflow (borrower paying lender) — multiply by -1 for positive display.
  • Related: [FV](/functions/fv/), [PV](/functions/pv/), [IPMT](/functions/ipmt/), [PPMT](/functions/ppmt/).

PMT arguments and period alignment

  • rate: interest rate per period — annual rate / 12 for monthly payments.
  • nper: total number of payment periods (e.g. 30*12 for 30-year mortgage monthly).
  • pv: present value — loan principal as positive number in borrower view (Excel convention varies).
  • fv (optional): future value/balloon; default 0.
  • type (optional): 0 = end of period (default); 1 = beginning of period.

Step-by-step: monthly mortgage payment

  • Step 1 — Annual rate 6% in B1 → monthly rate =B1/12 in B2.
  • Step 2 — Term 30 years → nper =30*12 in B3.
  • Step 3 — Loan amount $300,000 in B4.
  • Step 4 — =PMT(B2, B3, B4) typically negative — display =-PMT(B2,B3,B4).
  • Step 5 — Verify against bank calculator; check rate/nper units match (monthly with monthly).

PMT vs PPMT vs IPMT vs manual formula

  • PMT — total payment each period.
  • PPMT — principal portion in specific period.
  • IPMT — interest portion in specific period.
  • Category: [Financial functions](/categories/financial/) for full loan amortization toolkit.

Worked examples to copy

  • Monthly car loan: =-PMT(5%/12, 5*12, 25000).
  • Annual payment: =PMT(8%, 10, 100000) with rate and nper both annual.
  • Lease with balloon: =PMT(rate, nper, pv, balloon_fv).
  • Beginning of period: =PMT(rate, nper, pv, 0, 1).

People also ask

  • Why PMT negative? — Excel cash flow convention; flip sign for display.
  • PMT monthly rate? — Divide annual APR by 12 for monthly nper.
  • PMT vs Excel template? — PMT for formula-driven models; templates for quick what-if.
  • PMT include taxes insurance? — No. Add escrow separately to PMT for total housing payment.

Common errors

  • #NUM! with invalid arguments
  • Rate must match period (monthly rate for monthly payments)

Use cases

  • Mortgage calculations
  • Car loans
  • Investment planning

Frequently asked questions

  • What is the PMT function in Excel? PMT calculates the periodic payment for a loan based on constant payments and a constant interest rate. The syntax is =PMT(rate, nper, pv, [fv], [type]). For example, =PMT(5%/12, 360, -200000) calculates the monthly payment for a $200,000 mortgage at 5% annual interest over 30 years (360 months), returning approximately $1,073.64.
  • Why does PMT return a negative number? PMT follows cash flow sign conventions: money you pay out is negative, money you receive is positive. If your loan amount (pv) is positive (money received), PMT returns negative (money paid out). To get a positive result, make pv negative: =PMT(rate, nper, -loan_amount). This represents the loan as money you owe.
  • How do I calculate monthly mortgage payment with PMT? For monthly payments, divide annual rate by 12 and multiply years by 12. Formula: =PMT(annual_rate/12, years*12, -loan_amount). Example: =PMT(6%/12, 30*12, -300000) calculates monthly payment for a $300,000 loan at 6% for 30 years. Result: $1,798.65/month. Always match rate period to payment period.
  • What is the difference between PMT, PPMT, and IPMT? PMT calculates total payment (principal + interest). PPMT calculates just the principal portion for a specific period. IPMT calculates just the interest portion for a specific period. PMT = PPMT + IPMT for any given period. Use PPMT and IPMT to create amortization schedules showing how payments split between principal and interest over time.
  • How do I use PMT for car loan or lease calculations? For car loans: =PMT(annual_rate/12, months, -loan_amount). Example: =PMT(7%/12, 60, -25000) for a $25,000 car at 7% for 5 years = $495.03/month. For leases with residual value, add fv parameter: =PMT(rate/12, months, -price, residual_value). The residual is what you'd pay to buy the car at lease end.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use PMT

  • Mortgage calculations — common Financial scenario for PMT.
  • Car loans — common Financial scenario for PMT.
  • Investment planning — common Financial scenario for PMT.

PMT in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • PMT syntax: =PMT(rate, nper, pv, [fv], [type])
  • rate (required): Interest rate per period
  • nper (required): Total number of payment periods
  • pv (required): Present value (loan amount)
  • fv (optional): Future value (default 0)
  • Confirm PMT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm PMT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • PPMT (/functions/ppmt/): Returns the principal payment for a given period of an investment.
  • IPMT (/functions/ipmt/): Returns the interest payment for a given period of an investment.
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now.
  • FV (/functions/fv/): Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
  • NPER (/functions/nper/): Returns the number of periods for an investment based on periodic payments and a constant interest rate.

Errors to watch for

  • #NUM! with invalid arguments — review causes on linked error pages in the directory.
  • Rate must match period (monthly rate for monthly payments) — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter PMT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

PMT worked examples to copy

  • =PMT(5%/12, 360, -200000) — Monthly payment for 30-year mortgage at 5%. Expected result: $1,073.64.
  • =PMT(6%/12, 48, -25000) — Monthly car payment for 4 years at 6%. Expected result: $587.13.

PMT reference summary for crawlers and offline review

  • PMT belongs to the Financial category in Excel. Calculates the payment for a loan based on constant payments and a constant interest rate.
  • Full syntax: =PMT(rate, nper, pv, [fv], [type]). Open /functions/pmt/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! with invalid arguments; Rate must match period (monthly rate for monthly payments)
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.