PMT Function Basics

Here's a step-by-step example of how to calculate loan payments (PMT) in Microsoft Excel, complete with values and formulas.

🧾 Loan Example

You want to borrow 20,000 at an annual interest rate of 7% for 5 years, and you’ll make monthly payments.

📊 Step-by-Step Setup in Excel

Cell Description Value or Formula

A1 Loan Amount 20000

A2 Annual Interest Rate 7%

A3 Loan Term (Years) 5

A4 Payments per Year 12

🔄 Intermediate Calculations

Cell Description Formula Result

A5 Monthly Interest Rate =A2/A4 0.583%

A6 Total Payments =A3*A4 60

Calculate Monthly Payment

In A7, use the PMT function:

=PMT(A5, A6, -A1)

Explanation:

• A5 → monthly interest rate

• A6 → number of total payments

• -A1 → loan amount (negative because it’s money you owe)

✅ Result:

=PMT(0.07/12, 60, -20000)
👉 Output: 396.02

(The minus sign indicates a payment going out—you’re paying this every month.)

📌 Bonus: Show it Positive (Optional)

If you want to display the result as positive, use:

=ABS(PMT(A5, A6, -A1))

🔍 Summary Table:

Item Value

Loan Amount 20,000

Interest Rate (Annual) 7%

Term 5 Years

Monthly Payment 396.02