Excel NPV & IRR: Investment Analysis
NPV and IRR are the core functions for evaluating investments, comparing projects, and making capital budgeting decisions.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Evaluate a $100,000 investment that returns $30,000/year for 5 years at a 10% discount rate"
=NPV(0.10, B2:B6) + B1NPV calculates the present value of cash flows in B2:B6 at 10% discount rate. Add B1 (the initial investment, entered as -100000) to get the net present value. Positive NPV means the investment is profitable.
Step-by-step breakdown
- NPV(rate, value1, value2, ...) discounts future cash flows to present value
- 0.10 is the 10% annual discount rate
- B2:B6 contains the 5 annual cash flows ($30,000 each)
- B1 is the initial investment (-$100,000) — added separately because NPV assumes first flow is at period 1
- Result: positive = profitable, negative = unprofitable at this rate
Edge cases & warnings
- NPV assumes cash flows are equally spaced — use XNPV for irregular timing
- The initial investment must be added separately (NPV starts discounting from period 1, not period 0)
- IRR may not converge if cash flows don't change sign at least once
- Multiple sign changes can produce multiple IRRs — use MIRR in that case
Examples
"Investment: -$100K, Returns: $30K/yr x 5, Rate: 10%"
NPV ≈ $13,724 (profitable)"=IRR(B1:B6)"
IRR ≈ 15.2% (above 10% threshold)"=IRR(B1:B6) vs 10% hurdle rate"
Accept: IRR > hurdle rateFrequently asked questions
What's the difference between NPV and XNPV?
NPV assumes equal periods (annual, monthly). XNPV takes specific dates for each cash flow, giving more accurate results for irregular timing.
When do I use IRR vs NPV?
NPV tells you the dollar value created. IRR tells you the percentage return. Use NPV for comparing projects of different sizes. Use IRR for comparing to a required rate of return.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.