Your PMT function is usually negative because Excel treats the loan amount and its repayments as cash flows in opposite directions: money you receive is positive, and money you pay out is negative. If the present value (the loan amount) is entered as a positive number, a negative payment is the expected result—not necessarily an error.
To display the payment as a positive amount, enter the loan amount as a negative value in PMT, or put a minus sign before the PMT function. Choose one approach, not both, and keep the cash-flow signs consistent with what your worksheet represents.
How Excel’s PMT function handles signs
PMT calculates a regular payment for a loan or other financial arrangement, assuming a constant interest rate and equal payments over a set number of periods. Its arguments specify the interest rate for each period, the total number of periods, the present value, and optionally a future value and whether payments are due at the beginning or end of each period.
The sign is based on cash flow, not on whether the payment is mathematically positive or negative in everyday conversation. For a standard loan, you receive the borrowed principal at the start and pay installments later. If the principal is positive in the formula, Excel treats it as cash coming in. The matching payments must go out, so PMT returns a negative number.
This convention lets Excel balance the different parts of a financial calculation. For instance, when modeling a loan, you might record the initial advance as a positive amount and each repayment as a negative amount. If both the principal and payments had the same sign, they would describe cash flows moving in the same direction, which is not the usual borrowing scenario.
A worked loan-payment example
Suppose a loan is $20,000, its annual interest rate is 6%, and it is repaid in 60 monthly installments. Put the annual rate in B2, the number of monthly payments in B3, and the loan principal in B4. Enter the rate as 6% and the principal as 20000. Because the payment period is monthly, divide the annual rate by 12.
With an annual rate in B2, monthly payment count in B3, and positive loan principal in B4, enter:
=PMT(B2/12,B3,B4)
The result is approximately -$386.66 per month. The minus sign indicates money paid out from the borrower’s perspective. It does not mean the lender owes the borrower a negative amount or that the loan calculation failed.
If you prefer the worksheet to show the borrower’s installment as a positive expense amount, use the same cell layout but enter the principal as a negative value:
=PMT(B2/12,B3,-B4)
This returns approximately $386.66. The payment amount has not changed; the signs now describe the cash flows from the opposite perspective. You can also keep B4 positive and negate the result:
With the annual rate in B2, payment count in B3, and positive principal in B4, use:
=-PMT(B2/12,B3,B4)
That also returns approximately $386.66. Avoid using both a negative principal and a minus sign before PMT: doing so flips the sign twice and returns a negative payment again.
Match the interest rate and payment periods
A sign that seems wrong can be distracting, but a more consequential mistake is using an interest rate that does not match the payment schedule. PMT needs a rate per period, not automatically the advertised annual rate. For monthly payments on a loan with a quoted annual rate, a common calculation uses the annual rate divided by 12, as in the example.
The number of periods must use the same unit. For a five-year loan with monthly payments, enter 60 periods, not 5. For quarterly payments over five years, use 20 periods and a quarterly rate. When the lender’s rate is an effective annual rate or the loan has unusual compounding terms, dividing by 12 may not reproduce the lender’s calculation. Check the rate definition and payment terms in the loan documents rather than assuming every annual rate is converted the same way.
Also check that the rate is entered as a percentage or decimal correctly. A cell displaying 6% has a stored value of 0.06, while entering 6 as an ordinary number means 600% in a calculation. Formatting a number as a percentage changes how it appears; it does not repair an incorrect underlying value.
What the optional PMT arguments change
The future-value argument is often zero for a loan that is fully repaid by the final installment. A different future value can represent a remaining balance or a target amount at the end of the payment schedule. The type argument controls timing: 0 means payments are due at the end of each period, and 1 means payments are due at the beginning. If omitted, Excel treats the payment as due at the end.
Changing timing or the future value can change the payment amount, but it does not remove the cash-flow sign convention. If the loan principal is positive and the future value is zero, PMT will generally still return a negative payment. A lease, balloon loan, or savings plan may need different assumptions, so verify what each argument represents before interpreting the result.
PMT calculates a level payment, not a full payment schedule. It does not separately show each period’s interest and principal, and it does not automatically include fees, insurance, taxes, or other charges. A payment calculated from the loan’s rate and principal can therefore differ from the total amount a borrower is billed.
Common reasons a PMT result looks wrong
- The principal has the opposite sign from your preferred display. A positive present value commonly produces a negative payment. Decide which perspective you are modeling and use matching signs.
- The annual rate is used as a monthly rate. For monthly payments, this can make the calculated installment far too large. Convert the rate to the payment period when appropriate.
- The number of periods is in the wrong unit. A 60-month loan uses 60 monthly periods. Entering 5 instead describes five payment periods, not five years of monthly payments.
- The percentage was entered as 6 instead of 6%. Confirm the stored value in the formula bar or inspect the cell’s number format and underlying entry.
- The payment timing is different. Payments at the beginning of a period use type 1; payments at the end use type 0. Check the contract’s timing.
- The displayed value is formatted to look unusual. Accounting formats may show a negative number with parentheses or a dash. Select the cell and check the formula bar to see the underlying result.
- The formula references the wrong cells. Verify that the principal, rate, and period count are the intended values, especially after copying a formula to another row.
How to choose the right sign convention
There is no universal rule that the displayed payment must be positive. Use the sign that makes sense for the rest of your model. If the worksheet is a simple budget and you want to read the installment as an expense, a positive display may be clearer. If the worksheet tracks cash flowing into and out of an account, retain the signs that distinguish receipts from payments.
For a borrower’s cash-flow view, the loan proceeds can be positive and the subsequent payments negative. For a lender’s view, the initial advance is money paid out and the repayments are money received, so the signs reverse. Either viewpoint is valid when applied consistently. A useful check is to label the perspective near the calculation and ensure that incoming and outgoing cash flows do not accidentally share the same sign.
When comparing two loan scenarios, use the same convention, period length, and payment timing in both calculations. Otherwise, a positive-versus-negative display difference can be mistaken for a change in the actual payment. Compare the absolute payment amounts only after confirming that the assumptions match.
When a negative result is not just a sign issue
If you expected a positive display, first check the present-value sign and the formula’s other references. If the payment amount is also unexpectedly large or small, review the rate and period units before changing signs. Negating an incorrect result only changes its direction; it does not correct an incorrect interest rate, loan balance, or term.
PMT is intended for fixed-rate, equal-payment calculations. A variable-rate loan, irregular payment dates, extra repayments, changing installments, or a payment schedule with fees may not be represented accurately by one PMT result. In those cases, a period-by-period schedule or the lender’s disclosed figures may be more appropriate. For a real borrowing decision, check actual terms with the lender or a qualified professional; this spreadsheet example is illustrative, not financial advice.
In Excel for Microsoft 365 on Windows, the PMT formula works in the worksheet grid. The same function is available in Excel for Mac and Excel for the web; the core sign convention and formula logic are the same. Some regional settings use semicolons rather than commas between formula arguments, so follow the separators accepted by your Excel installation if a pasted formula is rejected.
Frequently Asked Questions
Should a loan payment in Excel be negative?
It can be. If the loan principal is entered as positive cash received, PMT normally returns the repayment as negative cash paid. A positive display is also reasonable when the worksheet is presenting the installment as an expense rather than modeling signed cash flows.
How do I make PMT return a positive number?
Enter the present value as negative, or place a minus sign before the PMT function. Use one method only. If both the principal and the function are negated, the result becomes negative again.
Why is my PMT result much larger than the loan payment?
Check whether you used an annual interest rate as if it were a monthly rate, entered the term in years instead of monthly periods, or typed 6 instead of 6%. Also compare the result with the loan’s actual rate definition and payment timing.
Does a negative PMT mean I owe money?
No. The minus sign marks the direction of the cash flow under the signs you supplied. It does not, by itself, indicate a balance due, a loss, or an Excel error.
Does PMT include interest and fees?
PMT’s level payment calculation reflects the entered rate and loan value, along with any specified optional arguments. It does not automatically add separate lender fees, insurance, taxes, or other charges that are not represented in the inputs.
Related
- Hide Column in Tableau: Steps and Tips
- Group Rows in Smartsheet: Sheets, Reports, and Views
- Numbers Text to Columns: Split Your Data Correctly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Not Blank Function: Check Whether a Cell Has DataExcel does not have a function named NOTBLANK. To test whether A2 is not truly empty, use =NOT(ISBLANK(A2));…
- Find Month and Year From a Date in Excel with FormulasTo find a month and year from a date in Excel, enter =MONTH(A2) for the month number and…
- Excel SORT and INDEX: How to Sort Data and Return ValuesTo sort a range in Excel for Microsoft 365, use =SORT(A2:C6,3,-1) to sort its rows by the third…
- Excel Formula for Current Date: TODAY ExplainedIn Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell to display the current date.…
- Excel SEQUENCE: Create Automatic Number SequencesYes. In Microsoft Excel for Microsoft 365, enter a SEQUENCE formula to generate a series automatically: with an…
- How to Hide the Formula Bar in Excel on Windows and MacTo hide the formula bar in Excel for Microsoft 365, open the View tab, then in the Show…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.