IRR and NPV Calculator

How to use this tool

Pick what to calculate. "NPV and IRR from cash flows" takes the money in and out period by period, with period 0 first. "Even flows with a salvage value" covers a level payment and an optional resale value. The NPV profile plots NPV against the rate, "compare two projects" gives the crossover rate, and the spreadsheet check shows what Excel returns if the outlay goes inside NPV().

Enter money you spend as a negative number and money you receive as a positive one, with the rate per period. If each period is a month, use a monthly rate. The input people most often get wrong is the outlay: typing 100000 instead of −100000 leaves no sign change, so the tool reports that there is no IRR. Empty boxes at the end are ignored, and an empty box in the middle counts as zero.

What the calculation is doing

Net present value shrinks each cash flow for the time it takes to arrive. A flow in period t is divided by (1 + r) to the power t, where r is the discount rate per period. Period 0 is today, so the outlay is not discounted: NPV = CF0 + CF1 ÷ (1 + r) + CF2 ÷ (1 + r) to the power 2, and so on.

The discount rate is the return you give up by using the money here instead of the next best place at similar risk. A positive NPV means the project beats it.

The internal rate of return is the rate that makes the NPV exactly zero. It has no formula, so the calculator scans rates from −100% upward for changes of sign, narrows each bracket by bisection and finishes with a Newton step. Putting the answer back into the NPV formula returns zero to within rounding.

Payback is the time until the running total reaches zero, interpolated inside the period where it crosses. Discounted payback does the same after discounting. The profitability index is the present value of inflows divided by the present value of outflows. MIRR compounds inflows forward at a reinvestment rate and outflows back at a finance rate: MIRR = (FV of inflows ÷ PV of outflows) to the power 1 ÷ n, minus 1.

For two projects the tool subtracts one list from the other. The IRR of the difference is the crossover rate, where both projects have the same NPV.

When IRR misleads

Cash flows that change sign more than once can have more than one IRR. A $100,000 outlay, $230,000 of income and a $132,000 clean up bill has IRRs of exactly 10% and 20%, and is worth doing only between them. By Descartes' rule of signs the number of IRRs cannot exceed the number of sign changes. Flows of −1,000, +6,000, −11,000 and +6,000 change sign three times and have IRRs of 0%, 100% and 200%. Flows that never change sign have none.

IRR also ignores size. A $10,000 project returning $6,000 a year for three years has an IRR of 36.31% and an NPV of $4,921 at 10%. A $100,000 project returning $50,000 a year has a lower IRR, 23.38%, but an NPV of $24,343, five times the value.

The NPV curves of those two projects cross at 21.9%. Below it the larger project wins on NPV and the smaller wins on IRR, and above it the two methods agree. The calculator finds it from the difference of the flows and states where the rankings disagree. When they do, follow NPV.

IRR assumes each inflow is reinvested at the IRR itself. At 13.75% that is plausible, and at 36% it rarely is. Reinvesting the default project's inflows at 10% gives a MIRR of 12.08%, below the 13.75% IRR. Neither is wrong, but MIRR is the cautious one when the IRR is high.

Payback ignores everything after the cutoff. The default project pays back in 3.33 years, and in 4.37 once discounted, but neither figure says whether it creates value. Treat payback as a measure of how long the money is at risk and use NPV to judge value.

The spreadsheet NPV trap

Excel and LibreOffice have an NPV() function that assumes the first value arrives one period from now. Microsoft says a first cash flow at the start of the first period must be added to the NPV result, not included in the values, and its VBA reference says the investment begins one period before the date of the first value. The LibreOffice help likewise takes the time zero cash flow outside the function.

Put the whole list inside NPV() and the outlay is discounted by a period it never had, so the result is the true NPV divided by 1 + r. For the default project at 10%, =NPV(10%, −100000, 25000, 30000, 35000, 30000, 25000) returns $8,936.47 against a true $9,830.12. Microsoft's first example is the same case: it labels its −$10,000 as a cost one year from today, and NPV() returns $1,188.44. With the outlay at today the NPV is $1,307.29, which is $1,188.44 × 1.1.

The correct form is =NPV(rate, flows 1 to n) + flow 0, as in Microsoft's second example. The spreadsheet check in the tool prints both forms for your own list. IRR() is not affected, because multiplying every term by 1 + r does not move the rate at which the sum is zero.

IRR of equal yearly flows

The IRR of an outlay of 100 recovered by level yearly flows. Find your flow as a share of the outlay, read across for the length, and compare it with your required rate.

A yearly flow of 20% of the outlay for five years returns the money and nothing more, an IRR of exactly 0%. Stretching a 30% flow from three years to ten lifts the IRR from −5.09% to 27.32%, so length matters as much as size.

A worked example you can check on paper

The default project costs $100,000 now and returns $25,000, $30,000, $35,000, $30,000 and $25,000 at the ends of years 1 to 5, discounted at 10%. Each flow is multiplied by 1 ÷ 1.1 to the power of its year. Year 1: 25,000 × 0.909091 = $22,727.27. Year 2: 30,000 × 0.826446 = $24,793.39. Year 3: 35,000 × 0.751315 = $26,296.02. Year 4: 30,000 × 0.683013 = $20,490.40. Year 5: 25,000 × 0.620921 = $15,523.03. They add to $109,830.12, so NPV = 109,830.12 − 100,000 = $9,830.12.

For the IRR, try 13% and 14%. The NPV is $1,843.61 at 13% and −$615.52 at 14%, so it reaches zero about 0.75 of the way between them, near 13.75%. The exact answer is 13.75%.

The running totals of cash are −75,000, −45,000, −10,000 and +20,000, so payback is 3 + 10,000 ÷ 30,000 = 3.33 years. The discounted running totals are −$77,272.73, −$52,479.34, −$26,183.32, −$5,692.92 and +$9,830.12, so discounted payback is 4 + 5,692.92 ÷ 15,523.03 = 4.37 years. The profitability index is 109,830.12 ÷ 100,000 = 1.098. For MIRR, the inflows grow at 10% to $176,882.50 by year 5, and (176,882.50 ÷ 100,000) to the power 1 ÷ 5, minus 1, gives 12.08%.

Mistakes that catch people out

Entering the outlay as a positive number. Money you spend must be negative. With every flow positive there is no sign change, no IRR, and an NPV that ignores the cost.

Mixing period lengths. Monthly flows need a monthly rate, and 12% a year is about 0.949% a month, not 1%. Set the period to months and enter the monthly rate.

Putting the outlay inside a spreadsheet NPV(). It scales the answer down by the factor 1 + r. Add flow 0 outside the function.

Trusting one IRR when the flows change sign twice. A late cost such as decommissioning can create two IRRs or none. Check the sign changes and rely on NPV.

Ranking exclusive projects by IRR or payback. Both ignore the value a larger or longer project adds. Compare NPV at one discount rate and check the crossover.

Popular calculations

Each opens the calculator with those flows and rates filled in, and they can be changed once it is open.

Even flows and salvage value

Cash flow lists

Two IRRs, three IRRs and no IRR

MIRR and payback

NPV profile

Compare two projects

Spreadsheet NPV timing

How this was checked

The engine was compared with exact rational arithmetic on 3,000 random cash flow lists, and NPV agreed to about 2 parts in 10 trillion of the total size of the flows. IRR was found by plain bisection and by Newton's method from a 10% guess, where it converged, on 4,000 random normal projects, and both agreed with the calculator to rounding. An exact Sturm sequence count of the roots matched the tool on all 500 random lists tried, with up to eight flows. MIRR matched its closed form on 2,843 lists, payback matched a brute force interpolation, and crossover rates matched bisection on the difference of the two NPVs.

Published examples were reproduced. Microsoft Support's NPV page gives $1,188.44 for its first example, $1,922.06 for its second and −$3,749.47 with a $9,000 loss in year 6. Its IRR page gives −2.1%, 8.7% and −44.4%, and its MIRR page gives 13%, −5% and 13% where the tool gives 12.61%, −4.80% and 13.48%, which round to the same figures. LibreOffice help gives 94.16% for its MIRR example. OpenStax Principles of Finance gives an NPV of $2,835.63, IRR of 14.09%, profitability index of 1.18, discounted payback of 5.05 years and MIRR of 12% for its embroidery machine. The tool shows $2,835.62 for the NPV because the book sums present values already rounded to cents. A Touro College corporate finance solution gives a crossover of 14.53% and Corporate Finance Institute gives 76%, and the tool matches both.

Every address in the lists above was then opened and checked for a distinct title, the right answer and a chart that renders.

Common questions

What does NPV tell me?

It is the value a project adds, in today's money, after paying your required return. Each flow is divided by (1 + rate) to the power of its period, and period 0 is left alone. Positive means the project beats your rate and negative means it falls short. The default project has an NPV of $9,830.12 at 10%.

What discount rate should I use?

Use the return you would earn on the next best use of the money at similar risk, such as a firm's hurdle rate or the rate on a loan you could repay. The verdict moves with the rate, so check the NPV profile. The default project stays positive at any rate below 13.75%.

Why can IRR give two answers, or none?

The IRR solves an equation that can have as many roots as the flows have sign changes. One outflow followed by inflows gives one IRR. A late clean up cost can give two, as with −$100,000, +$230,000 and −$132,000, which has IRRs of 10% and 20%. Flows that never change sign give none. In those cases rely on NPV.

Why is my spreadsheet NPV different from this calculator?

Probably the outlay went inside NPV(). Excel and LibreOffice discount the first value by one period, so the result is the true NPV divided by 1 + rate: $8,936.47 instead of $9,830.12 for the default project. Use =NPV(rate, flows 1 to n) + flow 0, or open the spreadsheet check here.

When do NPV and IRR disagree about which project is better?

When projects differ in size or timing. A $10,000 project with an IRR of 36.31% adds $4,921 at 10%, while a $100,000 project with an IRR of 23.38% adds $24,343. Below the crossover of 21.9% the larger wins on NPV and the smaller on IRR. For projects you cannot combine, follow NPV at your own rate.

What is MIRR and when should I use it?

The modified IRR compounds every inflow forward to the last period at a reinvestment rate, discounts every outflow to period 0 at a finance rate, and finds the single rate linking the two. It drops the assumption that cash is reinvested at the IRR and always gives one answer. Microsoft's example flows give 12.61% at 10% and 12%.

Related Calculators and Reference

• Present Value Calculator: the discounting step behind NPV.
• CAGR Calculator: the growth rate between two values.
• ROI Calculator: the simple return on an investment, before the time value of money.
• Inflation Calculator: for a real against a nominal discount rate.
• Refinance Calculator: costs now and savings later, so it suits an NPV test.
• Cap Rate Calculator: turn a cap rate and NOI into a property value before running cash flows.
• Mortgage Points Calculator: the return on the cost of mortgage points, set out as a net present value and a rate of return.
• OpenStax: Internal Rate of Return (IRR) Method: the textbook treatment of the IRR.