How to Calculate Net Present Value in Excel (Formula, Function & Templates)
Microsoft Excel provides built-in formulas like =NPV() and =XNPV() to calculate discounted cash flows. Discover best practices and avoid the famous Excel Year 0 discounting error.
Key Takeaways
- The Excel =NPV(rate, value1, value2, ...) function assumes value1 is received at the END of Year 1.
- Do NOT include Year 0 initial investment inside the NPV() argument; add it outside: =NPV(rate, Year1:YearN) + Year0_Outflow.
- Use =XNPV() for exact irregular dates with daily compounding.
1. The Famous Excel NPV Formula Trap
The most frequent financial modeling error in Excel is putting the Year 0 initial outlay directly into the `=NPV()` function range. If you write `=NPV(10%, B1:B6)` where B1 is Year 0, Excel will discount Year 0 as if it occurred at the end of Year 1!
**The Correct Excel Formula Syntax:** ```excel =NPV(rate, B2:B6) + B1 ``` (Where B1 contains the negative Year 0 initial investment `-1000000`, and B2:B6 contain cash inflows for Years 1 through 5).
2. Calculating Both NPV and IRR in Excel
To compute both Net Present Value and Internal Rate of Return side-by-side in your spreadsheet:
1. **NPV Formula:** ```excel =NPV(B1, B3:B7) + B2 ``` *(Where B1 is the discount rate `10%`, B2 is the negative Year 0 initial investment `-1000000`, and B3:B7 are cash inflows)*
2. **IRR Formula:** ```excel =IRR(B2:B7) ``` *(Note: Unlike `=NPV()`, the `=IRR()` function **must include** the Year 0 initial outlay cell B2 so it can solve for the discount rate where NPV equals zero)*
3. **XIRR & XNPV Formulas for Irregular Dates:** ```excel =XNPV(B1, B2:B7, A2:A7) =XIRR(B2:B7, A2:A7) ```
3. Step-by-Step Manual NPV Schedule in Excel (Audit-Ready)
If building an institutional-grade DCF model for an investment committee or board presentation, construct a 5-column explicit timetable without relying on the black-box `=NPV()` formula:
| Column A (Period) | Column B (Cash Flow) | Column C (Formula) | Column D (Discount Factor) | Column E (PV) |
|---|---|---|---|---|
| Period 0 | -$100,000 | Day 0 CapEx | =1/(1+$B$1)^0 [1.0000] | -$100,000.00 |
| Period 1 | $30,000 | Year 1 Inflow | =1/(1+$B$1)^1 [0.9091] | +$27,272.73 |
| Period 2 | $35,000 | Year 2 Inflow | =1/(1+$B$1)^2 [0.8264] | +$28,925.62 |
| Period 3 | $40,000 | Year 3 Inflow | =1/(1+$B$1)^3 [0.7513] | +$30,052.59 |
| Period 4 | $45,000 | Year 4 Inflow | =1/(1+$B$1)^4 [0.6830] | +$30,735.61 |
| Summary Cell | Total NPV | =SUM(E2:E6) | Net Present Value | +$46,986.55 |
Calculate Net Present Value Live
Run your cash flows through our free, real-time Net Present Value calculator.