# PMT function in Excel

> PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments.

Source: https://spreadsheetpractice.com/functions/pmt · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments.
- **Syntax:** `=PMT(rate, nper, pv, [fv], [type])`
- **Most common use:** `=PMT(B3/12,B4*12,-B2)` returns $489.15 (Monthly payment: monthly rate and 60 monthly periods)
- **Available in:** Every current version
- **Category:** [Financial functions](https://spreadsheetpractice.com/functions/category/finance)
- **Returns:** The payment per period. Negative if pv is positive, because the payment is money going out.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments. Use it for mortgage, car loan and lease payments. The two things to get right are units (monthly rate with monthly payments) and signs (money you receive is positive, money you pay is negative).

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `rate` | Required | The interest rate per period. For monthly payments on an annual rate, divide by 12. |
| `nper` | Required | The total number of payments. For a 5-year loan paid monthly, 60. |
| `pv` | Required | The amount borrowed today. Enter it as negative to get a positive payment. |
| `fv` | Optional | The balance you want left after the last payment. Usually 0, the default. |
| `type` | Optional | 0 or omitted if payments are due at the end of each period, 1 if at the beginning. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=PMT(B3/12,B4*12,-B2)` | $489.15 | Monthly payment: monthly rate and 60 monthly periods |
| D3 | `=PMT(B3/12,B4*12,B2)` | -$489.15 | Same payment as a negative: money going out |
| D4 | `=PMT(B3,B4,-B2)` | $6,015.86 | Paying once a year instead: annual rate, 5 periods |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Input | Value |  | Result |
| 2 | Loan amount | $25,000 |  | $489.15 |
| 3 | Annual rate | 6.5% |  | -$489.15 |
| 4 | Years | 5 |  | $6,015.86 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| A payment that is far too high | The annual rate was used with monthly periods. Divide the rate by 12 and multiply the years by 12. |
| A negative payment | That is the sign convention: a positive loan amount produces a negative (outgoing) payment. Enter the loan as -B2, or wrap PMT in ABS. |
| Rate typed as 6 instead of 6% | PMT treats 6 as 600% per period. Enter 6%, or 0.06. |

## Tips

- Total interest is the payment times the number of payments, minus the loan: =PMT(...)*n - loan.
- Put the rate, term and amount in their own cells so you can change them and see the payment update.

## Related functions

- [PV](https://spreadsheetpractice.com/functions/pv): PV calculates what a series of future payments, or a single future amount, is worth today at a given interest rate.
- [NPER](https://spreadsheetpractice.com/functions/nper): NPER calculates how many payment periods it takes to pay off a loan or reach a savings goal, at a fixed payment and interest rate.
- [RATE](https://spreadsheetpractice.com/functions/rate): RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.
- [FV](https://spreadsheetpractice.com/functions/fv): FV calculates what an investment will be worth in the future, given regular deposits, a starting balance and a constant rate of return.

## Questions

### How do I calculate a monthly loan payment in Excel?

Use =PMT(annual_rate/12, years*12, -loan_amount). For $25,000 at 6.5% over 5 years, that is about $489.15 a month.

### Why is my PMT result negative?

Excel treats money you pay out as negative. Enter the loan amount as a negative number to get a positive payment.

### Does PMT include taxes and insurance?

No. PMT covers principal and interest only. Add escrow items like property tax separately.

## Practice

A graded PMT exercise ("Monthly payment") is on [the PMT page](https://spreadsheetpractice.com/functions/pmt). It is checked instantly, then re-checked on changed data.

Lessons that use PMT:

- [Lesson 39: Loan payments with PMT](https://spreadsheetpractice.com/learn/pmt-loan-payments) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
