What it does
The function needs three things: the interest rate per period, the total number of periods and the present value (the loan amount). If you pay monthly, the rate must be monthly too: for 6% a year use 0.06/12; for a 25-year loan the number of periods is 25*12, or 300. Forgetting to divide the annual rate by 12 makes the payment many times too large.
Excel's financial functions track the direction of money with its sign: money you receive is positive, money you pay out is negative. When you take out a loan the amount comes to you (positive) and the payments go out, so =PMT(0.06/12,300,300000) returns a negative number. To see the payment as a positive number, put a minus sign in front of the formula. In a savings plan the target amount is positive and the monthly deposit comes back negative.
IPMT and PPMT split each payment into interest and principal. To go from a payment to a loan amount use PV, to find the term use NPER, and to find the rate use RATE. A lender's quoted payment can differ from this result because of fees, insurance and taxes.
Syntax
=PMT(rate, nper, pv, [fv], [type])=DEVRESEL_ÖDEME(oran; dönem_sayısı; bd; [gd]; [tür])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. For monthly payments, the annual rate divided by 12: 0.06/12 or C2/12. |
nper | Required | The total number of payments. For a 25-year loan paid monthly, 25*12, or 300. |
pv | Required | The present value: the amount the loan gives you (money in, positive). For a savings plan that starts from nothing, 0. |
fv | Optional | The amount to reach after the last payment. Leave it out (0) for a loan; for a savings plan, enter the target as a positive number. |
type | Optional | When payments are made: 0 or omitted at the end of each period (usual for loans), 1 at the start. |
Examples
1Monthly payment on a mortgage and a car loan
Column E divides the annual rate by 12, multiplies the term by 12 and gives the loan amount as a positive number, so the payment comes back negative. Column F writes the total repaid as a positive number by flipping the sign. The rates are assumptions.
=PMT(C2/12,D2*12,B2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Loan | Amount | Annual Rate | Term (Years) | Monthly Payment | Total Repaid |
| 2 | Mortgage (25 yrs) | 300,000.00 | 6.00% | 25 | -1,932.90 | 579,871.26 |
| 3 | Mortgage (30 yrs) | 300,000.00 | 6.00% | 30 | -1,798.65 | 647,514.57 |
| 4 | Car loan | 25,000.00 | 7.50% | 5 | -500.95 | 30,056.92 |
- E2 (filled down to E4)
=PMT(C2/12,D2*12,B2) - F2 (filled down to F4)
=-E2*D2*12
The 25-year mortgage costs -1,932.90 a month; the minus sign shows the money leaves you. Stretching the term to 30 years lowers the payment (-1,798.65) but raises the total repaid (647,514.57).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2How much to save each month to reach a goal
There are no savings at the start, so pv is 0 and the target goes in fv as a positive number. The monthly deposit leaves your pocket, so the result is negative. The returns are assumptions.
=PMT(C2/12,D2,0,B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Goal | Target | Annual Return | Months | Monthly Deposit |
| 2 | House deposit | 40,000.00 | 4.50% | 36 | -1,039.88 |
| 3 | Holiday | 5,000.00 | 4.50% | 12 | -408.14 |
| 4 | University | 60,000.00 | 4.50% | 60 | -893.58 |
- E2 (filled down to E4)
=PMT(C2/12,D2,0,B2)
Saving a house deposit in 36 months takes -1,039.88 a month. Because returns do part of the work, the total paid in is less than the target. The holiday goal needs -408.14 a month.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Payment far too large
Cause: The annual rate was not divided by 12, so Excel applied the full annual rate to every month.
Fix: Put the rate in the same unit as the periods:
0.06/12.- Negative result
Cause: The loan amount was entered as a positive number, so the payment comes back negative. That is not an error; it shows the money leaving you.
Fix: To see it as positive, use
=-PMT(…)or enter pv as a negative number.- #NUM!
Cause: nper is 0, so there are no periods to spread the payment over.
Fix: Make sure the number of periods is at least 1 and the term cell is not empty.
Good to know
- Keep the rate and the term in their own cells; the formula becomes
=PMT(C2/12,D2*12,B2)and trying different scenarios gets easy. - The total repaid is the payment times the number of periods; the total interest is that minus the loan amount. Because the payment is negative, put a minus sign in front of the product.
In Nexell
Get Nexell to do it from a sentence
Add monthly payment and total repaid columns to the loan table based on the amount, annual rate and term
Convert to tableBring your loan table into the tool and type this. Nexell adds both columns with real Excel formulas and shows them in the preview; in the downloaded file, the payment changes when the rate or term does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I calculate a loan payment in Excel?
Use =PMT(annual_rate/12,years*12,loan_amount). For example, for a 25-year 300,000 mortgage at 6% a year, =PMT(0.06/12,300,300000). The result is negative because the payment is money you pay out.
Why is the PMT result negative?
In Excel, the loan amount is money you receive, so it is positive, and the payment is money you pay, so it is negative. To show it as positive, put a minus sign in front of the formula: =-PMT(0.06/12,300,300000).