Excel Formula for Calculating a Loan Payment

Formulas & functions · Updated November 22, 2026

The core Excel formula for calculating a loan payment is =PMT(rate, nper, pv), where rate is the periodic interest rate, nper is the total number of payment periods, and pv is the loan amount (present value). For example, =PMT(0.05/12, 60, -20000) returns approximately $377.42, which is the monthly payment for a $20,000 loan at 5% annual interest over 5 years.

Understanding the PMT Function

The PMT function is Excel’s built-in tool for calculating loan payments based on constant payments and a constant interest rate. It is part of the financial functions category and works for both loans and investments. The function uses the following syntax:

=PMT(rate, nper, pv, [fv], [type])
  • rate – The interest rate for each period. If you have an annual rate and make monthly payments, divide the annual rate by 12 (e.g., 5% becomes 0.05/12).
  • nper – The total number of payment periods. For a 5-year monthly loan, this is 60 (5*12).
  • pv – The present value, or the total loan amount. This should be entered as a negative number to get a positive payment result, or you can use a positive number and the result will be negative.
  • [fv] – Optional future value, or cash balance you want after the last payment. For a loan, this is usually 0 (default).
  • [type] – Optional: 0 (default) for payments at the end of the period, or 1 for payments at the beginning.

The result of PMT is a negative number when you enter a positive loan amount, because it represents an outgoing payment. To display a positive number, you can either enter the loan amount as negative (e.g., =PMT(0.05/12, 60, -20000)) or wrap the function with a minus sign: =-PMT(0.05/12, 60, 20000).

Setting Up Your Spreadsheet

To make the formula reusable and easy to adjust, it’s best to put the loan variables in separate cells and reference them. Here’s a common layout:

  • A1: Annual interest rate (e.g., 5%)
  • A2: Number of years (e.g., 5)
  • A3: Loan amount (e.g., 20000)
  • A4: Payments per year (e.g., 12)

Then use the formula: =PMT(A1/A4, A2*A4, -A3).

This approach lets you change any input and the payment updates automatically. For monthly payments, you divide the annual rate by 12 and multiply the years by 12. For quarterly payments, divide by 4 and multiply by 4, and so on.

Example with Real Numbers

Let’s calculate a monthly payment for a $25,000 car loan at 4.5% annual interest over 6 years.

  1. In a new worksheet, enter these values:
    • A1: 0.045 (annual rate)
    • A2: 6 (years)
    • A3: 25000 (loan amount)
    • A4: 12 (payments per year)
  2. In cell A5, enter: =PMT(A1/A4, A2*A4, -A3)
  3. Press Enter. The result is $397.75 (rounded to two decimals).

Check: 4.5%/12 = 0.00375, nper = 72, pv = -25000. The PMT function returns 397.75. You can verify with a financial calculator or by building an amortization schedule.

Common Mistakes and How to Avoid Them

Several errors are frequent when using PMT:

  • Forgetting to convert the interest rate – Using the annual rate directly with monthly payments gives a wildly incorrect result. Always divide by the number of periods per year.
  • Wrong number of periods – Multiply years by the number of payments per year, not just using the number of years.
  • Sign confusion – If you get a negative payment, it’s because you entered a positive loan amount. Use a negative loan amount or negate the result.
  • Using commas as decimal separators – In some locales, Excel expects a period as the decimal separator. Ensure your system settings match.

Comparing Payment Scenarios

PMT is excellent for comparing different loan terms. For instance, you can create a small table with different interest rates or loan durations to see how payments change. Here’s a quick example:

  • Loan amount $20,000, 5-year term at 4%: =PMT(0.04/12, 60, -20000) returns $368.33
  • Same loan at 6%: =PMT(0.06/12, 60, -20000) returns $386.66
  • Same loan at 4% but 7-year term: =PMT(0.04/12, 84, -20000) returns $273.07

You can also use a data table (What-If Analysis) to see multiple scenarios at once, but a simple list of formulas works well for a quick comparison.

Related Functions: IPMT and PPMT

While PMT gives the total payment, you may want to know how much goes toward interest versus principal. Excel has two related functions:

  • =IPMT(rate, per, nper, pv) – Returns the interest portion for a specific period (per).
  • =PPMT(rate, per, nper, pv) – Returns the principal portion for that period.

For example, for the $25,000 loan above, the interest in the first month is =IPMT(0.045/12, 1, 72, -25000) which returns $93.75, and the principal is =PPMT(0.045/12, 1, 72, -25000) returning $304.00. Together they sum to $397.75.

These functions are useful for building an amortization schedule, where you list each period’s interest and principal.

Frequently Asked Questions

Why does my PMT formula return a negative number?
PMT returns a negative value because it represents a cash outflow (your payment). To get a positive number, enter the loan amount as a negative value in the pv argument, or put a minus sign before the function: =-PMT(...).

How do I calculate a payment for an interest-only loan?
For interest-only loans, the payment is simply the interest on the principal: =loan_amount * annual_rate / payments_per_year. For example, a $100,000 loan at 6% annual interest with monthly payments would be =100000*0.06/12 = $500. PMT is not needed for that case.

Can I use PMT for a loan with a balloon payment?
Yes, include the future value (fv) argument. For a $10,000 loan with a $2,000 balloon at the end, use =PMT(0.05/12, 36, -10000, 2000). The payment will be lower than a fully amortized loan because part of the principal is deferred.

What if my payments are at the beginning of the month?
Set the type argument to 1. For example, =PMT(0.05/12, 60, -20000, 0, 1) gives a payment of $375.28, slightly lower than the end-of-period payment of $377.42 because interest accrues for less time.

Does PMT work for weekly or biweekly payments?
Yes, just adjust the rate and nper accordingly. For biweekly (every two weeks), divide the annual rate by 26 and multiply years by 26. For weekly, divide by 52 and multiply by 52. For example, a $20,000 loan at 5% over 5 years with biweekly payments: =PMT(0.05/26, 5*26, -20000) returns $188.71 per biweekly period.

Why does my result differ from an online calculator?
Differences usually come from rounding or the timing of payments. Some calculators round the interest rate to more decimal places, or they may assume payments at the beginning of the period. Also, check whether the online tool uses a 360-day year (common in some loans) versus a 365-day year. Excel’s PMT assumes a standard monthly period with no day-count conventions.

Important Notes on Accuracy

Always verify your loan terms with your lender. The PMT function assumes a fixed interest rate and constant payment amounts. For adjustable-rate loans or loans with fees, you may need a more complex model. Also, ensure that the interest rate you enter is the nominal annual rate, not the APR (which includes fees). If you’re unsure about the exact rate or payment schedule, consult a financial professional.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.