How to calculate NPV in Excel (and the year-0 mistake)

To calculate NPV in Excel, use =NPV(rate, year1:yearN) + year0. Excel’s NPV assumes every value arrives at the end of a period, so it discounts even the first one. Your initial investment happens today, so leave it out of the function and add it afterwards. At 10%, with −10,000 today and 3,000, 4,000, 4,000 and 3,000 over the next four years, the NPV is 1,087.36. Putting the −10,000 inside NPV gives 988.51, which is wrong.

The NPV formula in Excel

Argument	Required	What it means
rate	Yes	The discount rate for one period, e.g. 10% a year for yearly cash flows.
value1	Yes	The first cash flow, assumed to arrive at the end of period 1.
value2, …	No	Later cash flows, evenly spaced, each at the end of its period. Up to 254 in total.

NPV returns the present value of a series of future cash flows. It has no idea what “today” is: period 1 is simply the first value it sees. Money going out is negative, money coming in is positive. Inside a range, empty cells, text and TRUE/FALSE are ignored.

Worked example: a 4-year project at 10%

A project costs 10,000 today and returns 3,000, 4,000, 4,000 and 3,000 at the end of each of the next four years. Columns C and D are a check by hand: each cash flow times its discount factor, 1 ÷ (1 + rate) to the power of its year.

B10fx=NPV(B1,B5:B8)+B4

Row	A	B	C	D
1	Discount rate	10%
2
3	Year	Cash flow	1/(1+r)^t	Present value
4	0	-10,000	1.0000	-10,000.00
5	1	3,000	0.9091	2,727.27
6	2	4,000	0.8264	3,305.79
7	3	4,000	0.7513	3,005.26
8	4	3,000	0.6830	2,049.04
9
10	NPV	1,087.36		1,087.36

The sum of column D matches the NPV result, which shows year 0 wasn’t discounted. A positive NPV means the project earns more than 10% a year on the money put in.

Practise it: calculate the NPV yourself

Same idea, new numbers, so you can’t copy the answer above. We check the number your formula returns, then change the rate and the cash flows to make sure it still holds.

Your task: In B10, return the project's NPV at the rate in B1.

Graded live

B10fx

Row	A	B
1	Discount rate	8%
2
3	Year	Cash flow
4	0	-25,000.00
5	1	6,000.00
6	2	9,000.00
7	3	9,000.00
8	4	7,000.00
9
10	NPV

Write the formula in the bar above, then press Check or Enter.

Hint

Put only years 1 to 4 (B5:B8) inside NPV, with the rate from B1, then add year 0 from B4.

Solution

=NPV(B1,B5:B8)+B4 Any formula that returns the same result passes.

Check

The year-0 mistake, explained

Write =NPV(10%, B4:B8) and Excel treats the −10,000 as if it arrived at the end of year 1, and every later cash flow one year too late. The whole answer is divided by 1.10 one extra time: 1,087.36 ÷ 1.10 = 988.51. The gap grows with the rate and the size of the project, and it’s the most common NPV error in models and interview tests.

Other common NPV mistakes

1. A yearly rate with monthly cash flows. Convert it. The effective monthly rate for 10% a year is =(1+10%)^(1/12)-1, about 0.797%. If your 10% is a nominal rate compounded monthly, use 10%/12, about 0.833%.
2. Blank cells for years with no cash. NPV skips blanks, so every later flow moves one period earlier and the result comes out too high. Type 0.
3. Uneven timing. NPV assumes equal periods. If cash lands on real dates, use XNPV.
4. Wrong signs. Investments and costs are negative, money coming in is positive. If every value is positive, you’ve measured value, not NPV.
5. PV instead of NPV. PV is for the same payment every period, like a loan. Uneven cash flows need NPV.

NPV vs XNPV: which should you use?

	NPV	XNPV
Syntax	=NPV(rate, value1, …)	=XNPV(rate, values, dates)
Timing	Equal periods, end of each	The actual dates you give it
First value	Discounted one full period	Not discounted: it’s the start date
Year-0 investment	Add it outside the function	Include it inside the range
Rate	Per period	Yearly

With the cash on 1 January 2026 and then 1 January 2027 to 2030, =XNPV(B1, B4:B8, A4:A8) (dates in column A) gives 1,086.04, slightly less than NPV’s 1,087.36 because XNPV counts real days on a 365-day year and 2028 has 366.

NPV and IRR: how they connect

IRR is the discount rate at which NPV equals zero. For these cash flows, =IRR(B4:B8) returns 14.90%. Note that IRR does take year 0 inside the range. Because 14.90% is above our 10% rate, the NPV is positive. At any rate above 14.90% it would turn negative.

To try your own cash flows, the NPV and IRR calculator gives the NPV and the IRR with the Excel formulas for both.