Excel PMT Function — Complete Financial Guide with Practical Examples

Introduction

The Excel PMT function is the go-to tool for calculating periodic loan payments when the interest rate is constant and the payment schedule is fixed. Whether you’re evaluating a mortgage, car loan, or business financing, PMT provides fast, accurate results that are essential for financial planning. In this guide you’ll find the PMT syntax, clear step-by-step examples (monthly and yearly), advanced scenarios, troubleshooting tips, and frequently asked questions — everything you need to use PMT like a pro.

H2: What is the PMT function in Excel?

The PMT function calculates the payment for a loan based on constant interest and a fixed number of periods.

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

rate — interest rate per period.

nper — total number of payment periods.

pv — present value (the principal amount or loan).

fv (optional) — future value (cash balance desired after last payment). Default = 0.

type (optional) — when payments are due: 0 = end of period (default), 1 = beginning of period.

Note: Excel returns payments as negative values because they represent cash outflows. Use a leading minus sign to show a positive payment: =-PMT(…).

External link

• description: Official Microsoft documentation for the PMT function
• link: https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441

H2: Key concepts before you start

• Match units: If you use monthly payments, make sure to convert the annual rate to monthly (annual_rate/12) and convert years to months (years*12).
• Sign convention: pv (loan) is usually positive; PMT will be negative. Use -PMT(...) to display positive payment values.
• Type argument: Use type = 1 if payments are at the beginning of the period (e.g., some leases).

H2: Basic examples — step by step

H3: Example 1 — Simple monthly loan payment

Loan: $10,000 — Annual rate: 6% — Term: 5 years (60 months)

1. Convert rate: 6% / 12 = 0.5% = 0.005
2. Convert term: 5 * 12 = 60 months
3. Formula: =PMT(6%/12, 60, 10000)

Result: -193.33 → Monthly payment = $193.33

H3: Example 2 — Yearly payments
Loan: $5,000 — Annual rate: 10% — Term: 2 years

Formula: =PMT(10%, 2, 5000)

Result: payment per year.

H3: Show payment as positive

To show payment as positive: =-PMT(6%/12, 60, 10000)

H2: Advanced examples & real-life scenarios

H3: Mortgage calculation (30 years)

Mortgage: $200,000 — Annual rate: 5% — Term: 30 years (360 months) =PMT(5%/12, 360, 200000)

Result (approx): -1073.64 → Monthly payment ~$1,073.64

H3: Car loan with upfront payment timing

Car loan: $25,000 — Annual rate: 7% — Term: 5 years (payments at beginning of period) =PMT(7%/12, 60, 25000, 0, 1)

H3: Loan with a residual/future value

You want a future balance of $10,000 after loan term: =PMT(8%/12, 120, 50000, 10000)

This calculates a lower monthly payment because you plan to leave a balance.

H2: PMT in practical spreadsheets (tables and scenario analysis)

• Monthly amortization table: Combine PMT with IPMT and PPMT to build an amortization schedule (interest vs principal per payment).
• Scenario analysis: Use Data Table or Goal Seek to see how changing rates or terms affect PMT.
• Rounding: Wrap ROUND to present two decimals: =ROUND(-PMT(...), 2).

H2: Common errors and how to fix them

H3: Error — wrong rate unit

Using annual rate directly for monthly payments:
Wrong: =PMT(6%, 60, 10000)
Fix: divide rate by 12: =PMT(6%/12, 60, 10000)

H3: Error — sign confusion

If PMT returns negative values and you want positive:
Use =-PMT(...).

H3: Error — forgetting type

Payments at beginning vs end change the payment slightly. Add the type argument when needed.

H2: PMT vs other financial functions

Short comparison to choose correct function:

• PMT: Payment per period for a loan.
• FV: Future value of an investment given periodic contributions.
• NPV: Net present value of a series of cash flows.
• IPMT / PPMT: Interest and principal portions of a payment.

(Suggested internal link: link this section to future posts about IPMT, FV, and NPV.)

« Besides using formulas like PMT for loan calculations, you can also simplify your financial tracking with ready-made tools. Download free Excel invoice templates to simplify your financial management

2: Frequently Asked Questions (FAQ)

Q1 — Why is PMT negative?
PMT treats payments as cash outflows. Use -PMT() to display positive numbers.

Q2 — Can PMT handle variable interest rates?
No. PMT assumes a constant rate. For variable rates use period-by-period modeling and IPMT per period or build a custom schedule.

Q3 — How to show principal vs interest?
Use IPMT(rate, per, nper, pv) for interest in a given period and PPMT(...) for principal.

Q4 — Can I calculate bi-weekly payments?
Yes — convert the annual rate and periods to bi-weekly terms (e.g., annual_rate/26 and years*26).

H2: Conclusion & next steps

The Excel PMT function is foundational for personal and business finance: quick, reliable, and widely applicable. Use the examples above to test real loan scenarios and build amortization tables for clarity. If you want, I can prepare a downloadable Excel template (with tables and charts) you can publish with the article to increase user engagement and dwell time.

Call to action: Try the PMT formula with your loan numbers today — and if you want, send me your loan details (amount, rate, term) and I’ll prepare the amortization table you can publish on your site.