PMT Formula in Excel: Full Guide with Examples

Formulas & functions · Updated November 22, 2026

The PMT function in Excel calculates the periodic payment for a loan or investment based on a constant interest rate and a fixed number of payments. The syntax is PMT(rate, nper, pv, [fv], [type]), where rate is the interest rate per period, nper is the total number of payment periods, and pv is the present value (the total loan amount or initial investment).

What the PMT Function Does

PMT returns a negative number because it represents an outgoing payment. For example, if you borrow $10,000 at 6% annual interest for 3 years with monthly payments, the formula =PMT(6%/12, 36, 10000) returns -304.22. That means you pay $304.22 each month. The function assumes payments are made at the end of each period by default (type=0). If payments are due at the beginning, set type=1, which slightly reduces the payment amount.

PMT is part of Excel’s financial functions, which also include IPMT (interest portion of a payment), PPMT (principal portion), and NPER (number of periods). These are often used together to build a full amortization schedule.

Understanding the Arguments

  • rate: The interest rate for each period. If your loan has an annual rate but monthly payments, divide by 12. For quarterly, divide by 4.
  • nper: Total number of payment periods. For a 5-year monthly loan, this is 60.
  • pv: Present value – the total amount of the loan or the starting balance of an investment. For a loan, this is a positive number you enter.
  • fv (optional): Future value – the cash balance you want after the last payment. For a loan, this is usually 0. For savings goals, you might set a target amount.
  • type (optional): 0 (default) for payments at the end of each period, 1 for payments at the beginning.

All arguments must be numbers or cell references. If you omit fv or type, Excel uses 0. If you enter a negative pv (like for an investment), PMT returns a positive number, meaning you receive money.

How to Use PMT in a Real Spreadsheet

Suppose you have a car loan: $25,000, 4.5% annual interest, 60 months. Set up cells: A1=25000, A2=4.5%, A3=60. Then in A4 enter =PMT(A2/12, A3, A1). The result is -466.14 (rounded). That is your monthly payment. To see the total interest paid, multiply the payment by nper and subtract the principal: =ABS(A4)*A3 - A1 gives 2,968.40.

For a savings goal, say you want $50,000 in 10 years with a 3% annual return, making monthly deposits. Use =PMT(3%/12, 120, 0, 50000). This returns -358.22, meaning you need to deposit $358.22 each month.

Common Errors and How to Fix Them

  • #NUM! – Occurs when rate and nper are inconsistent (e.g., annual rate with monthly periods but you forgot to divide by 12).
  • #VALUE! – Occurs if any argument is text or a non-numeric value.
  • Negative result surprises – PMT returns a negative number for loans. Many users expect positive. Wrap with ABS() to get a positive number, or use =-PMT(...).
  • Wrong rate conversion – For monthly payments, always divide the annual rate by 12. For quarterly, divide by 4. Never use the annual rate directly with monthly periods.

PMT vs. IPMT and PPMT

PMT gives the total payment, but you often need to know how much goes to interest vs. principal. IPMT and PPMT split that. For the same loan, =IPMT(rate, per, nper, pv) returns the interest for a specific period, and =PPMT(rate, per, nper, pv) returns the principal. The sum of IPMT and PPMT equals PMT for that period. For example, with A1=25000, A2=4.5%/12, A3=60, the first month’s interest is =IPMT(A2, 1, A3, A1) which returns -93.75, and principal is =PPMT(A2, 1, A3, A1) which returns -372.39. Adding them gives -466.14.

To build an amortization schedule, list periods 1 through 60 in a column, then use IPMT and PPMT with each period number. This is a common way to track loan balances over time.

Using PMT for Different Payment Frequencies

The rate and nper must match the payment frequency. If you pay monthly, divide the annual rate by 12 and multiply years by 12. If you pay bi-weekly (every two weeks), divide the annual rate by 26 and multiply years by 26. For example, a $200,000 mortgage at 5% over 30 years with monthly payments: =PMT(5%/12, 360, 200000) returns -1,073.64. With bi-weekly payments (26 per year), =PMT(5%/26, 780, 200000) returns -536.82 per payment, but you make 26 payments a year, so total annual payment is higher, reducing the loan faster.

Be careful: some lenders use 12 monthly payments, others use 26 bi-weekly. Always confirm the actual number of periods per year with your lender.

PMT with Variable Rates or Extra Payments

PMT assumes a constant interest rate. For variable-rate loans, you cannot use PMT directly; you would need to break the loan into segments with different rates and calculate each segment separately, or use a more complex model. For extra payments, PMT does not account for them. To model extra payments, you can build a custom amortization schedule where you subtract extra amounts from the balance each period. PMT is only accurate for fixed-rate, fixed-term loans without prepayments.

If you want to see how extra payments affect the loan term, you can use the NPER function. For example, if you pay $1,200 per month on a $200,000 loan at 5%, =NPER(5%/12, -1200, 200000) returns 232.08 months, compared to 360 months with the minimum payment.

Frequently Asked Questions

Why does PMT return a negative number? Because it represents a cash outflow. For a loan, you pay money out. To get a positive number, use =ABS(PMT(...)) or put a minus sign before the function.

Can I use PMT for a savings goal? Yes. Set pv to 0 (or your starting balance), fv to your target amount, and type to 1 if you deposit at the beginning of each period. The result is the amount you need to save each period.

What if I have a down payment? Subtract the down payment from the loan amount before using PMT. For example, if a house costs $300,000 and you put 20% down, the loan amount is $240,000, so use that as pv.

Does PMT include taxes and insurance? No. PMT only covers principal and interest. Escrow for taxes and insurance must be added separately.

Is PMT the same in Google Sheets? Yes, the function and syntax are identical, but the Mac version of Excel uses the same formula. The only difference is that Google Sheets may use semicolons as argument separators in some locales, but the function name is the same.

How do I get the total interest paid? Multiply the monthly payment by the total number of payments and subtract the principal. For example, =ABS(PMT(5%/12, 360, 200000))*360 - 200000 gives the total interest.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.