NPV Function in Excel: Net Present Value Explained

The NPV function in Excel tells you what a stream of future cash flows is worth in today's money, after taking off what you spend upfront. If the result is positive, the project beats your required return. The formula is short, but one detail trips up almost everyone: Excel's NPV assumes the first cash flow is a year away. This guide shows the right way to set it up, and how to check the answer by hand with SUM.

What is net present value?#

Net present value (NPV) is the total value of all cash flows, with each future amount reduced to what it's worth today. Money in the future is worth less than money now, because money now could be invested. The discount rate is the yearly return you could get elsewhere, and it's used to shrink future amounts.

A cash flow arriving in year n is worth Amount/(1+rate)^n today. NPV adds up those discounted amounts and subtracts the upfront cost.

The examples use this project in A1:B7, with a discount rate of 8% in E1:

Year 0 is today: the project costs 10,000 upfront. It then pays back 17,000 over five years. Is it worth it at 8%?

How to use the NPV function#

Use NPV on the future cash flows only, then add the year-0 amount outside the function.

=NPV(E1,B3:B7)+B2

The result is 3,595.37. NPV(E1,B3:B7) values years 1 to 5 at 13,595.37 in today's money. Adding B2 (-10,000) gives the net figure. A positive result means the project returns more than 8% a year.

The full syntax is:

=NPV(rate, value1, [value2], ...)

Negative values are money out, positive values are money in. Empty cells and text in the range are ignored, so don't leave a gap for a year with zero cash: type 0.

The year-0 mistake that gives the wrong NPV#

If you put the upfront cost inside NPV, Excel discounts it by a year, as if you paid it next year. That makes the result too small.

The wrong version is exactly the right answer divided by 1.08. On a big project, that gap can change the decision. Always keep year 0 outside.

How to check NPV by hand with SUM#

Discount each cash flow in its own column, then add them up. This shows every step and is a good way to prove your answer in an interview task.

In C2, type the discount formula and fill it down to C7 (Windows: Ctrl+D; Mac: ⌘D):

=B2/(1+$E$1)^A2

The $ signs lock the rate in E1 so every row uses it, while B2 and A2 move down row by row. The lesson on relative vs absolute references covers this pattern.

Then total the column with =SUM(C2:C7), which returns 3,595.37, the same as NPV. Year 0 divides by 1.08^0, which is 1, so it isn't discounted. The SUM function guide and the SUM and AutoSum lesson cover the totalling step, and the AutoSum shortcut (Windows: Alt+=; Mac: ⌘⇧T) adds the SUM for you.

You can also do it in one cell without a helper column:

=SUMPRODUCT(B2:B7/(1+E1)^A2:A7)

This divides each cash flow by its own discount factor and adds the results. The guide to SUMPRODUCT explains how it handles arrays.

How to read an NPV result#

The sign of NPV is the decision rule:

• NPV above 0: the project earns more than the discount rate. It adds value.
• NPV of 0: it earns exactly the discount rate.
• NPV below 0: it earns less than the discount rate. You'd be better off elsewhere.

The rate matters a lot. Try the same project at 20% by changing E1 to 20%: the NPV drops to about 179. The rate at which NPV is exactly 0 is the internal rate of return, which Excel calculates with IRR.

NPV vs XNPV, and other common mistakes#

NPV assumes cash flows arrive at equal intervals, such as every year or every month. When payments land on irregular dates, use XNPV, which takes a list of dates as well as amounts. Other things to watch:

1. Rate and period don't match. For monthly cash flows, use a monthly rate, such as 8%/12.
2. Rate typed as 8 instead of 8%. Excel reads 8 as 800%.
3. Missing years. A blank cell is skipped, which pulls later years one period closer. Type 0 instead.
4. Signs mixed up. Costs must be negative and income positive.

NPV fits naturally at the end of a cash flow forecast, and a running total of the discounted column shows the year in which the project pays back. For more on totals, see the guide to the SUM function.

Practise it#

NPV questions in finance tests check two things: whether you lock the rate correctly and whether you keep year 0 outside the function. Practise relative and absolute references in a live spreadsheet: you type the formula and it checks it instantly. Then practise SUM and AutoSum to build the hand check, and try the finance Excel test when you're ready.