Car loan
| A | B | C | |
|---|---|---|---|
| 1 | Amount | Annual rate | Months |
| 2 | 20000 | 0.06 | 60 |
Formula: E2: =ROUND(-PMT(B2/12,C2,A2),2)
Result: 386.66
Monthly rate 0.5% over 60 payments; the leading minus turns the payment positive and ROUND shows cents.
Financial
PMT
Fixed loan payment (annuity).
Syntax
PMT(rate, nper, pv, [fv], [type])Example: =PMT(24%/12,24,-2000000)
Available in: All versions
PMT calculates the fixed periodic payment on a loan with a constant interest rate: =PMT(rate, nper, pv, [fv], [type]). Divide an annual rate by 12 and use the number of months to get a monthly payment. The result is negative because it is money you pay out.
When to use it
- You want the monthly payment on a car loan, mortgage or personal loan.
- You compare loan terms (15 versus 30 years) or rates.
- You find the regular deposit needed to reach a savings goal.
- You build an amortisation schedule.
Arguments explained
- rate. The interest rate per period. For a monthly payment on a yearly rate, divide by 12, for example 6%/12.
- nper. The total number of payments. A 5-year loan paid monthly has 60 periods, so multiply years by 12.
- pv. The present value, which is the loan amount. Enter the amount as a positive number.
- fv. Optional. The balance you want after the last payment. The default is 0, meaning a fully repaid loan.
- type. Optional. 0 or omitted means payments at the end of each period, 1 means at the start. Excel returns the payment as a negative number because it is money you pay out.
Worked examples
Mortgage
| A | B | C | |
|---|---|---|---|
| 1 | Amount | Annual rate | Years |
| 2 | 300000 | 0.065 | 30 |
Formula: E2: =ROUND(-PMT(B2/12,C2*12,A2),2)
Result: 1896.2
Years are converted to 360 monthly payments.
Savings plan
| A | B | C | |
|---|---|---|---|
| 1 | Goal | Annual rate | Years |
| 2 | 50000 | 0.05 | 10 |
Formula: E2: =ROUND(PMT(B2/12,C2*12,0,-A2),2)
Result: 321.99
With pv 0 and fv set to the goal (entered as negative), PMT returns the monthly deposit needed.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =PMT(6%/12,24,-20000)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Cash flow | Result | |
| 2 | 0 | -10000 | 886.412205055 | |
| 3 | 1 | 3000 | ||
| 4 | 2 | 4000 | ||
| 5 | 3 | 4500 | ||
| 6 | 4 | 2500 |
Result in D2: 886.412205055
Common errors and how to fix them
- Mismatched units. Rate and nper must use the same period. Monthly payments need rate/12 and years*12.
- Negative result. Cash you pay out is negative by convention. Put a minus in front of PMT or enter the loan amount as negative.
- #NUM!. nper is zero or invalid.
- Result does not match the bank. Fees, insurance and taxes are not included, and banks may round or compound differently.
Related functions
Frequently asked questions
- Why is PMT negative?
- Excel treats payments as cash outflows. Use -PMT(...) to display a positive number.
- How do I calculate the total interest paid?
- Multiply the payment by the number of periods and subtract the loan amount.
- What does the type argument do?
- 0 (default) means payments at the end of each period, 1 means at the beginning.
- Does PMT work for savings goals?
- Yes. Set pv to 0 and fv to the goal.