Evaluating Projects With NPV

Businesses constantly ask: is this project worth doing? An investment costs money up front but pays back over years. NPV (Net Present Value) helps decide.

NPV discounts every future cash flow back to today's value and adds them up. If the total is positive, the project earns more than your required return; if negative, it falls short.

It is the cornerstone of capital budgeting.

The function is =NPV(rate, value1, [value2], ...).

• rate — the discount rate per period (your required return or cost of capital).
• value1, value2, ... — the future cash flows, one per period.

You can also pass a single range, like =NPV(rate, B2:B6), which is far more common in practice.

Here is the single most important thing about NPV: it assumes every value occurs at the END of a period, starting one period from now.

That means the first value you give it is discounted by one period, not treated as today's money.

So an initial investment made today should NOT go inside the NPV range — it is already in present-value terms.

Because the up-front cost happens at time zero, you add it outside the NPV function.

The pattern is: discount the future inflows with NPV, then add the (negative) initial investment separately.

This is the correct, textbook way to build an NPV in a spreadsheet.

You invest 1,000 today and expect to receive 300, 400, 500, and 600 at the end of the next four years. Your required return is 10%.

The initial -1,000 sits outside NPV. The four inflows go inside.

The formula returns about 402.92. Because it is positive, the project beats a 10% return and is worth doing.

In a real model the cash flows live in cells. Say B2:B5 holds the four future inflows and B1 holds the initial outlay (as a negative).

Reference the range inside NPV and add the outlay cell after.

This keeps the formula short and lets you change any flow and see the result update.

Interpreting NPV is simple.

• NPV > 0 — the project earns more than the discount rate; accept it.
• NPV = 0 — it exactly meets your required return; you are indifferent.
• NPV < 0 — it earns less than required; reject it.

A higher NPV means a more valuable project, all else equal.

NPV is sensitive to the discount rate. A higher rate punishes distant cash flows more, lowering the NPV.

Try the same project at 20% instead of 10% and the NPV shrinks — possibly turning negative.

Choosing the right rate (your cost of capital) is the hardest and most important judgment in the analysis.

Plain NPV assumes cash flows are evenly spaced — one per period, exactly one period apart.

If your dates are irregular, use XNPV instead, which takes an actual list of dates: =XNPV(rate, values, dates).

XNPV also treats the first value as occurring on its given date, so you can include the time-zero outflow inside it.

NPV, XNPV, and the soon-to-come IRR all exist in Google Sheets with identical syntax.

The same end-of-period assumption applies, and the initial-outlay-outside trick is still the right approach.

So a discounted cash-flow model built in Excel transfers cleanly to Sheets. Just remember: NPV discounts the first value by one full period.

NPV shines when ranking competing projects. Compute the NPV of each and the higher one creates more value.

Suppose Project A's flows are in B2:B5 and Project B's in C2:C5, both costing 1,000 up front at a 10% rate. Calculate both NPVs side by side.

Whichever cell shows the larger positive NPV is the better use of your money — assuming similar risk.

Test the most common NPV pitfall.

You learned to value projects with =NPV(rate, values).

• NPV discounts future cash flows to today and assumes each occurs at the end of a period.
• Keep the initial outlay outside NPV and add it separately.
• Positive NPV means accept; negative means reject.
• Higher discount rates lower NPV; use XNPV for irregular dates.

The same rules apply in Excel and Google Sheets.