Cumulative cash turns non-negative after 2.4118 periods, and cumulative present value after 2.7441.
Within the crossing period, cash is assumed to arrive evenly (linear interpolation).
About the NPV and IRR calculator
NPV discounts each period's cash flow back to the start and adds them up: NPV = Σ CF_t ÷ (1 + r)^t, with the first flow at period 0. IRR is the discount rate at which NPV is exactly zero. MIRR instead assumes outflows are funded at a finance rate and inflows reinvested at a reinvestment rate. Payback counts the periods until cumulative cash turns positive, and discounted payback does the same with present values.
With the defaults, an outlay of 10,000 followed by 3,000, 4,200 and 6,800, discounted at 10%, the NPV is 1,307.29 and the IRR is 16.34%. The money comes back after 2.41 periods, or 2.74 periods counting the time value of money. MIRR, at a 10% finance rate and 12% reinvestment rate, is 15.15%.
A period can be a year, a quarter or a month, as long as the rate is for the same period. Flows that change sign more than once can have several IRRs; the result then warns you and MIRR is the better guide.
How to calculate NPV and IRR
NPV discounts each cash flow to period 0 and adds them: NPV = Σ CFₜ ÷ (1 + r)ᵗ. IRR is the rate that makes NPV zero. For equally spaced flows of −10,000, 3,000, 4,200 and 6,800, NPV at 10% is 1,307.29 and IRR is 16.34%.
NPV worked by hand
Divide each flow by 1.1 raised to its period number. The outlay sits at period 0, so it is not discounted.
Period
Cash flow
Present value
0
-10000.00
-10000.00
1
3000.00
2727.27
2
4200.00
3471.07
3
6800.00
5108.94
The present values add up to 1,307.29. A positive NPV means the project returns more than the 10% hurdle: it is worth 1,307.29 more than it costs, measured at period 0. At a 0% rate the NPV would be the plain sum, 4,000.
The profitability index divides the present value of the later flows by the outlay: 11,307.29 ÷ 10,000 = 1.1307. Any value above 1 means a positive NPV.
Finding IRR by trial
IRR has no shortcut formula for a general series, so it is found by testing rates until NPV reaches zero:
At 15%, NPV is 255.61.
At 20%, NPV is −648.15, so the root lies between the two.
A straight line between those points gives 15 + 5 × 255.61 ÷ (255.61 + 648.15) = 16.41%.
Narrowing the bracket converges on 16.3406%.
IRR is a rate per period. With monthly flows it is a monthly rate, and (1 + monthly IRR)¹² − 1 converts it to a year: a 1% monthly IRR is 12.68% a year.
NPV vs IRR
For a conventional project, one outlay followed by inflows, a positive NPV and an IRR above the required rate agree. Between rival projects of different size or timing, the higher IRR can carry the lower NPV.
NPV measures the gain in money, so it decides between them. Brealey, Myers and Allen's Principles of Corporate Finance (chapter 5) compares these and other investment criteria.
MIRR with a finance rate and a reinvestment rate
MIRR replaces IRR's built-in assumption that inflows earn the IRR itself. Inflows grow to the final period at a reinvestment rate, outflows are discounted at a finance rate, and MIRR is the growth rate linking the two totals.
At a 12% reinvestment rate the inflows grow to 3,000 × 1.12² + 4,200 × 1.12 + 6,800 = 15,267.20. The only outflow is the 10,000 at period 0, so MIRR = (15,267.20 ÷ 10,000)^(1/3) − 1 = 15.15%.
After two periods, cumulative cash is −10,000 + 3,000 + 4,200 = −2,800. The third period's 6,800 covers that in 2,800 ÷ 6,800 = 0.41 of a period, so payback is 2.41 periods.
Discounted payback uses present values: −3,801.65 is unrecovered after two periods, and 5,108.94 arrives in period 3, giving 2.74 periods at 10%.
Both measures assume cash arrives evenly within a period, and both ignore everything after payback.
Multiple, missing and negative IRRs
Two IRRs. −100, 230, −132 has NPV zero at both 10% and 20%. NPV is positive between them (0.19 at 15%) and negative outside, so judge it by NPV at your required rate or by MIRR.
Negative IRR. −100 followed by 50 loses half the money in one period: IRR is −50%.
Zero IRR. −100, 50, 50 returns exactly the outlay, so IRR is 0%.
No IRR found. Rates are searched from −99.9% to 1,000% per period. −100 followed by 100,000,000 would need a far higher rate, so no IRR is reported, though NPV still is.
Matching Excel
Excel's NPV function treats its first value as arriving at the end of period 1, so =NPV(10%, -10000, 3000, 4200, 6800) returns 1,188.44, which is 1,307.29 ÷ 1.1.
IRR and MIRR need no correction. =IRR(values) gives 16.34%, because moving every flow one period later leaves the zero-NPV rate unchanged, and =MIRR(values, 10%, 12%) gives 15.15%.
Entering cash flows
Put outflows as negative amounts and list period 0 first.
Separate flows with spaces, new lines or a comma followed by a space. Inside a number, a comma groups digits: 10,000 reads as ten thousand.
Use a rate for the same period as the flows. An effective 10% a year is 1.1^(1/12) − 1 = 0.7974% a month.
The series needs at least one negative and one positive amount, up to 1,000 flows.
Checked against: script: textbook NPV, IRR by 300-step bisection, Excel MIRR definition. Microsoft's NPV example gives 1,188.44 for these flows with the first at t = 1; ×1.1 = 1,307.28
Microsoft MIRR example, five years
Cash flows, period 0 first
-120000 39000 30000 21000 37000 46000
Discount rate (per period)
10%
Finance rate for MIRR
10%
Reinvestment rate for MIRR
12%
Modified IRR
12.609413%
Internal rate of return
13.073554%
Payback period
3.81 periods
Checked against: Microsoft MIRR documentation: 12.61% (five years); exact decimal arithmetic gives 12.6094130366 and IRR 13.0735539471
Microsoft MIRR example, three years
Cash flows, period 0 first
-120000 39000 30000 21000
Discount rate (per period)
10%
Finance rate for MIRR
10%
Reinvestment rate for MIRR
12%
Modified IRR
-4.804466%
Internal rate of return
-14.405951%
Checked against: Microsoft MIRR documentation: −4.80% after three years; exact decimal arithmetic gives −4.8044655250
Microsoft IRR example, five years
Cash flows, period 0 first
-70000 12000 15000 18000 21000 26000
Discount rate (per period)
10%
Finance rate for MIRR
10%
Reinvestment rate for MIRR
12%
Internal rate of return
8.663095%
Net present value
-2,683.31
Checked against: Microsoft IRR documentation: 8.7% after five years; exact decimal arithmetic bisection gives 8.6630948037
Questions
How do you calculate NPV?
Divide each cash flow by (1 + r)^t, where t is its period, and add the results, counting the initial outlay at t = 0. At 10%, the flows −10,000, 3,000, 4,200 and 6,800 are worth −10,000 + 2,727.27 + 3,471.07 + 5,108.94 = 1,307.29. A positive NPV means the project earns more than the discount rate.
Why does Excel's NPV function give a different answer?
Excel's NPV treats the first value in its range as arriving at the end of period 1, not at period 0. Putting all four default flows into =NPV(10%, …) returns 1,188.44, which is 1,307.29 ÷ 1.1. To get the usual NPV, leave the initial outlay out of the function and add it separately: =NPV(10%, 3000, 4200, 6800) − 10000.
How is IRR calculated?
IRR is the rate that makes NPV zero, and it has to be found by trial: there is no general formula beyond four periods. For the default flows it is 16.34%. Excel's IRR starts from a 10% guess and returns #NUM! if it has not converged after 20 tries; here the search scans rates from −99.9% to 1,000% and then refines the root.
What is the difference between IRR and MIRR?
IRR implicitly assumes every inflow is reinvested at the IRR itself; MIRR uses a stated reinvestment rate and finance rate, and it always has a single answer. In Microsoft's example, −120,000 followed by 39,000, 30,000, 21,000, 37,000 and 46,000 has an IRR of 13.07% but an MIRR of 12.61% at a 10% finance rate and 12% reinvestment rate.
What is the difference between payback and discounted payback?
Payback counts plain cash; discounted payback counts present values, so it is always longer when the rate is positive. For the default flows, cumulative cash is −2,800 after two periods and the third period's 6,800 covers it in 0.41 of a period, a payback of 2.41. At 10% the discounted payback is 2.74 periods. Neither measure counts cash after the payback point.
Brealey, Myers & Allen — Principles of Corporate Finance, ch. 5 (NPV and other investment criteria)
For planning only. Lenders, tax authorities and markets apply their own rounding, fees and rules; confirm figures with them before you commit.
Checked against references
This calculator includes 6 worked examples with independently sourced answers. They run in the test suite, and you can run them here too.
Put this calculator on your site
Paste this where you want the calculator to appear. It is free to use, it stays in step with corrections made here, and the credit line below it links back so your readers can find the full version.