FormuladrillPMT
PMT(rate, nper, pv, [fv], [type]) returns the constant payment per period for a loan or annuity at a fixed interest rate. Use the rate and number of periods in the same unit (6%/12 and 360 for a 30-year monthly loan). The result is negative because it is money paid out.
Syntax
PMT(rate, nper, pv, [fv], [type])| Part | What it is |
|---|---|
rate | Interest rate per period: annual rate divided by 12 for monthly payments. |
nper | Total number of payments: years times 12 for monthly. |
pv | Present value, the loan amount (positive) or the amount deposited now. |
fv | Balance left at the end. 0 by default; use the target for a savings goal. |
type | 0 payments at the end of each period (default), 1 at the start. |
Examples
=PMT(6%/12, 30*12, 250000)Result: -1,498.88
Monthly payment on a 250,000 mortgage at 6% over 30 years. Negative because it is a payment.
=-PMT(5%/12, 60, 20000)Result: 377.42
Car loan of 20,000 at 5% over 5 years, shown positive.
=PMT(4%/12, 120, 0, 50000)Result: -339.56
Monthly deposit to reach 50,000 in 10 years at 4%, starting from zero.
=-PMT(B1/12, B2*12, B3) * B2 * 12 - B3Result: Total interest paid over the life of the loan in B3 at rate B1 for B2 years
Common mistakes
- The payment is far too large: the annual rate was used with monthly periods. Divide the rate by 12 when nper is in months.
- Positive and negative confusion: pv positive gives a negative payment. Either accept it or write =-PMT(...).
- #NUM!: rate is 0 with a non-zero fv in some edge cases, or nper is 0. Check the inputs.
- Rounding each payment to cents and multiplying by nper gives a total slightly different from the exact figure; banks round the installment up to a whole amount, so match their rule.
Practice PMT
These drills check your answer on live data, free, no account needed.
- Loan instalmentCalculate the monthly instalment with PMT. Rates and terms are yearly, payments are monthly. Banks do not use cents in instalments, so round the result up to a whole amount. The answer must be a positive number.
- Total interest on a loanA loan is repaid monthly over the years in B3 at the yearly rate in B2. Calculate the total interest paid over the life of the loan, as a positive number, in B4.
- Value of an annuityWhat is the value today of receiving the yearly payment in B1 for the number of years in B3, at the rate in B2? The answer must be positive. Put it in B4.
Questions
How do I calculate a monthly mortgage payment in Excel?
=PMT(annual_rate/12, years*12, loan_amount). For 300,000 at 5% over 25 years that is =PMT(5%/12, 300, 300000), about -1,753.77.
How do I split the payment into interest and principal?
IPMT(rate, period, nper, pv) gives the interest for one period and PPMT the principal. They add up to PMT.
Why is the PMT result negative?
Excel treats money you pay out as negative and money you receive as positive. A loan you received (positive pv) is repaid with negative payments.
Related
- NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.
- IRRHow the Excel IRR function finds the rate at which cash flows net to zero, with a worked example, the cash flow layout it needs, the #NUM! fix and XIRR.
- EOMONTH and EDATEHow the Excel EOMONTH function returns the last day of a month and EDATE adds or subtracts whole months, with examples for payment terms and first of month.
- ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.