PMT function

PMT calculates the constant periodic payment needed to repay a loan or investment over a fixed number of periods at a fixed interest rate.

=PMT(rate, nper, pv, [fv], [type])

Generate a PMT formula

Describe what you need. The generator will reach for PMT where PMT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How PMT reads its arguments
raterequirednperrequiredpvrequiredfvoptionaltypeoptionalPMT →
ArgumentRequiredDescription
rateRequiredThe interest rate per period as a decimal (e.g., 0.5% monthly). Must be a number greater than −1; expressed as a decimal (0.006 for 0.6%), not text.
nperRequiredThe total number of periods for repayment. Must be a positive number; zero or negative values produce #NUM! errors.
pvRequiredThe present value (loan principal), typically negative to represent money borrowed. Positive pv calculates payments for investments where cash is received upfront.
fvOptionalOptional future value remaining after all payments (default 0). Represents a balloon payment or final lump sum; if specified, it reduces the periodic payment.
typeOptionalOptional payment timing: 0 (default) = payment at end of period, 1 = payment at beginning of period. Beginning-of-period yields slightly lower payment amounts.

Returns

A number representing the payment amount per period (typically negative to indicate money paid out).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate monthly payment for olive oil supplier financing

IngredientSupplierUnitQtyUnit CostTotal
Olive OilItalian Imports Incliters50$15$750
TomatoesFresh Farms Cokg100$2.50$250
FlourBest Flour Millskg200$1.80$360
Chicken BreastLocal Poultrykg150$8.50$1,275
MozzarellaDairy Directkg80$12$960
=PMT(7.2%/12, 6, -750)

Result: $127.80

The restaurant financed a $750 olive oil purchase from Italian Imports at 7.2% annual interest over 6 months. PMT converts the annual rate to a monthly rate (7.2%÷12) and calculates that each monthly payment will be $127.80. The negative pv indicates borrowed money that must be repaid.

2. Calculate payment with balloon payment for chicken inventory

IngredientSupplierUnitQtyUnit CostTotal
Olive OilItalian Imports Incliters50$15$750
TomatoesFresh Farms Cokg100$2.50$250
FlourBest Flour Millskg200$1.80$360
Chicken BreastLocal Poultrykg150$8.50$1,275
MozzarellaDairy Directkg80$12$960
=PMT(8.4%/12, 12, -1275, 100)

Result: $111.47

The restaurant financed $1,275 of chicken from Local Poultry at 8.4% annual interest over 12 months with a $100 balloon payment due at the end. The fv argument (100) reduces the periodic payment because the final lump sum covers part of the debt. Monthly payments are $111.47.

3. Calculate payment due at start of period for flour purchase

IngredientSupplierUnitQtyUnit CostTotal
Olive OilItalian Imports Incliters50$15$750
TomatoesFresh Farms Cokg100$2.50$250
FlourBest Flour Millskg200$1.80$360
Chicken BreastLocal Poultrykg150$8.50$1,275
MozzarellaDairy Directkg80$12$960
=PMT(4.8%/12, 12, -360, 0, 1)

Result: $30.80

The restaurant financed $360 of flour from Best Flour Mills at 4.8% annual interest over 12 months. The type=1 argument specifies payments due at the beginning of each period (common for ingredient suppliers), which slightly reduces the monthly payment to $30.80 compared to end-of-period payment.

Common errors

Which PMT error are you seeing?
PMT returned an error#VALUE!
Convert all inputs to numeric format. Use =PMT(7.2%/12, 6, -750) instead of =PMT("7.2%/12", 6, -750). Verify that cell references point to cells containing numbers, not text that looks like numbers.
#NUM!
Ensure nper is 1 or greater. Verify that rate is always greater than −1. If dynamically calculating rate, add validation to keep it in valid bounds (typically −0.99 to 0.99 for real-world scenarios).
#DIV/0!
Avoid unrealistic interest rates. Validate that your rate argument is reasonable for the financial scenario—typically between −50% and 100% for practical use. Use IFERROR() to catch and handle unexpected rate values.
ErrorWhy it happensHow to fix it
#VALUE!One or more arguments (rate, nper, pv, fv, type) contains text instead of a number. For example: =PMT("7.2%", 6, -750) treats the rate as a string.Convert all inputs to numeric format. Use =PMT(7.2%/12, 6, -750) instead of =PMT("7.2%/12", 6, -750). Verify that cell references point to cells containing numbers, not text that looks like numbers.
#NUM!The nper argument is zero or negative (must be a positive integer), or the rate equals or approaches −1, creating an impossible calculation. For example: =PMT(7.2%/12, 0, -750).Ensure nper is 1 or greater. Verify that rate is always greater than −1. If dynamically calculating rate, add validation to keep it in valid bounds (typically −0.99 to 0.99 for real-world scenarios).
#DIV/0!In edge cases with extreme negative rates approaching −100%, the denominator of the PMT calculation formula approaches zero, causing a division error.Avoid unrealistic interest rates. Validate that your rate argument is reasonable for the financial scenario—typically between −50% and 100% for practical use. Use IFERROR() to catch and handle unexpected rate values.

Tips and when to use something else

  • Always match the rate frequency to the payment frequency: divide annual rates by 12 for monthly, by 4 for quarterly, by 2 for semi-annual payments.
  • Negative pv means borrowed money; positive pv means money invested upfront and withdrawn later. Ensure the sign matches your real-world scenario.
  • If you know the payment amount and need to find the interest rate, use RATE(). If you need the number of periods, use NPER(). To see how much of each payment goes to interest versus principal, use IPMT() and PPMT() instead of PMT().
  • PMT assumes a fixed interest rate and constant periodic payments. For irregular cash flows, changing rates, or one-time cash events, use NPV or XNPV instead.

Related functions

RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.NPERNPER returns the number of payment periods needed to pay off a loan or reach an investment goal at a fixed interest rate.IPMTIPMT returns the interest portion of a loan or investment payment for a specific period with constant payments and interest rate.PPMTReturns the principal payment for a given period of a loan or investment, given constant periodic payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.

Frequently asked questions

Why is my PMT result showing as negative when I expected a positive payment?
PMT returns a negative value because you entered pv as negative (representing borrowed money). A negative payout indicates money leaving your account. To display it as positive in reports, negate it: =-PMT(rate, nper, -750), or enter pv as a positive value if cash is flowing inward.
Can I use PMT for scenarios other than loans, like savings plans or leases?
Yes. PMT applies to any regular periodic payment: mortgage repayment, equipment leases, inventory financing, investment withdrawal schedules, or structured payoff plans. The logic is universal: principal, interest rate, and time determine the constant payment.
What is the difference between PMT and IPMT, and when should I use each?
PMT calculates the total payment each period (principal + interest combined). IPMT returns only the interest portion for a specific period. Use IPMT when you need to report, track, or separate interest expense from principal repayment.
My interest rate is quarterly but I want monthly payments—how do I handle that?
Adjust the rate to match your payment frequency. If you have a quarterly rate and need monthly payments, divide the quarterly rate by 3. Always ensure rate and nper use the same time unit (both monthly, both quarterly, etc.) for accurate results.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17