Excel Financial Functions: PMT, NPV, IRR and the Minus Sign That Decides the Answer

A finance function will not tell you that you have used it wrongly. It takes the numbers you gave it, applies the arithmetic exactly as documented, and returns a figure to fifteen decimal places — and that figure goes into a board pack, because it looks like the sort of thing that has been checked.

Look at period 3 in the grid below. The refit cost 6,500. It went in as 6,500 rather than -6,500, and column E dutifully added it, so the year the packaging line was in pieces became the strongest year in the appraisal. Nothing broke. SUM still works. The net present value of that sheet is 14,994 and the internal rate of return is 15.5%, and both are wrong by a distance that would change the decision — the honest figures are 4,955 and 11.2%.

That is the shape of nearly every finance-function bug: not a formula that errors, but a formula that has been handed a sign, a rate, or a spacing it was never told to question.

What this covers. PMT, PV, FV, RATE, NPER, IPMT, PPMT, CUMIPMT, CUMPRINC, NPV, IRR, MIRR, XNPV, XIRR, EFFECT and NOMINAL all work in every version of Excel this century, and in Google Sheets and LibreOffice with the same arguments. LET in section 4 needs Excel 2021 or Microsoft 365; there is a plain version alongside it. Nothing here needs the Analysis ToolPak, which stopped being a separate add-in in 2007.

1) The Sign Convention Everything Else Rests On

Every one of Excel's time-value functions solves the same equation. Not a similar one — the same one:

pv*(1+rate)^nper  +  pmt*(1+rate*type)*((1+rate)^nper - 1)/rate  +  fv  =  0

You supply four of rate, nper, pmt, pv, fv, and the function returns the fifth. PMT returns the payment, RATE returns the rate, NPER returns the number of periods, and so on. There is no separate loan engine and savings engine. There is one equation, five doors into it.

Because everything has to sum to zero, the terms cannot all point the same way, and that gives you the rule:

Money coming towards you is positive. Money leaving you is negative. Always from the point of view of whoever is sitting at the sheet.

Borrow 84,000 and the loan arrives, so pv is 84000. The payments leave, so PMT comes back negative. That minus is not a quirk to be stripped off with ABS — it is the function telling you which direction the money moves. If you find yourself wrapping finance functions in ABS to make the report look tidy, you have thrown away the one piece of information that catches sign errors downstream.

Two clean ways to show a positive payment, and one bad one:

=-PMT(0.074/12, 60, 84000)      → 1679.20    negate the result, keep the inputs honest
=PMT(0.074/12, 60, -84000)      → 1679.20    state the loan as money you owe
=ABS(PMT(0.074/12, 60, 84000))  → 1679.20    same number today, silent tomorrow

The first two are fine and equivalent. The third is the one that hurts, because ABS will keep returning a cheerful positive number on the day the inputs change sign for a real reason — and that day, the sheet stops warning you.

The six arguments, in the order they appear, with the two that people forget:

Now look at the grid. Column C is the capital flow and column D the operating cash, and column E adds them. Row 5 — period 3 — has a refit of 6,500 in column C with no minus sign, so the sheet reads it as 6,500 arriving. Every figure in the rest of this article that depends on that column has two versions: the one the sheet produces, and the one you get after typing the minus.

2) PMT: The Payment That Actually Clears the Debt

The packaging line was financed rather than bought outright: 84,000 over five years at a nominal 7.4%, paid monthly in arrears.

🎯 Scenario: Find the monthly payment, and prove it clears the loan.

Put the assumptions somewhere you can point at — H1 the principal, H2 the annual rate, H3 the years — and then:

H1:  84000
H2:  0.074
H3:  5
H5:  =-PMT(H2/12, H3*12, H1)     → 1679.20

The two divisions are the whole trick. rate becomes a monthly rate and nper becomes a count of months, and they must be changed together. Divide the rate and forget the periods and you get 17,112 a month, which at least looks wrong. Multiply the periods and forget the rate — PMT(0.074, 60, 84000) — and you get 6,303, which does not look wrong at all, and is the version that reaches a board pack.

The total cost, which nobody asks for and everybody wants:

=-PMT(H2/12, H3*12, H1) * H3*12          → 100751.93    total paid
=-PMT(H2/12, H3*12, H1) * H3*12 - H1     → 16751.93     total interest

The type argument. Set it to 1 and every payment moves to the start of its period, so each one has one less period to accrue interest. On this loan that takes the payment from 1,679.20 to 1,668.91 — about 617 over the five years. It is worth the ten seconds it takes to check which one your contract says, because leases very often are payable in advance and loans very often are not.

A balloon through fv. Suppose the finance agreement leaves 15,000 outstanding at month 60. That 15,000 is money you still owe at the end, so from your point of view it is money leaving — negative:

=-PMT(H2/12, H3*12, H1, -15000)     → 1471.84

The monthly payment drops by 207, and 15,000 is due on the last day. Put 15000 in there instead of -15000 and Excel will quote you 1,887 a month, having understood that somebody is going to hand you 15,000 at the end as well.

That last row is worth knowing for its own sake: 518.00 is the first month's interest, and it is the floor below which no payment ever repays anything. Section 5 comes back to it.

3) Splitting the Payment: IPMT, PPMT and the Identity That Proves Your Schedule

The payment is constant. What it is made of is not: early payments are mostly interest, late ones mostly principal. IPMT and PPMT take the same arguments as PMT plus a per argument saying which period you mean, inserted second.

=-IPMT(H2/12, 1,  H3*12, H1)     → 518.00      interest in month 1
=-PPMT(H2/12, 1,  H3*12, H1)     → 1161.20     principal in month 1
=-IPMT(H2/12, 60, H3*12, H1)     → 10.29       interest in month 60
=-PPMT(H2/12, 60, H3*12, H1)     → 1668.91     principal in month 60

The identity. For any period, IPMT + PPMT = PMT, exactly. 518.00 + 1,161.20 = 1,679.20. 10.29 + 1,668.91 = 1,679.20. This is the cheapest self-check in finance modelling: put it in one cell next to your schedule and it will catch a mistyped per, a mismatched nper, and a rate you changed in one formula but not the other.

=SUMPRODUCT(--(ROUND(IPMT(H2/12,ROW(1:60),H3*12,H1) + PPMT(H2/12,ROW(1:60),H3*12,H1) - PMT(H2/12,H3*12,H1), 6) <> 0))

Zero means all sixty rows reconcile. Anything else names the number of rows that do not.

CUMIPMT and CUMPRINC for a range of periods. These take a start and end period, and — unlike almost everything else in this family — the type argument is required, not optional. Leave it off and you get #N/A, which is a strange way to say "you missed an argument", and is why these two get abandoned halfway through more often than any other finance function.

=-CUMIPMT(H2/12, H3*12, H1, 1,  12, 0)     → 5733.54     interest in year 1
=-CUMIPMT(H2/12, H3*12, H1, 49, 60, 0)     → 784.98      interest in year 5
=-CUMPRINC(H2/12, H3*12, H1, 1, 12, 0)     → 14416.85    principal repaid in year 1

Year one costs 5,733.54 in interest; year five costs 784.98. Same payment, seven times the interest, and that gap is the entire argument for overpaying early rather than late.

4) The Amortisation Schedule, and Why the Last Row Should Land on Zero

Four columns and one absolute reference. Put the opening balance in J2 and the period numbers 1 to 60 down I3:I62:

J2:  =H1                                    opening balance, 84000
K3:  =J2*$H$2/12                            interest this month
L3:  =$H$5-K3                               principal this month
J3:  =J2-L3                                 closing balance, carried down

Fill J3:L3 down to row 62 and the last closing balance should read 0. If it reads 0.0000000001, that is floating-point residue and is fine — wrap the display in ROUND, not the arithmetic. If it reads -0.08, that is something different and worth understanding.

The rounding drift. Real lenders bill a payment rounded to the cent. Build the schedule with ROUND(PMT(...), 2) — 1,679.20 rather than 1,679.198875 — and after sixty months you have overpaid by 8 cents. That is correct behaviour, not a bug, and it is why loan agreements specify a final payment that differs from the other 59. The habit that keeps you out of trouble: round the payment once, at the top, and let the schedule inherit it. Rounding each row's interest instead compounds a new error every month.

H5:  =ROUND(-PMT(H2/12, H3*12, H1), 2)      → 1679.20, and every row agrees with the bank

The last-row check, in one cell. Rather than reading the bottom of a 60-row block, assert it:

=IF(ABS(INDEX(J:J, 62)) < 0.5, "clears", "off by " & TEXT(INDEX(J:J,62), "0.00"))

And without a schedule at all. FV will tell you the balance after any number of payments directly, which is how you check a schedule you did not build:

=-FV(H2/12, 24, -H5, H1)     → 54062.50     what is still owed after two years

In one formula, with LET, for the version you paste into someone else's model:

=LET(r, H2/12, n, H3*12, p, ROUND(-PMT(r,n,H1),2), after, 24,
     -FV(r, after, -p, H1))

5) RATE and NPER: Solving for the Number You Do Not Have

The equation goes both ways, and this is where finance functions earn their place. A supplier does not quote you an interest rate. They quote a monthly figure.

🎯 Scenario: The dealer offers the 84,000 line at "1,680 a month for 60 months, no deposit". What rate is that?

=RATE(60, -1680, 84000)*12      → 0.0742    7.42% nominal

RATE returns the rate per period, so multiply by 12 for a nominal annual rate. Note what the quote hides: 7.42%, not the 7.40% in the paperwork, because rounding 1,679.20 up to 1,680 is worth two basis points to whoever wrote the quote.

RATE is iterative — it guesses, checks, and guesses again, up to twenty times, aiming for 0.0000001. If it cannot get there it returns #NUM!, and the fix is the optional sixth argument:

=RATE(60, -1680, 84000, 0, 0, 0.01)     the guess, as a per-period rate

Give it a per-period guess, not an annual one. Feeding 0.074 to a monthly problem is asking the solver to start twelve times too high, which is a good way to manufacture the #NUM! you were trying to avoid.

NPER: how long at a payment you can actually afford.

=NPER(H2/12, -1500, H1)     → 68.91    months at 1500
=NPER(H2/12, -1400, H1)     → 75.16    months at 1400
=NPER(H2/12, -500,  H1)     → #NUM!

NPER returns a fraction, and 68.91 means 68 full payments and a smaller 69th. The #NUM! on the last line is the most informative error in this article: 500 a month is less than the first month's interest of 518.00, so the balance grows every month and no number of periods ever clears it. Excel is not being difficult. It is telling you the loan is unpayable at that figure.

EFFECT and NOMINAL: comparing quotes that are not comparable. A nominal rate ignores compounding within the year, which is why two lenders quoting the same number can charge different amounts.

=EFFECT(0.074, 12)      → 0.076562    7.4% nominal, compounded monthly, is 7.66% effective
=NOMINAL(0.0766, 12)    → 0.074035    and back again

Compare effective rates when the compounding frequencies differ; compare nominal rates only when they do not. A 7.5% annual-compounding offer beats a 7.4% monthly-compounding one, and the only way to see that is to convert.

6) NPV: The Function That Is Not Net Present Value

Here is the sentence that costs more money than any other in this article:

Excel's NPV discounts its first value by one full period.

NPV(rate, value1, value2, ...) treats value1 as arriving at the end of period one, not today. So if your range starts with a day-one outlay — as almost every appraisal does — NPV moves that outlay a year into the future and discounts it. The function is a present-value-of-a-future-stream calculator. The "N" is a promise it does not keep.

🎯 Scenario: The packaging line's net cash flows sit in E2:E7, with the 84,000 outlay on day one in E2. The discount rate is 9%.

=NPV(0.09, E2:E7)          the wrong one
=E2 + NPV(0.09, E3:E7)     the right one — day-one flow added outside, later flows inside

Run both on the sheet as handed over, and then again with the refit's minus sign restored:

Two independent errors, and they are different animals. The wrong NPV shape divides the whole answer by (1+rate) — 4,955 ÷ 1.09 = 4,546 — because every flow, including the one at time zero, has been pushed out one period. It is a consistent, proportional understatement, which is exactly why it survives review: the number still moves in the right direction when you change an assumption, so the sheet feels alive.

The sign error is not proportional. It adds a phantom 13,000 of inflow — 10,038 of it in today's money, twice the project's real NPV — and it changes the decision.

Two more things NPV will do quietly. It ignores empty cells and text, so a gap year in your range does not become a zero — it disappears, and every later flow slides one period closer to today. If a year genuinely has no cash flow, type 0. And it takes the order of the range as the order of the periods, so a range sorted by anything other than time is nonsense delivered to fifteen decimals.

The version that reads like what it means:

=LET(cf, E2:E7, r, 0.09,
     INDEX(cf,1) + NPV(r, DROP(cf,1)))

Or, without LET and DROP, the formula that needs no special-casing at all — discount each flow by its own period number and add them up:

=SUMPRODUCT(E2:E7 / (1+0.09)^A2:A7)     → 4955.12

Column A already holds the period numbers, starting at 0. This version cannot be got wrong the way NPV can, because the exponent is stated rather than assumed.

7) IRR: What It Solves, and the Three Ways It Lies

IRR finds the rate at which the NPV of a set of cash flows is zero. Unlike NPV, it does treat the first value as time zero — which is a genuine inconsistency between two functions that sit next to each other in the same menu, and is worth saying out loud because half the errors in this area come from expecting them to behave alike.

=IRR(E2:E7)     → 15.5% on the sheet as typed
=IRR(E2:E7)     → 11.2% once the refit is a minus

Against a cost of capital somewhere near 9%, one of those is a comfortable yes and the other is a conversation. Same formula, same range, one keystroke apart.

Lie one: it assumes your periods are equally spaced. IRR has no idea what is in column B. It counts rows. Period 5 in the grid is dated 15 March 2031, four and a half years after day one rather than five, because the line was sold early — and IRR prices that 21,250 as though it arrived on the fifth anniversary. Money arriving early is worth more, so the true annualised return is higher than IRR reports. Section 8 gets the real figure.

Lie two: it can have more than one answer, and returns whichever it happened to find. A cash-flow series has as many possible IRRs as it has sign changes. The grid, corrected, goes negative once and then stays positive: one sign change, one IRR, no ambiguity. But suppose the refit had been 30,000 rather than 6,500 — period 3 would go negative, the signs would read −, +, +, −, +, + and there would be three sign changes and up to three mathematically valid IRRs. Excel returns the first one its solver lands on, and moving the optional guess argument moves the answer:

=IRR(E2:E7, 0.1)      start looking near 10%
=IRR(E2:E7, 0.5)      start looking near 50% — on a multi-root series, a different answer

If two IRR calls on the same range with different guesses disagree, that is not a bug. That is the series telling you IRR is the wrong tool for it.

Lie three: it assumes you reinvest every inflow at the IRR itself. An 11.2% IRR quietly assumes the 21,500 that arrives in 2027 goes straight back to work at 11.2% for the remaining four years. If your business actually parks spare cash at 5%, the project's real return is lower than the headline. MIRR states both rates instead of assuming one:

=MIRR(E2:E7, 0.074, 0.05)     → 8.5%
     values, finance_rate (what borrowing costs), reinvest_rate (what cash actually earns)

11.2% against 8.5% is the size of the assumption you were making without saying so. MIRR is not a more accurate IRR; it is a more honest one, because it makes you write the reinvestment rate down where a reviewer can argue with it.

8) XNPV and XIRR: When the Dates Are Real

XNPV and XIRR take a second array — the dates — and discount by actual days elapsed on a 365-day year. The first date in the array is the reference date, and it is not discounted at all, which is precisely the behaviour NPV refuses to give you.

🎯 Scenario: Value the corrected packaging line using the dates in column B rather than the row numbers.

=XNPV(0.09, E2:E7, B2:B7)     → 5504.26
=XIRR(E2:E7, B2:B7)           → 11.5%

Compare with 4,955.12 and 11.2% from the equal-spacing versions. The gap is not rounding — it is the early sale. XIRR knows the final 21,250 arrived on 15 March rather than 1 September and credits it accordingly. It also knows that 2028 was a leap year and the second period was 366 days.

Three things that will bite:

• The dates must be real dates, not text that looks like dates. If B2:B7 is left-aligned by default, it is text, and you will get #VALUE!. Section 10 covers the fix.
• There must be at least one negative and one positive value, or XIRR returns #NUM!. The same is true of IRR.
• XIRR returns an annual effective rate, while IRR returns a rate per period. On annual data they are comparable; on monthly data they are emphatically not — a monthly IRR of 0.9% is not 0.9% a year.

When to use which, which is simpler than it sounds:

That last row is the practical advice. If your sheet has a date column, there is no reason to use NPV and IRR at all. The date-aware pair gives the same answer on regular data and the right answer on irregular data, and it cannot be broken by someone inserting a row.

9) PV and FV: The Buy-Now-Or-Pay-Yearly Question

The same equation, entered through two more doors.

🎯 Scenario: The maintenance contract on the line is 4,200 a year for five years, or 18,500 paid up front today. Money costs 9%. Which is cheaper?

=-PV(0.09, 5, -4200)     → 16336.54

Five annual payments of 4,200 are worth 16,336.54 in today's money, so the up-front offer at 18,500 costs 2,163 more. Pay yearly. Change the discount rate to 3% and the annual stream is worth 19,235 — now the up-front deal wins. The answer is a function of what money costs you, which is the point of the whole exercise, and it is why a "discount" quoted as a lump sum should always be run through PV before anyone signs it.

If the contract were payable in advance, the first payment leaves today and is not discounted at all:

=-PV(0.09, 5, -4200, 0, 1)     → 17806.82

FV for the other direction — building up rather than paying down. Setting aside 600 a month for the replacement machine, on top of a 5,000 opening reserve, at 4.2%:

=FV(0.042/12, 72, -600, -5000)     → 55463.88

Both the contributions and the opening balance are negative, because both are money you are handing over to the account. Get one of the two signs wrong and Excel will subtract your opening balance from your savings and report a plausible, smaller number.

10) The Errors, and What Each One Actually Means

Finance functions have a small vocabulary of complaints, and each one has a specific cause.

Finding text dates, which cause more #VALUE! than anything else in this family:

=SUMPRODUCT(--ISTEXT(B2:B7))     → 0 means they are all real dates

If it returns anything else, select the column and run Data → Text to Columns → Finish. That one-step no-op re-parses every cell and converts the ones Excel can read — faster than any formula, and it fixes the underlying values rather than working around them.

And a whole-column sanity check on signs, which would have caught the refit:

=SUMPRODUCT(--(C2:C7>0), --(D2:D7>0))     → counts rows claiming capital AND operating inflows

A capital column with a positive entry is not automatically wrong — period 5 sold the line for 9,000 and that inflow is real. It is worth a glance every time, though, and a glance is all this formula costs.

11) A Checklist Before the Model Leaves Your Hands

1. State the assumptions in labelled cells, not inside formulas. Rate, term, principal, discount rate, one cell each.
2. Check every rate against its period. If nper counts months, rate is divided by 12. Read the two arguments together, out loud.
3. Read the signs down each cash-flow column. Outflows negative, inflows positive, no exceptions and no ABS.
4. Replace every NPV(rate, whole_range) with the first flow outside the function — or with the SUMPRODUCT form, which cannot be got wrong.
5. Prefer XNPV and XIRR wherever a date column exists. They are right on regular data too.
6. Count the sign changes in any series you are about to hand to IRR. More than one, and quote MIRR or XIRR instead.
7. Write the reinvestment assumption down if you are quoting an IRR at all. Someone will ask, and the honest answer is "the IRR itself, unless I used MIRR".
8. Round the payment once, at the top, and let the amortisation schedule inherit it.
9. Assert the last balance is zero in a cell, rather than reading it off the bottom of the schedule.
10. Put IPMT + PPMT - PMT somewhere visible. It costs one cell and it proves the whole block.

12) Mini Exercises

Copy the grid into a blank sheet starting at A1, and fix the refit's sign in C5 before you start. Every answer is one formula.

1. The honest appraisal. Write the net present value of E2:E7 at 9% in a single formula that does not use NPV at all. It should return 4955.12.
2. The premium for waiting. XNPV at 9% gives 5,504.26 against NPV's 4,955.12. Write one formula returning the difference, and say in a sentence which of the two dates in the grid is responsible.
3. The rate that breaks even. At what discount rate does the corrected project's NPV hit zero? Write it two ways — one using IRR, one using XIRR — and explain in one line why they differ.
4. The payment you can afford. You can pay 1,450 a month and nothing more. Write the formula for how many months the 84,000 loan then takes, and a second formula returning the size of the final, smaller payment.
5. The overpayment. If you paid 250 a month extra from month one, how much total interest would you save? (NPER gives you the new term; CUMIPMT needs a whole number of periods — decide what to do with the fraction and say why.)
6. Break IRR on purpose. Change C5 to -30000 so period 3 goes negative. Write IRR twice with guesses of 0.1 and 0.9, report both answers, and give the one-sentence reason they are both correct.

Summary

There is one equation under all of this, and it balances to zero. That is why the signs matter: they are not formatting, they are the direction of travel, and a refit typed as +6,500 turns a marginal project into an obvious one without changing a single formula.

Three habits carry most of the value. Read the rate and the period together every time, because an annual rate on a monthly term is the error that produces the most plausible wrong answer. Never pass a day-one outlay to NPV as the first value — put it outside, or use SUMPRODUCT and state the exponent. And if the sheet has a date column, use XNPV and XIRR and stop thinking about spacing altogether.

Finance functions are not hard. They are unforgiving, which is a different thing, and the difference is that they never tell you when you have got it wrong.