NPV & IRR Calculator
Free NPV & IRR Calculator: compute net present value with =NPV(rate, B2:B4)+B1 — where B1 holds the (negative) year-0 investment OUTSIDE the NPV range —...
3 one value per year
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
How it works
In Excel or Google Sheets, compute net present value with =NPV(rate, B2:B4)+B1 — where B1 holds the (negative) year-0 investment OUTSIDE the NPV range — and the internal rate of return with =IRR(B1:B4) over the full series including year 0.
Put the year-0 investment as a NEGATIVE number in the first cell.
List each following year's net cash flow below it, one cell per year.
NPV: =NPV(rate, flows-from-year-1) + investment-cell.
IRR: =IRR(entire-range-including-year-0). Compare IRR to your hurdle rate.
What this does
NPV discounts future cash flows back to today at a chosen rate and nets them against the upfront investment; a positive NPV means the project beats that rate. IRR is the discount rate at which NPV is exactly zero. The classic trap is that Excel's NPV assumes every value in its range sits one period in the future — so the year-0 investment must be added outside the function, while IRR wants the full series including year 0.
A worked example
B1 holds -10000 (investment today), B2:B4 hold 3000, 4200, 6800. =NPV(10%,B2:B4)+B1 returns €1,307.29 — the project creates value at a 10% hurdle rate. =IRR(B1:B4) returns 16.36%, the rate at which the same series breaks even. NPV and IRR are the standard go/no-go arithmetic for investments, equipment purchases, and projects — and the off-by-one-period NPV trap is probably the most common spreadsheet finance error.
Common mistakes
- Including the year-0 investment inside the
NPVrange, which discounts it one year too many. - Feeding
IRRa series with no sign change — it needs at least one negative and one positive flow. - Comparing projects of different lengths by
IRRalone;NPVat a common rate is the safer ranking. - Using annual
NPV/IRRon monthly flows — switch the rate to a monthly rate or useXNPV/XIRRwith real dates.
FAQ
Why does Excel's NPV differ from my finance textbook?
Excel's NPV discounts every range value by at least one period. Add the year-0 flow outside the function: =NPV(rate,flows)+initial.
What does #NUM! from IRR mean?
IRR could not converge — usually the series has no sign change, or you should supply a guess: =IRR(range, 0.1).
When should I use XNPV and XIRR?
When cash flows are not evenly spaced. XNPV/XIRR take actual dates alongside the values.
Built in your browser when you click. Nothing is uploaded, no signup, free for commercial use.