The Excel PMT function calculates periodic loan payments — the most useful financial function for mortgages, car loans, student loans, and savings calculations. Type =PMT(rate, periods, present_value) and Excel returns the constant payment needed to pay off the loan at the given interest rate over the given number of periods.
What PMT does. Solves the time-value-of-money equation: how much must you pay each period to repay a present amount over N periods at rate R? The answer is constant for the life of the loan (assuming fixed rate). Used by banks, financial planners, businesses, and anyone calculating regular payments.
When to use PMT. Calculating mortgage payments. Estimating car loan payments. Personal loan and student loan payments. Investment goal calculations (how much to save monthly to reach a target). Lease payment calculations. Any periodic payment with fixed interest rate.
Sign convention quirk. PMT returns a negative value by default. Why? Excel treats money received as positive and money paid out as negative. So if you borrow $10,000 (positive cash flow to you), your payment is negative (cash out). To get a positive payment value for display, prefix with negative sign: =-PMT(...).
Foundation of financial calculations. PMT is the gateway to a full suite of related Excel financial functions: PV (present value), FV (future value), RATE (interest rate), NPER (number of periods). Master PMT and the others follow naturally.
This guide covers PMT syntax, examples for mortgages and loans, sign conventions, advanced uses, related functions, and common mistakes. It's for anyone using Excel for financial calculations.
Basic PMT examples. Start with the simplest case.
Example 1: $200,000 mortgage at 6.5%, 30 years. Setup: loan amount $200,000, rate 6.5% annually, term 30 years. PMT formula: =PMT(0.065/12, 30*12, 200000). Returns: -$1,264.14. This is the monthly payment (negative because it's cash out).
For positive display. =-PMT(0.065/12, 30*12, 200000) = $1,264.14. Same calculation, prefixed with negative for readable result.
Example 2: $25,000 car loan at 7%, 5 years. =-PMT(0.07/12, 5*12, 25000) = $495.03 monthly.
Example 3: $50,000 student loan at 5.5%, 10 years. =-PMT(0.055/12, 10*12, 50000) = $542.61 monthly.
Step by step. Step 1: Identify variables. Loan amount, annual rate, total term. Step 2: Convert annual to periodic rate. Divide annual rate by 12 (for monthly) or 4 (quarterly). Step 3: Calculate total periods. Years × payments per year. Step 4: Apply PMT formula. =PMT(periodic_rate, total_periods, loan_amount). Step 5: Negate for positive display. =-PMT(...).
What you're calculating. PMT assumes: constant payments throughout the term. Each payment is part principal + part interest. Early payments are mostly interest; later payments are mostly principal. The total of payments = principal + total interest.
Total interest formula. =total_payments - loan_amount. For our $200K mortgage: $1,264.14 × 360 = $455,089. Loan was $200,000. Total interest = $255,089. The borrower pays back more than double the original loan over 30 years.
Annual rate ÷ payments per year (e.g., 6.5%/12 for monthly)
Years × payments per year (e.g., 30 × 12 = 360 monthly)
Current loan amount (positive for what you owe)
Optional. 0 for fully paid off (default)
0 = end of period (typical). 1 = beginning of period
Negative = payment out. =-PMT() for positive display
Common mortgage and loan PMT scenarios.
30-year fixed mortgage. Standard residential mortgage. =-PMT(rate/12, 360, principal). For $300K at 7%: =-PMT(0.07/12, 360, 300000) = $1,995.91 monthly.
15-year fixed mortgage. Faster payoff, lower total interest. =-PMT(rate/12, 180, principal). For $300K at 6%: =-PMT(0.06/12, 180, 300000) = $2,531.57 monthly. Higher payment but $230,000 less in interest vs 30-year at 7%.
Adjustable rate mortgage (ARM). Initially fixed, then variable. PMT calculates initial fixed-period payment. After adjustment, recalculate with new rate. =-PMT(new_rate/12, remaining_periods, current_balance).
Refinance calculation. Compare current vs new mortgage. Current: =-PMT(current_rate/12, remaining_periods, current_balance). New: =-PMT(new_rate/12, new_term*12, new_principal). Calculate savings × remaining months.
Car loan. Typically 3-7 years. =-PMT(rate/12, years*12, principal). For $35K at 5.5% over 5 years: =-PMT(0.055/12, 60, 35000) = $668.04.
Student loan. Federal: 10-25 years typical. Private: 5-20 years. =-PMT(rate/12, years*12, principal). For $50K at 5.5% over 10 years: =-PMT(0.055/12, 120, 50000) = $542.61.
Personal loan. 1-7 years typical. Higher rates. =-PMT(rate/12, years*12, principal). For $10K at 11% over 3 years: =-PMT(0.11/12, 36, 10000) = $327.07.
Business loan. Term loan or SBA loan. 3-25 years. Similar formula. =-PMT(rate/12, years*12, principal). For $250K at 8% over 10 years: =-PMT(0.08/12, 120, 250000) = $3,033.18.
Construction loan. Often interest-only initially, then conventional. PMT for interest-only periods is simply loan × rate/payments per year. PMT for principal+interest period uses standard PMT formula.
=-PMT(rate/12, 360, principal). Standard residential mortgage. $300K at 7%: $1,995.91/month. Total payments over 30 years: $718,529. Total interest: $418,529 (more than original loan).
=-PMT(rate/12, 180, principal). Faster payoff. $300K at 6%: $2,531.57/month. Higher monthly but $232K saved vs 30-year. Worth considering if budget allows.
=-PMT(rate/12, months, principal). $35K at 5.5% over 5 years: $668.04/month. Total interest: $5,082. Shorter terms = less interest but higher monthly payment.
=-PMT(rate/12, months, principal). $50K at 5.5% over 10 years: $542.61/month. Federal loans typically 10-25 years. Income-driven plans extend term and may reduce payment.
=-PMT(rate/12, months, principal). $10K at 11% over 3 years: $327.07/month. Personal loan rates higher than secured loans. Shorter terms typical.
=-PMT(rate/12, months, principal). $250K at 8% over 10 years: $3,033.18/month. SBA loans often 7%-10%, 7-25 year terms. Equipment loans typically shorter.
PMT for savings and investment goals. Reverse the logic.
Savings goal: how much to save monthly. To reach $50,000 in 10 years at 7% annual return: =-PMT(0.07/12, 120, 0, 50000) = $288.27 monthly. Negative because money out (saving). The fv=50000 means you want to end with $50K.
Retirement savings calculation. To have $1 million in 30 years at 7% return: =-PMT(0.07/12, 360, 0, 1000000) = $820.07 monthly. Modest monthly amount compounds to seven figures.
College savings. 18 years to save $200,000 at 6% return: =-PMT(0.06/12, 216, 0, 200000) = $516.74 monthly.
Emergency fund. 3 months expenses ($15,000) in 12 months at 4% savings rate: =-PMT(0.04/12, 12, 0, 15000) = $1,228.74 monthly. Short timeline means most through your savings, minimal compound growth.
What this assumes. Constant rate of return. Constant monthly savings amount. No tax considerations. Real returns vary; this is mathematical baseline.
Adjusting for inflation. To save $50,000 in today's dollars in 10 years at 5% inflation: actual target = $50,000 × 1.05^10 = $81,445. Then calculate savings rate. PMT doesn't auto-adjust; do manually.
Real vs nominal returns. Nominal return 7% with 3% inflation = ~4% real return. Use real return for inflation-adjusted goals. PMT works with whatever rate you provide.
Combining with already-saved. If you have $10,000 saved, formula adjusts. =-PMT(0.07/12, 120, -10000, 50000) — the -10000 is current savings (negative because you 'gave' that to your future self).
Tax considerations. PMT doesn't account for tax. Roth vs traditional retirement: returns differ in tax treatment. PMT calculates pre-tax savings. After-tax planning requires additional calculation.
Advanced PMT scenarios.
Variable rate scenarios. PMT assumes constant rate. For variable-rate loans, recalculate at each rate change. Track interest paid (IPMT) and principal (PPMT) by period to understand balance changes.
Balloon loans. Periodic payments plus large final payment. PMT formula handles balloon when fv is non-zero. =-PMT(rate/12, periods, principal, balloon_amount). The balloon is the unpaid principal at end of regular payments.
Interest-only mortgages. No principal paid during interest-only period. Payment = principal × rate / 12. PMT formula: =-PMT(rate/12, total_periods, principal, principal). The fv=principal because you owe the full principal at end of interest-only period.
Bi-weekly mortgages. 26 payments per year (every 2 weeks). =-PMT(rate/26, years*26, principal). Slightly more payments per year reduces total interest. Equivalent to one extra monthly payment per year.
Refinance breakeven. Compare costs. Current monthly minus new monthly = monthly savings. Refinance fees ÷ monthly savings = months to breakeven. Worth refinancing if breakeven before you plan to move/refinance again.
Loan payoff with extra payments. =NPER(rate/12, -increased_payment, principal) returns months until payoff with extra payment. Compare to original NPER for time savings.
Sinking fund. Save monthly toward future expense (replacement, retirement). =-PMT(rate/12, months, 0, target_amount). Same as savings goal calculation.
Annuity calculations. Receive monthly income from invested amount. =-PMT(rate/12, months, present_value, 0). The PV is your investment; monthly payment is income; 0 means fully depleted at end.
Comparing loan offers. Lender A: $250K at 6.5%, 30 years, $2,000 fees. Lender B: $250K at 6.25%, 30 years, $5,000 fees. Compare total cost over expected term.
fv = balloon amount. Periodic payments + final lump.
fv = principal. Payments cover interest only during fixed period.
rate/26, periods*26. ~1 extra month payment per year.
Monthly savings × months to recoup fees.
Use NPER to find new payoff date with higher payment.
PV as investment. Monthly income calculated.
Related Excel financial functions.
PV (Present Value). Calculate how much something is worth today given future payments. =PV(rate, nper, pmt, [fv]). Useful for valuing future income streams, comparing loan offers, retirement planning.
FV (Future Value). Calculate future value of present amount or savings stream. =FV(rate, nper, pmt, [pv]). Useful for retirement projections, savings growth, investment forecasting.
RATE. Calculate interest rate from other variables. =RATE(nper, pmt, pv, [fv]). Useful when you know payment, principal, and term but not rate. Can be slow due to iterative calculation.
NPER. Calculate number of periods to pay off or reach goal. =NPER(rate, pmt, pv, [fv]). Useful for 'how long until I'm debt-free' or 'how long to reach savings goal.'
IPMT and PPMT. Break down each payment into interest (IPMT) and principal (PPMT) components. =IPMT(rate, period, nper, pv) — interest portion of specific payment. =PPMT(rate, period, nper, pv) — principal portion. Useful for understanding amortization.
CUMIPMT and CUMPRINC. Cumulative interest and principal between specific periods. Useful for analyzing first vs last years of a mortgage. =CUMIPMT(rate, nper, pv, start_period, end_period, type). Useful for tax calculations (mortgage interest deductible).
NPV (Net Present Value). Discount future cash flows to present. =NPV(rate, cash_flows). Useful for investment analysis, comparing projects, valuing businesses.
IRR (Internal Rate of Return). Discount rate that makes NPV = 0. =IRR(cash_flows). Useful for investment evaluation, comparing returns of different opportunities.
XNPV and XIRR. Same as NPV/IRR but handles irregular dates. Useful for real-world investments with non-uniform cash flow timing.
Practical use. Build amortization schedule using PMT, IPMT, PPMT. Show how each payment breaks down. Visualize how loan balance decreases over time. Strong understanding of how loans actually work.
=PV(rate, nper, pmt, [fv]). What's something worth today? If you'll receive $500/month for 10 years at 6% discount, =PV(0.06/12, 120, -500) = $45,043. Useful for valuing income streams, retirement planning.
=FV(rate, nper, pmt, [pv]). What will current amount grow to? $10,000 invested at 7% for 30 years: =FV(0.07, 30, 0, -10000) = $76,123. Adding monthly contributions: include in pmt.
=RATE(nper, pmt, pv, [fv]). What's the implied interest rate? You're paying $500/month on $20K loan for 5 years: =RATE(60, -500, 20000)*12 = 18% annually. Identifies hidden costs.
=NPER(rate, pmt, pv, [fv]). How long until paid off or goal reached? $10K credit card at 18% paying $400/month: =NPER(0.18/12, -400, 10000) = 32 months until paid off.
Per-payment interest (IPMT) and principal (PPMT). =IPMT(rate, period, nper, pv) for interest portion of payment #1, #2, etc. Useful for amortization schedules and tax records.
NPV: =NPV(rate, cash_flows) for present value of varied cash flows. IRR: =IRR(cash_flows) for implied return. Used in business and investment analysis.
Building an amortization schedule with PMT and friends.
Why an amortization schedule. Shows month-by-month: interest paid, principal paid, remaining balance. Reveals how loans actually work. Strongly recommended for understanding any loan.
Setup. Headers: Payment Number, Payment, Principal, Interest, Remaining Balance. Inputs: loan amount, rate, term. PMT formula calculates payment.
Payment 1 row. Payment = =-PMT(rate/12, total_periods, principal). Constant for entire schedule. Interest = principal × rate/12. Principal = payment - interest. Remaining balance = principal - principal_paid.
Payment 2 and beyond. Payment = same constant. Interest = previous_balance × rate/12. Principal = payment - interest. Remaining balance = previous_balance - principal_paid.
Copy down. Drag formulas down for full schedule (e.g., 360 rows for 30-year monthly mortgage).
What you'll see. Early payments: mostly interest (e.g., $1,083 interest + $181 principal on $200K mortgage payment 1). Mid-loan: more balanced. Late payments: mostly principal (e.g., $7 interest + $1,257 principal on last payment).
Total interest paid. Sum of interest column. For $200K at 6.5% over 30 years: $255,000+ total interest. Eye-opening.
Adding extra payment column. Show effect of additional payments on payoff. Add extra payment field, recalculate principal/balance row by row. See how extra payments accelerate payoff.
Visualization. Chart of principal vs interest by payment number. Bar chart of remaining balance over time. Pie chart of total paid (principal vs interest).
Practical use. Understand your loan deeply. Identify when extra payments help most. Compare loan options. Make better borrowing decisions.
Loan amount, annual rate, term. Single-cell inputs.
=-PMT(rate/12, total_periods, principal). Constant.
Previous balance × rate/12. Changes each month.
Payment - interest. Increases over time.
Previous balance - principal paid. Decreases each period.
Chart total paid, balance over time, principal vs interest.
Common PMT mistakes and how to avoid them.
Mistake 1: Using annual rate for monthly payments. =PMT(0.065, 30*12, 200000) — wrong! Result is huge because rate not converted. Fix: =PMT(0.065/12, 30*12, 200000). Annual rate must be divided by 12 for monthly payments.
Mistake 2: Wrong number of periods. Used years instead of months. =PMT(0.065/12, 30, 200000) — wrong! Result: $4,265 instead of $1,264. Fix: =PMT(0.065/12, 30*12, 200000). Use total payment periods (years × payments per year).
Mistake 3: Forgetting sign convention. =PMT(0.065/12, 360, 200000) returns -1264.14. Display issue: shows as negative. Fix: =-PMT(...) for positive display. Underlying math is correct either way.
Mistake 4: Mixing periods. Quarterly compounding with monthly payments? Get consistent periods. If rate is annual, must convert to match payment frequency. =PMT(annual_rate/payments_per_year, total_periods, principal).
Mistake 5: Wrong PV sign. Loan amount should be positive (you receive cash, owe future payments). =PMT(rate, nper, principal) = correct. =PMT(rate, nper, -principal) would give payment in opposite direction.
Mistake 6: Including upfront fees in principal. PMT calculates payment based on full principal. If you have origination fees added to loan, those increase principal and monthly payment. Some calculations don't account for fees.
Mistake 7: Ignoring property taxes/insurance. For mortgages, PMT calculates principal+interest. Add property tax, insurance, PMI for full monthly housing cost (PITI).
Mistake 8: Confusing nominal and APR. Banks may quote nominal rate (compounded annually) but charge effective APR (compounded monthly). Slight differences in calculations. Verify which rate the lender quoted.
Mistake 9: Forgetting compound interest. PMT assumes compound interest, not simple. Some loans (especially auto) use simple interest. Calculation method differs.
Mistake 10: Treating PMT as set-in-stone. Variable-rate loans, ARMs, ballooning ARMs all change. PMT calculates given current variables. Actual payments may change over loan life.
Real-world applications of PMT.
Personal financial planning. Build retirement projection: =FV(7%/12, 30*12, -500, 0) shows $1M+ from $500/month over 30 years at 7%. Build budget for expected expenses (mortgage, car loan). Plan for major purchases.
Mortgage shopping. Compare lender offers. Calculate total interest paid over loan life. Decide between 15-year and 30-year. Decide between fixed and adjustable rate. Quantify trade-offs.
Refinance decision. Current monthly: $1,896. New offer with $5,000 fees: $1,650 monthly. Savings: $246/month. Breakeven: 20 months. Worth it if you'll stay 3+ years.
Car affordability. Want $35K car? Monthly payment at 5% over 5 years: $660. Add insurance, gas, maintenance ($200-400/month). Total transportation cost: $860-1,060/month. Decide if affordable for your income.
Investment analysis. Comparing real estate vs stock investment. Rental property cash flow analysis. ROI calculations for renovations. Lease-buy decisions for equipment.
Business financing. Loan vs lease analysis. SBA loan terms. Equipment financing decisions. Cash flow projections with debt service.
Education funding. College cost vs student loan implications. Compare federal vs private loan options. Income-based repayment vs standard.
Debt consolidation. Combine multiple high-interest debts into one lower-rate loan. PMT calculates new monthly payment. Compare to current total monthly payments.
Auto leasing vs buying. Lease payment (no principal payoff) vs loan payment (builds equity). Total cost comparison over typical ownership period.
Insurance planning. Disability insurance: how much income you need to replace. Long-term care insurance: monthly premium vs potential payout.
Compare lender offers. Calculate total interest. Decide 15 vs 30 year. Plan for refinancing breakeven. PMT is essential mortgage tool.
Calculate monthly payment. Compare term lengths. Decide trade-in vs sell. Make car affordable decisions. PMT for 'can I afford this car?'
Retirement projections. Savings goal planning. Emergency fund building. Debt payoff strategy. PMT-based personal finance dashboard.
Loan vs lease. Equipment financing. SBA loan calculations. Cash flow with debt service. PMT for business financial planning.
Student loan analysis. Compare federal vs private. Income-based repayment. Plan college costs. PMT for education financing decisions.
Real estate cash flow analysis. Bond pricing. Investment property ROI. PMT for evaluating investment opportunities.
Tips for using PMT effectively.
Tip 1: Always verify your math. Spot-check PMT result against a financial calculator (Google 'mortgage calculator' for sanity check). Different sources should agree closely.
Tip 2: Use named ranges. Name cells 'rate', 'principal', 'years'. Then =PMT(rate/12, years*12, principal). Easier to read and update.
Tip 3: Build templates. Save mortgage calculator, car loan calculator, savings goal templates. Reuse for future calculations.
Tip 4: Combine with charts. Visualize amortization. Show how principal vs interest changes over time. Charts communicate better than numbers.
Tip 5: Sensitivity analysis. What if rate changes by 0.5%? What if you take a 15-year instead of 30-year? Build a what-if scenarios sheet showing payments at various rates and terms.
Tip 6: Compare to other tools. PMT in Excel. Bank online calculators. NerdWallet, Bankrate. Verify your results across sources.
Tip 7: Account for total cost. Mortgage isn't just principal + interest. Add taxes, insurance, PMI for full housing cost. Car loan isn't just monthly payment. Add insurance, fuel, maintenance.
Tip 8: Document assumptions. What rate did you use? What term? What did you exclude? Notes prevent confusion 6 months later.
Tip 9: Plan for changes. Variable rates change. Income changes. Life changes. PMT calculates point-in-time scenarios. Build flexibility into financial plans.
Tip 10: Learn related functions. Master PMT, then learn PV, FV, RATE, NPER, IPMT, PPMT. Each unlocks new analyses.
Final thoughts. The Excel PMT function is one of the most useful financial tools you can master. Whether buying a home, financing a car, planning retirement, or analyzing investments, PMT gives you the answers you need quickly.
Start with the basics. =-PMT(rate/12, years*12, principal) for any loan. Understand sign conventions (negative payment is correct). Master the related functions (PV, FV, RATE, NPER, IPMT, PPMT) as you progress.
Apply it to your life. Don't just learn the formula — use it. Calculate your mortgage payment. Plan your retirement savings. Analyze whether to refinance. Compare car loan options. The function is most valuable when applied to real decisions.
Build templates. Mortgage calculator, car loan calculator, retirement planner. Reusable spreadsheets save time and increase accuracy. Add charts, what-if scenarios, sensitivity analyses.
Avoid common mistakes. Convert annual rates to monthly. Use total periods, not just years. Understand the sign convention. Account for total cost (not just principal + interest).
The PMT function democratizes financial calculations. What used to require a banker, financial advisor, or specialized calculator now sits in any spreadsheet. Master it, and you'll make better financial decisions throughout your life. Whether borrowing or saving, planning or comparing, PMT provides the mathematical foundation for sound personal finance.