NPV function in Excel

The net present value of a project is the sum of every future cash flow discounted back to today, minus what you put in. NPV(rate, value1, value2, …) does the discounting. It also carries the assumption that has cost analysts more money than any other single default in the application:

Excel's NPV assumes value1 occurs at the end of period 1 (one period from now) not today.

How does the off-by-one trap work?

So =NPV(10%, C1, C2, C3, C4) computes C1/1.1¹ + C2/1.1² + C3/1.1³ + C4/1.1⁴. Every argument is discounted, and nothing is treated as "today." That's fine for a stream of future flows. A real project, though, almost always has an initial outlay at t = 0. That one happens today and isn't discounted. Drop that outlay inside NPV as the first argument and Excel discounts it by a period it shouldn't. Worse, it shifts every later flow one period too late.

The correct pattern keeps the t = 0 outlay outside the NPV:

=initial_outlay + NPV(rate, year1, year2, …, yearN)

Here initial_outlay is negative and already at today's value. Take a project that costs ₹1,00,000 today and pays ₹30,000, ₹40,000, ₹50,000 and ₹20,000 at the end of years 1 to 4, with a discount rate of 10%.

CORRECT:  =-100000 + NPV(0.10, 30000, 40000, 50000, 20000)   →   ₹11,556.59
WRONG:    =NPV(0.10, -100000, 30000, 40000, 50000, 20000)    →   ₹10,505.99

The wrong version is the correct NPV divided by (1 + rate): 11,556.59 / 1.10 = 10,505.99. Putting the initial flow inside NPV over-discounts the entire project by one full period. Here that understates value by 10%, and it can turn a marginal "accept" into a "reject" at the worst moment. Walk through the discounting one cash flow at a time and you'll feel it. The correct t = 0 outlay is −100000/1.1⁰ = −100000, undiscounted. The wrong one is −100000/1.1¹ = −90,909. The year-1 inflow that should be 30000/1.1¹ becomes 30000/1.1², and so on down the line. Everything slides one year into the future.

How do you avoid the trap?

There are two clean fixes. The first is the pattern above: outlay outside, future flows inside. The second, and the better one for anything with real dates, is XNPV (see XNPV and XIRR). Its first cash flow is anchored to its date and isn't discounted, so a t = 0 outlay comes out right by construction. Professionals also lay cash flows out in a labelled row with the period number above each one. The t = 0 column then stands visibly apart, and the trap can't happen.

UI and keyboard. NPV and XNPV are under Formulas ▸ Financial. After building a DCF, use Formulas ▸ Evaluate Formula to step through the discounting once and confirm the t = 0 flow is undiscounted. It's a five-second check, and it catches the off-by-one before it reaches a decision memo.