The PMT function calculates the constant periodic payment required to repay a loan or reach a savings goal, based on a fixed interest rate and number of periods. It returns the payment amount as a negative number by default, representing money leaving your account.
=PMT(rate, nper, pv, [fv], [type])What does PMT do?
The PMT function is one of Excel's financial functions, and it answers a very common question: what will my regular payment be? Whether you are paying off a mortgage, a car loan, or a business equipment loan, PMT takes the interest rate, the number of periods, and the loan amount, and returns the payment you need to make each period to pay it off completely.
PMT is useful because it lets you compare scenarios quickly. You can change the interest rate or the loan term and instantly see how the monthly payment changes. This helps with budgeting, loan shopping, and financial planning without needing a separate calculator or spreadsheet model.
For example, imagine you are considering a £20,000 car loan at 5% annual interest over 5 years. Instead of manually working through the formula, you can set up a small table with the rate, periods, and loan amount, and PMT will give you the monthly payment. You can then adjust the numbers to see what happens if you put down a larger deposit or choose a shorter term.
When to use PMT
- Calculating monthly payments for a mortgage or home loan.
- Working out car loan or personal loan repayments.
- Planning business loan repayments for equipment or expansion.
- Determining how much to save each month to reach a savings goal (using PMT with future value).
- Comparing different loan offers by varying interest rates and terms.
- Creating an amortisation schedule to see how payments split between principal and interest.
PMT syntax
=PMT(rate, nper, pv, [fv], [type])| Argument | Required? | What to enter |
|---|---|---|
rate | Required | The interest rate per period. For a monthly payment on an annual rate, divide the annual rate by 12. Example: 5% annual becomes 0.05/12. |
nper | Required | The total number of payment periods. For a 5-year monthly loan, use 5*12 = 60. Example: 60. |
pv | Required | The present value, or the loan amount. This is the amount you are borrowing. Example: 20000. |
fv | Optional | The future value, or the cash balance you want after the last payment. For a loan, this is usually 0 (the default). Example: 0. |
type | Optional | When payments are due: 0 for end of period (default), 1 for beginning of period. Example: 0. |
What it returns: The PMT function returns the constant payment amount per period, including principal and interest, as a number. By default, the result is negative because it represents money paid out. You can use a minus sign before PMT or wrap it in ABS to get a positive number.
PMT examples
1 Simple monthly loan payment
This example calculates the monthly payment for a £10,000 loan at 6% annual interest over 3 years. The rate per period is 6%/12 = 0.5%, and the number of periods is 3*12 = 36. The formula =PMT(B2/12, B3*12, B1) uses the loan amount from B1, the annual rate from B2, and the years from B3. It returns approximately -304.22, meaning you pay about £304.22 per month. The negative sign indicates cash outflow.
| A | B | C | |
|---|---|---|---|
| 1 | Loan Amount | 10000 | |
| 2 | Annual Interest Rate | 0.06 | |
| 3 | Years | 3 | |
| 4 | Monthly Payment | -304.2193745 | |
| 5 |
2 Car loan with down payment
Here we calculate the monthly payment for a car loan after a down payment. The car price is £25,000, the down payment is £5,000, so the loan amount is £20,000. The annual interest rate is 4.5% and the loan term is 5 years. The formula =PMT(B2/12, B3*12, B1-B4) subtracts the down payment from the price to get the present value. It returns approximately -372.86, so the monthly payment is about £372.86.
| A | B | C | |
|---|---|---|---|
| 1 | Car Price | 25000 | |
| 2 | Annual Interest Rate | 0.045 | |
| 3 | Loan Term (Years) | 5 | |
| 4 | Down Payment | 5000 | |
| 5 | Monthly Payment | -372.8603848 | |
| 6 |
Common PMT errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | The rate or nper is invalid, such as a negative number or zero when it should not be. | Ensure rate and nper are positive numbers. If rate is 0, use a different approach or set rate to a very small number. |
| #VALUE! | One or more arguments are text instead of numbers. | Check that all arguments are numeric. Remove any currency symbols or commas from numbers, or use VALUE to convert text to numbers. |
| Wrong sign of result | PMT returns a negative number for payments (cash outflow). | If you want a positive number, put a minus sign before PMT or use ABS(PMT(...)). |
| #DIV/0! | nper is zero, causing division by zero. | Ensure nper is greater than zero. |
Tips for using PMT
- Always use consistent time units: if payments are monthly, divide the annual rate by 12 and multiply years by 12.
- Use a minus sign before PMT or ABS to display payments as positive numbers.
- For loans, set fv to 0 (or omit it). For savings goals, set pv to 0 and fv to the goal amount.
- Use the type argument (0 or 1) to specify whether payments are due at the end or beginning of each period.
- Label your input cells clearly and use cell references in the formula for easy scenario testing.
- Remember that PMT calculates a constant payment; it does not account for fees or variable rates.
Frequently asked questions
Why does PMT return a negative number?
PMT returns a negative number because it represents money leaving your account (a payment). To get a positive number, use a minus sign before the function or wrap it in ABS.
How do I calculate a monthly payment from an annual interest rate?
Divide the annual rate by 12 for the rate argument, and multiply the number of years by 12 for the nper argument. For example, for 5% annual over 10 years, use rate=5%/12 and nper=10*12.
Can PMT calculate savings goals?
Yes. Set pv to 0 and fv to your savings goal. The function will return the regular payment needed to reach that goal, assuming a constant interest rate.
What is the difference between type 0 and type 1 in PMT?
Type 0 (default) means payments are due at the end of each period. Type 1 means payments are due at the beginning. Beginning payments result in slightly lower payments because interest accrues for less time.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. PMT reference