Part of the free Module 5: Excel Formulas and Functions · Function 68 of 102 · Full Excel course
The Excel PMT function calculates the fixed periodic payment for a loan or the regular deposit needed to reach a savings goal, given a constant interest rate and a fixed number of periods. PMT in Excel returns the payment as a negative number (money going out), covering both principal and interest, and is the core of every mortgage, car-loan and EMI calculator.
PMT syntax
=PMT(rate, nper, pv, [fv], [type])Arguments
| Argument | Required | Meaning |
|---|---|---|
| rate | Required | Interest rate per period. For monthly payments divide the annual rate by 12. |
| nper | Required | Total number of payments. For a 2-year monthly loan, 24. |
| pv | Required | Present value: the loan amount, or 0 for a savings plan starting from nothing. |
| fv | Optional | Future value wanted after the last payment. Default 0 (loan fully repaid). |
| type | Optional | 0 or omitted: payments at the end of each period. 1: payments at the beginning. |
Keep rate and nper in the same unit: monthly rate with monthly periods, annual rate with annual periods.
Step-by-step example
You borrow 10,000 at 5% a year for 2 years with monthly repayments.
=PMT(5%/12, 2*12, 10000)- 5%/12 converts the annual rate to a monthly rate of 0.4167%.
- 2*12 gives 24 monthly periods.
- Excel returns -438.71: a payment of 438.71 each month clears the loan. The minus sign shows cash leaving your account.
Practical use cases
1. Monthly deposit to reach a savings target
=PMT(3%/12, 5*12, 0, 20000)Returns approximately -309.37: save 309.37 a month at 3% to have 20,000 after five years.
2. Car loan with a positive result
=-PMT(7%/12, 4*12, 15000)About 359.20 a month. The leading minus flips the sign for display; alternatively enter pv as -15000.
3. Student loan over 10 years
=PMT(6%/12, 10*12, 25000)Approximately -277.55 per month.
4. Deposits at the start of each month
=PMT(4%/12, 20*12, 0, 50000, 1)About -135.87 a month to reach 50,000 in 20 years. Setting type to 1 slightly lowers the payment because each deposit earns one extra month of interest.
5. Total interest paid over the life of a loan
=-PMT(B1/12, B2*12, B3)*B2*12 - B36. Interactive EMI calculator
=ROUND(-PMT($B$1/12, $B$2*12, $B$3), 2)Link B1 (rate), B2 (years) and B3 (amount) to input cells and the payment updates instantly. Pair with IPMT and PPMT for an amortisation schedule.
Common mistakes and errors
- Payment far too large – the annual rate was used with monthly periods. Divide rate by 12 (or 4 for quarterly, 52 for weekly).
- #NUM! – rate is so extreme or nper so long that the result overflows, or nper is 0 with a non-zero pv.
- #VALUE! – an argument is text, for example “5%” typed with quotes or a number with a stray character.
- Negative result confusion – it is not an error. Enter pv as a negative, or prefix PMT with a minus, to display a positive payment.
- Fees and variable rates – PMT assumes a constant rate and no charges. Use separate calculations or a schedule for floating-rate or fee-laden loans.
Related functions
- ACCRINT – accrued interest on bonds.
- IF – build validation around the inputs of a loan calculator.
- EDATE – generate the payment dates for an amortisation schedule.
- IPMT, PPMT, NPER, RATE and FV are the companion financial functions (interest part, principal part, number of periods, rate and future value).
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Why is the PMT result negative?
Excel follows cash-flow sign convention: money you pay out is negative, money you receive is positive. Enter the loan amount as a negative number, or put a minus before PMT, to show a positive payment.
How do I calculate a monthly payment with an annual interest rate?
Divide the annual rate by 12 and multiply the years by 12: =PMT(rate/12, years*12, loan). Keeping rate and nper in the same time unit is the most important rule for PMT.
Can PMT handle a variable interest rate?
No. PMT assumes one constant rate for every period. For rate changes, build an amortisation table and recalculate the payment at each reset, or use separate PMT formulas for each rate period.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.