Excel Financial Functions: PMT, NPV, IRR Explained

Excel’s financial functions answer the questions behind a loan, a savings plan or an investment: what is the monthly payment, what is a future stream of cash worth today, and how much of an asset’s value is used up each year. The main ones are PMT, FV, NPV, IRR and a family of depreciation functions (SLN, DB, DDB, SYD). They all share one convention worth learning once: money you pay out is negative, and money you receive is positive.

This guide runs a loan, a savings goal, a five-year investment and a depreciation schedule through these functions, and shows the details that catch people out, especially where NPV stops and where IRR can have more than one honest answer. Every result was produced by running the formula in Microsoft Excel.

The sign convention

Every function in this guide follows the same rule: an amount you pay is negative, an amount you receive is positive. A loan you take out is a positive amount received once and negative payments afterwards. A savings plan is negative deposits and a positive amount received at the end. Getting the sign backwards does not raise an error, it just flips the sign of the result, which is an easy mistake to miss.

PMT: the payment on a loan

PMT(rate, nper, pv, [fv], [type]) gives the fixed payment on a loan of pv, over nper periods, at rate per period. type is 0 (the default) for payments at the end of each period, or 1 for the start. A loan of 200,000, 10 percent a year, paid monthly over 3 years (36 months):

The rate and the number of periods must use the same unit. For a monthly payment on an annual rate, divide the rate by 12 and multiply the years by 12, exactly as above.

FV: reaching a savings goal

FV(rate, nper, pmt, [pv], [type]) gives the value built up by regular payments. Saving 5,000 a month for 5 years (60 months) at 8 percent a year:

NPV: what a cash flow is worth today

NPV(rate, value1, value2, ...) discounts a series of future cash flows back to today’s value, at a discount rate that reflects what the money could otherwise earn. The important detail: NPV assumes its first value arrives at the end of period 1, not today. An investment paying 60,000 a year for 5 years, discounted at 10 percent:

If the project also required paying 250,000 today (period 0) to get started, that outlay is not one of the values inside NPV, because NPV would wrongly discount it by one period. It is added or subtracted outside the function instead: =NPV(...) - 250000. With the 250,000 outlay, this project’s net present value is -22552.79384, negative, meaning it does not clear a 10 percent hurdle.

IRR: the rate that makes NPV zero

IRR(values, [guess]) finds the discount rate at which the net present value of a series of cash flows is exactly zero: the project’s own effective rate of return. For the same 250,000 outlay followed by five years of 60,000:

6.40% is below the 10 percent used above, which is consistent with the negative NPV found there: a project’s IRR being below your required rate and its NPV at that rate being negative are the same fact stated two ways.

Cash flows that change sign more than once can have more than one mathematically valid IRR. -100, 230, -132 changes sign twice (down, up, down), and asking with two different starting guesses finds two different answers, both technically correct:

When cash flows change sign only once (an outlay, then only receipts, as in every other example here), IRR has one answer and this problem does not arise.

MIRR: a more realistic version of IRR

IRR quietly assumes that money coming back out of the project is reinvested at the same rate as the project’s own return, which is optimistic. MIRR(values, finance_rate, reinvest_rate) uses two separate, more realistic rates: what it costs to borrow the money, and what you could actually earn by reinvesting the proceeds:

7.94% is a more conservative figure than the plain IRR above, and it is generally considered the more trustworthy number when comparing projects.

IPMT and PPMT: splitting one payment

Every loan payment is part interest, part principal (the amount that actually reduces what you owe). IPMT(rate, per, nper, pv) and PPMT(rate, per, nper, pv) split a single numbered payment, per, into its two pieces. On the 200,000 loan from before, the first and the last (36th) monthly payment:

This is the same pattern behind every amortising loan: the interest share shrinks and the principal share grows with every payment, even though the total payment stays the same.

CUMIPMT and CUMPRINC: totals over several payments

CUMIPMT(rate, nper, pv, start_period, end_period, type) and CUMPRINC(...) add up interest or principal over a range of payments instead of just one, which is what a “total interest in year one” question needs. Over the whole 36 months of the same loan:

EFFECT and NOMINAL: the true annual rate

A loan or a deposit quoted as “12 percent a year, compounded monthly” does not actually earn 12 percent over a year, because interest is added every month and then itself earns interest. EFFECT(nominal_rate, periods_per_year) converts the quoted (nominal) rate into the true annual rate, and NOMINAL does the reverse:

Depreciation: SLN, DB, DDB and SYD

These functions spread the cost of an asset over its useful life. An asset bought for 180,000, expected to be worth 10,000 (its salvage value) after 5 years:

SLN spreads the value evenly and is the easiest to explain to non-finance readers. The other three are “accelerated” methods that claim more depreciation in the early years, which some tax rules require or allow. Note that DDB and DB do not automatically stop exactly at the salvage value in every year; DDB in particular can be told to switch to straight-line partway through in more advanced setups, and simply summing the five DDB figures here gives {dep_total.result_of(dep_total.pairs()[0][0])}, close to but not necessarily exactly 170,000 (180,000 minus the 10,000 salvage).

XNPV and XIRR: when payments are not evenly spaced

NPV and IRR assume the cash flows are spaced exactly one period apart. When they are not, for example an outlay today, followed by receipts after 3, 8 and 12 months, XNPV(rate, values, dates) and XIRR(values, dates) use the actual dates instead:

Using the ordinary NPV here (adding the day-1 outlay outside it, exactly as before) gives a different, wrong figure (-2103.68) because it assumes evenly spaced yearly gaps that this cash flow does not actually have. Whenever payment dates are irregular, XNPV and XIRR are the correct tools, not NPV and IRR.

Common mistakes

Mistake 1: getting the sign wrong

A payment typed as positive when it should be negative flips the sign of the whole result. If an answer for a loan payment comes out positive, check the sign of the loan amount you gave.

Mistake 2: mismatched rate and period

An annual rate used directly with a monthly number of periods overstates the interest hugely. Convert both to the same unit, as in every example above.

Mistake 3: putting today’s outlay inside NPV

NPV discounts its first value by one period, so an amount that happens today should be added outside the function, not passed as the first argument.

Mistake 4: trusting a single IRR blindly

If the cash flows change sign more than once, check with a different guess argument, or use MIRR, which does not have this ambiguity.

Mistake 5: using NPV and IRR on irregular dates

Both assume even spacing. Use XNPV and XIRR whenever the gaps between cash flows are not all the same length.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A loan of 200,000 at 10 percent annual interest is repaid monthly over 3 years. What is the monthly payment, and the total interest paid?

Show solution

PMT and total interest

Formula	Excel shows
=ROUND(PMT(0.10/12,36,200000),2)	-6453.44
=ROUND(PMT(0.10/12,36,200000,0,1),2)	-6400.1
=ROUND(PMT(0.10/12,36,200000)*36,2)	-232323.75
=ROUND(-PMT(0.10/12,36,200000)*36-200000,2)	32323.75

The payment is negative because it is money going out; the total interest is the total of all payments minus the original loan.

2. A project needs 250,000 today and returns 60,000 a year for 5 years. Is it worth doing at a 10 percent required return?

Show solution

No. =NPV(0.1,60000,60000,60000,60000,60000)-250000 gives -22552.79384, a negative net present value, and its IRR (6.40%) is below the required 10 percent.

3. Why can a cash flow of -100, 230, -132 have two different IRR answers?

Show solution

Because it changes sign twice (negative, then positive, then negative again). IRR looks for a rate that makes the net present value zero, and with more than one sign change, more than one rate can satisfy that.

4. Why would XNPV give a different answer from NPV for the same cash flows?

Show solution

NPV assumes the payments are exactly one period apart. If the real dates are not evenly spaced, XNPV, which uses the actual dates, gives the accurate figure and NPV does not.

Frequently asked questions

Why is my PMT result negative?

PMT follows the convention that money paid out is negative. If the loan amount (pv) is entered as positive, the payment comes out negative, which is correct: it is money leaving your account.

What is the difference between NPV and IRR?

NPV converts a series of future cash flows into a single value in today’s money, at a rate you choose. IRR instead finds the rate that would make that NPV exactly zero, without you choosing a rate.

Why doesn’t NPV include today’s cash flow?

NPV assumes its first argument is one period in the future. An amount that happens today should be added or subtracted outside the NPV function, not included as its first value.

Can IRR have more than one answer?

Yes, if the cash flows change sign more than once. Try IRR with a different starting guess to see if another valid rate exists, or use MIRR, which avoids the ambiguity.

What is the difference between IRR and MIRR?

IRR assumes money released by the project is reinvested at the project’s own rate of return. MIRR lets you specify a separate, more realistic financing rate and reinvestment rate.

What is the difference between EFFECT and NOMINAL?

EFFECT converts a quoted (nominal) interest rate into the true annual rate once compounding is accounted for. NOMINAL does the reverse, finding the quoted rate that produces a given effective rate.

Which depreciation method should I use?

SLN spreads the cost evenly and is simplest to explain. DB, DDB and SYD are accelerated methods that assign more depreciation to the early years; which one applies is usually set by accounting or tax rules, not a free choice.

When should I use XNPV and XIRR instead of NPV and IRR?

Whenever the cash flows are not spaced at exactly equal intervals. XNPV and XIRR use the real calendar dates; NPV and IRR assume even, regular periods.