NPV and IRR calculator
Enter a project's cash flows and the return you require. Get its net present value and internal rate of return, and the Excel formulas that give the same answers.
Your cash flows
- Net present value (NPV)
- 1,087.36
- Internal rate of return (IRR)
- 14.90%
At 10.00%, the money coming in is worth 1,087.36 more today than the money going out.
The rate at which the NPV is exactly zero: the return the project earns each year.
The formulas in Excel
Type the cash flows down column C, today's first: C1 to C5. Then:
=NPV(10%, C2:C5)+C1 → 1,087.36=IRR(C1:C5) → 14.90%A new cell may show the IRR rounded, as 15% for 14.90%, until you give it more decimal places.
The classic NPV mistake
Excel's NPV treats the first value it gets as one year away. Put today's cash flow inside the range and it gets discounted too:
=NPV(10%, C1:C5) → 988.51That gives 988.51 instead of 1,087.36: the right answer divided by (1 + 10%), as if today's -10,000.00 came a year from now.
Learn more: the NPV function and how to calculate NPV in Excel. Practise it: Is the project worth it? NPV and The return on a project: IRR.
On this page
What NPV tells you
Money you get later is worth less than the same money today, because today's money could be earning a return in the meantime. NPV, net present value, brings every future cash flow back to what it is worth today at your discount rate, then adds them up with today's.
For a project that pays out first and earns later:
- Positive NPV: the project earns more than your discount rate. It adds that much value, in today's money.
- Negative NPV: it earns less than your rate. It may still make money, just not enough to beat the return you require.
- Zero: it earns exactly your rate.
The discount rate is the return you could get elsewhere at the same risk, often the cost of capital. Use a rate per period that matches the cash flows: a yearly rate for yearly flows.
The year-0 mistake
Excel's NPV assumes every value it is given arrives at the end of a period, the first one included. Today's investment doesn't: it is paid now. Put it inside the range and Excel discounts it by a year, which shrinks the whole answer by your rate. Keep year 0 outside and add it on:
=NPV(rate, C2:C6)+C1The guide How to calculate NPV in Excel works through this mistake step by step, with a sheet that grades your own formula.
What IRR tells you
IRR, the internal rate of return, is the discount rate at which the NPV is exactly zero. It answers "what return does this project earn?" For a project that pays out first, an IRR above your discount rate means a positive NPV. When money comes in first, as with a loan you take, the IRR is what the money costs you, and lower is better. IRR takes the whole range, today's cash flow included:
=IRR(values, [guess])Excel finds IRR by trial: it starts from a guess, 10% unless you give one, and improves it until the NPV is close enough to zero. That is why IRR can fail. If every cash flow goes the same way there is no answer, and if the flows change sign more than once there can be more than one. A rate far from 10% can also be missed, with #NUM!: then give IRR a guess near it, as the calculator's formula does. When there is no single answer, trust the NPV.
Working out a loan instead? The loan payment calculator gives the payment, the total interest and the full schedule, with the PMT formula.
FAQ
Why is Excel's NPV different from my calculator?
Excel's NPV treats the first value you give it as arriving one period from now. Financial calculators treat the first cash flow as today. Leave today's amount out of the range and add it after: =NPV(rate, C2:C6)+C1. Then the two match.
What does IRR tell me?
IRR is the discount rate at which the NPV is exactly zero: the yearly return the project earns on the money tied up in it. For a project that pays out first, an IRR higher than the rate you require means a positive NPV at that rate. When money comes in first, as with a loan, the IRR is what the money costs, and lower is better.
Why does IRR say there is no answer?
IRR needs at least one negative and one positive cash flow. If every flow has the same sign, no rate can bring the NPV to zero, and Excel returns #NUM!. Some flows that do change sign still never reach an NPV of zero, and Excel returns #NUM! for those too.
Can a project have more than one IRR?
Yes, when its cash flows change sign more than once, for example a clean-up cost at the end. Each change of sign can add another rate where the NPV is zero. Excel shows one of them, depending on its starting guess, and the calculator lists them all. Judge such a project by its NPV.
Should I decide with NPV or IRR?
When the two disagree, for example when you compare projects of different sizes, go with NPV. It measures the value a project adds in money, while IRR is a rate that ignores how big the project is.
What if my cash flows are monthly?
Use a monthly discount rate: the yearly rate divided by 12 for a nominal yearly rate. IRR then returns a monthly rate too. Multiply it by 12 for a nominal yearly rate.
Know NPV? Prove it on a real sheet.
Practise NPV, IRR and the year-0 rule in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install