Why does PMT return a negative number when your loan is $250,000? Why does changing the rate from 6% to 0.06 break your calculation? Why does copying the same formula from a colleague’s sheet give you $1,498 instead of $1,498.32?
The answer isn’t ‘Excel is buggy.’ It’s that PMT doesn’t calculate payments — it calculates cash flow direction. And almost everyone ignores that.
The Myth
Most people think: “PMT = monthly loan payment.” Full stop. They plug in rate, nper, and pv — then treat the result as a positive dollar amount. They copy formulas from blogs that say ‘just use =PMT(rate,nper,pv)’ and never check signs.
This leads to three predictable failures:
• Loan amortization schedules with mismatched totals
• Budget sheets showing ‘$1,500 outflow’ but summing to +$1,500 instead of –$1,500
• Finance managers rejecting your model because ‘the net present value doesn’t reconcile’
The Reality
PMT returns the net cash flow per period, not just a number. Its sign tells you who’s paying whom. Positive = money coming in. Negative = money going out.
Here’s what actually happens inside PMT — proven with real inputs:
| Symptom | Cause | Fix |
|---|---|---|
| =PMT(0.06/12,360,250000) returns -1,498.32 | Rate entered as annual (0.06), but divided by 12 → correct. PV is positive → Excel treats this as money *received* → payment must be negative to balance. | Make PV negative: =PMT(0.06/12,360,-250000) → returns +1,498.32 |
| =PMT(6%,360,250000) returns -9,472.22 | Rate is 6% per YEAR, but PMT expects rate *per period*. Using 6% (not 6%/12) means Excel assumes 6% monthly → ~72% APR. | Always convert: B2=6% → use B2/12 in formula. Or type 0.005 directly. |
| PMT gives $0 when pv is blank or zero | Empty cell = 0. PMT(0.005,360,0) = 0. Not an error — mathematically correct. | Use =IF(ISBLANK(A1),"Enter loan amount",PMT(B2/12,C2,-A1)) |
| Result changes when you format as Currency | Formatting doesn’t change value — but hiding decimals makes $1498.32 look like $1,498, causing rounding drift downstream. | Set Number Format to Accounting with 2 decimals. Or use ROUND(PMT(...),2) if strict rounding needed. |
Why the Myth Persists
Early Excel guides (pre-2007) showed =PMT(8%/12,300,100000) and called it ‘payment’. They skipped sign conventions because most users only cared about magnitude.
YouTube tutorials still say ‘just add a minus sign if it’s negative’ — without explaining why it’s negative, or what happens if you flip the sign on FV instead.
And Microsoft’s own Function Wizard labels the ‘pv’ field as “present value” — not “present value *to the borrower*”. That tiny omission cost thousands of analysts hours debugging balance sheet mismatches.
The Right Way
Do this — in order — every time:
- Define perspective first. Are you the lender (money out → negative PMT) or borrower (money in → positive PV)?
- Convert rate to period rate. Annual rate in B2? Use B2/12 for monthly. Semi-annual? B2/2. Never skip this.
- Sign PV opposite to your cash flow. Borrower receives $250,000 → PV = -250000. Lender disburses $250,000 → PV = +250000.
- Set FV only if balloon or residual matters. Most loans end at zero. So omit FV or set to 0. Don’t leave it blank — Excel treats blank as 0, but it’s safer to be explicit.
Try it now. In A1:A5, enter:
A1: 6% (annual rate)
A2: 360 (months)
A3: 250000 (loan amount)
A4: 0 (future value)
A5: =PMT(A1/12,A2,-A3,A4)
Result: 1,498.32
Now press Alt + H + H to open the Format Cells dialog. Choose Accounting → 2 decimals. No more guessing.
Counterintuitive tip: If you’re building a dashboard for executives, wrap PMT in ABS(). But only after you’ve validated sign logic. Otherwise you mask errors.
Proof It Works
Below is a 5-row slice from a full 360-month amortization starting with the correct PMT. We used =PMT(A1/12,A2,-A3,0) in cell D2, then built columns for beginning balance, interest, principal, ending balance.
| Month | Beg Bal | Interest | Principal | End Bal |
|---|---|---|---|---|
| 1 | $250,000.00 | $1,250.00 | $248.32 | $249,751.68 |
| 2 | $249,751.68 | $1,248.76 | $249.56 | $249,502.12 |
| 3 | $249,502.12 | $1,247.51 | $250.81 | $249,251.31 |
| 4 | $249,251.31 | $1,246.26 | $252.06 | $249,000.25 |
| 360 | $1,497.42 | $7.49 | $1,490.83 | $0.00 |
Sum of all Principal (C2:C361) = $250,000.00. Sum of all Interest = $289,395.20. Total paid = $539,395.20. Matches PMT × 360 = 1,498.32 × 360 = $539,395.20. No rounding drift. No hidden sign bugs.
Exceptions
The ‘myth’ works — but only in narrow cases:
- You’re doing quick napkin math and only need magnitude (e.g., quoting a client: “about $1,500/month”).
- Your entire model uses absolute values only — no NPV, IRR, or cash flow stacking.
- You’re modeling lease payments where GAAP requires gross presentation (FASB ASC 842), and your accountants told you to ignore sign conventions.
- You’re using PMT inside SUMPRODUCT with arrays where sign cancels out — e.g., =SUMPRODUCT(PMT(B2:B10/12,C2:C10,-D2:D10),E2:E10).
In those cases, yes — just slap ABS() around it and move on. But if your output feeds into financial statements, dashboards, or investor reports? Sign integrity isn’t optional. It’s auditable.
Next step: Open your current loan model. Find the PMT formula. Check these three cells:
• Is rate divided by periods? (e.g., B2/12, not B2)
• Is PV signed opposite to your role? (borrower → negative)
• Is FV explicitly 0 or omitted? (not blank, not text)
If any is wrong — fix it now. Then test row 360’s ending balance. It must be $0.00, ±$0.02. If not, your amortization is leaking.
