# How to calculate a loan payment in Excel

Use PMT with the monthly rate, the number of months and the amount: `=PMT(B3/12,B4*12,-B2)` returns the monthly payment.

**Excel:** `=PMT(B3/12,B4*12,-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Car loan | Value |
| Amount borrowed | 25000 |
| Annual interest rate | 0.065 |
| Term (years) | 5 |
| Monthly payment |  |

Formula in B5: `=PMT(B3/12,B4*12,-B2)`

Result: $489.15

## How it works

PMT needs the rate per period and the number of periods in the same unit as the payments. Payments are monthly, so the annual rate is divided by 12 and the years multiplied by 12. PMT treats money you receive as positive and money you pay as negative, so the loan amount goes in as `-B2` to get a positive payment.

The payment covers interest and principal; it doesn't include fees, taxes or insurance.

## Other ways

- `=-PMT(B3/12,B4*12,B2)`: Flip the sign of the result instead of the amount. Same payment.
- `=B2*(B3/12)/(1-(1+B3/12)^(-B4*12))`: The annuity formula PMT uses, written out. Useful to check a result by hand.

## Common mistakes

- Using the annual rate as is, `PMT(B3,B4*12,-B2)`, charges 6.5% a month and gives a payment of over $1,600.
- Forgetting to multiply the years by 12 gives the payment for a 5-month loan.
- A negative result isn't wrong, it's PMT's sign convention: payments go out. Put a minus in front of the amount or the function.

## Questions

**How do I calculate the total cost of the loan?**

Multiply the payment by the number of payments: `=B5*B4*12`. Subtract B2 for the total interest.

**How do I calculate weekly or biweekly payments?**

Use 52 or 26 periods a year: `=PMT(B3/26,B4*26,-B2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-a-loan-payment