PMT Formula Not Working? Fix Excel Payment Errors

If your Excel PMT formula is not working, first make the interest rate and number of periods match the payment schedule: monthly payments need a monthly rate and a total number of months. Also check the sign of the loan amount—when the present value is positive, PMT normally returns a negative payment because Excel treats it as money flowing out.

What PMT calculates

PMT calculates the regular payment for a loan or other cash flow when the interest rate stays constant and payments are made at regular intervals. It returns a payment that covers interest and, where applicable, gradually repays the principal over the specified number of periods. It is commonly used for monthly loan estimates, but the function itself does not assume that payments are monthly.

The function uses this argument order: rate, nper, pv, fv, type. Rate is the interest rate for one payment period; nper is the total number of payment periods; pv is the present value, such as the amount borrowed; fv is the balance you want after the final payment; and type specifies when payments are due. You can omit fv and type for the usual loan calculation. The default future value is zero and the default type is zero, meaning payments are due at the end of each period.

PMT is not a full loan quote. Its result does not automatically include fees, insurance, taxes, changing interest rates, or other charges. A lender’s actual payment can therefore differ from a basic PMT estimate.

Check the rate and number of periods

The most frequent cause of a surprising result is mixing annual and monthly units. For a loan paid monthly over three years, nper must be 36, not 3, and rate must be the rate per month, not the annual rate. Excel does not infer or convert the interval from your labels or formatting.

For a standard nominal annual rate quoted with monthly payments, dividing the annual rate by 12 gives the monthly rate. Multiply the term in years by 12 to get the number of monthly payments. For quarterly payments, use a quarterly rate and the total number of quarters; for annual payments, use an annual rate and the number of years. The rate and nper must describe the same interval.

Percent formatting can also be misleading. A cell displaying 6% generally stores the decimal value 0.06, which is appropriate as an annual rate for the monthly calculation after division by 12. A cell containing the number 6 and formatted as a percentage represents 600%, not 6%. Formatting changes how a value looks, not the value used in the calculation.

If a quoted rate is an effective annual rate rather than a nominal annual rate, simply dividing it by 12 does not produce the mathematically equivalent monthly rate. Check how the rate is defined in the loan terms, and use the rate period that matches the stated compounding and payment arrangement.

Use the right signs for cash flowing in and out

Excel’s financial functions use a cash-flow convention: money you receive and money you pay out have opposite signs. In a borrowing calculation, the loan proceeds are money received, while each payment is money paid. If you enter the amount borrowed as a positive pv, PMT will generally return a negative result. That negative sign does not mean the calculation failed; it indicates an outgoing payment.

If you want the displayed payment as a positive number, you can reverse the sign of the PMT result. Alternatively, enter the amount borrowed as a negative pv, in which case the returned payment will normally be positive. Choose one convention and use it consistently. Do not change signs just to hide a negative value unless you understand what the sign represents, particularly when a model includes several cash flows.

A result of zero can be legitimate if the inputs imply no payment—for example, a zero principal and no future balance. It can also signal that a referenced cell is blank or contains zero when you expected a loan amount or rate. Inspect the underlying cell values rather than relying only on their displayed labels.

Worked monthly loan example

Suppose B1 contains a principal of 20,000, B2 contains an annual nominal interest rate of 6%, and B3 contains a term of 3 years. The loan is paid monthly, at the end of each month, and the target balance after the last payment is zero. With those cells and units, use:

=-PMT(B2/12,B3*12,B1)

The result is approximately 608.44 per month, displayed as a positive amount because the formula negates PMT’s cash-flow result. The rate passed to PMT is 0.5% per month (6% divided by 12), and nper is 36 (3 years multiplied by 12). If you remove the leading minus sign, the result is approximately -608.44. Minor differences in displayed decimals can arise from rounding the cell format; the underlying calculation retains more precision.

This example assumes a fixed rate, equal monthly periods, end-of-period payments, and no extra charges. If payments are due at the beginning of each period, the type argument must be 1 instead of the default 0. That timing changes the calculation because each payment is made one period earlier. Confirm the payment timing and rate convention against the actual agreement rather than treating the example as a quote.

Recognize common Excel errors

#NUM!: Check for an invalid or impossible numeric input, especially a nonpositive number of periods or an unsuitable rate. Make sure the loan term has not accidentally become zero through a bad cell reference.
#VALUE!: One or more required arguments may be text instead of numbers. A cell that contains text such as a typed currency label, a number stored as text, or an unexpected space can prevent the calculation.
#NAME?: Check that the function is spelled PMT and that names used in the formula are defined. A misspelled function or an unrecognized name can produce this error.
A result that is far too large or small: Look for a 6 entered where 6% was intended, a term in years passed as monthly periods, or an annual rate used as a monthly rate.
A negative payment: This is usually the expected cash-flow sign when pv is positive, not an error. Decide whether you want to preserve the cash-flow convention or show the payment as positive.

Also check that the formula points to the intended cells. Copying a formula can shift relative references, so a formula that works in one row may use a different rate or principal after it is filled down. Click the formula cell and inspect its references, then compare them with the input labels.

Check cell contents and calculation settings

If the formula is visible in the cell instead of a result, the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula so Excel evaluates it. Merely changing a cell’s format after typing a formula may not trigger the text entry to become a calculation; editing and confirming the entry is often necessary.

Check whether Excel is set to calculate automatically. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. The corresponding calculation option is also available on the Formulas tab in Excel for Mac, though the ribbon layout can differ. If calculation was set to Manual, formulas may show stale results until recalculated. A workbook opened from another file can retain a manual calculation setting, so it is worth checking when results do not update after an input changes.

Regional settings can affect how Excel expects separators and decimal marks. The formulas in this article use commas between arguments, as in standard English-language Excel syntax. If your installation expects semicolons as list separators, Excel may reject a comma-separated entry; use the separator required by your Excel settings. Do not replace a decimal point or percentage value with a different character without checking how your locale interprets numbers.

Understand what PMT leaves out

PMT models a level payment under a fixed-rate assumption. It is useful for checking the basic relationship between principal, rate, and term, but it cannot represent every real-world repayment structure. A variable-rate loan, irregular payment dates, a balloon payment, or fees financed into the loan may need additional inputs or a different model.

The optional future-value input lets the calculation target a balance other than zero at the end of the term. The optional type input distinguishes payments at period end from payments at period beginning. If either feature applies, leaving the argument out may produce a result that differs from the intended arrangement. In an amortization schedule, Excel’s IPMT and PPMT functions can separately calculate the interest and principal portions of a payment; PMT alone gives the combined regular payment.

For budgeting or comparison, check the assumptions and calculations against the lender’s disclosed terms. This example is illustrative spreadsheet arithmetic, not financial, tax, investment, or legal advice. Ask the lender or a qualified professional to clarify actual rates, fees, timing, and repayment conditions.

Frequently Asked Questions

Why does PMT return a negative number?

Excel marks incoming and outgoing cash flows with opposite signs. If the amount borrowed is entered as a positive present value, the payment is an outflow and PMT usually returns it as a negative number. The result can be valid even if you prefer to display the payment as positive.

Should I divide the interest rate by 12 in PMT?

Divide by 12 when the input is a nominal annual rate and the calculation is for monthly periods with the corresponding monthly rate convention. Also set nper to the total number of monthly payments. If the rate is defined differently, check its terms before converting it.

Why is my PMT result much higher than expected?

Check whether a percentage was entered as a whole number, whether the annual rate was used without conversion for monthly payments, and whether the term was entered in the wrong units. Also verify the principal reference and whether fees or a nonzero ending balance were included.

Does PMT include interest and principal?

For a standard fully amortizing loan, PMT returns the combined regular payment that covers interest and repayment of principal. The share going to each changes over time. The function does not automatically add separate fees, taxes, or insurance charges.

What does the type argument in PMT mean?

Type specifies payment timing: 0 means payments are due at the end of each period, and 1 means they are due at the beginning. If omitted, Excel uses 0. Use the timing that matches the arrangement you are modeling.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.