Valuation Updated November 2026

NPV in Excel

Net present value is the sum of discounted cash flows minus the initial investment. In Excel, =NPV(rate, value1, value2, ...) assumes the first value arrives one period from now, so add the time zero outlay outside the function: =NPV(rate, CF1:CFn) + CF0 (CF0 negative).

Syntax

=NPV(rate, CF1:CFn) + CF0

Part	What it is
`rate`	Discount rate per period (annual rate for annual cash flows)
`CF1:CFn`	Cash flows at the end of periods 1 to n, in order
`CF0`	The time zero cash flow, usually a negative investment, added outside NPV

Examples

Rate 10%, investment -1,000 at t0, then 400, 400, 400
=NPV(10%, 400, 400, 400) - 1000

Result: -5.26

PVs: 363.64 + 330.58 + 300.53 = 994.74, less 1,000.

Rate 8%, -500 at t0, then 300, 300
=NPV(8%, 300, 300) - 500

Result: 34.98

Wrong: initial outlay inside the function
=NPV(10%, -1000, 400, 400, 400)

Result: -4.78

Every value, including the -1,000, is discounted one extra period.

Common mistakes

Putting the time zero investment inside NPV, which discounts it by one period and overstates the result (for a negative outlay).
Using an annual rate on monthly cash flows: convert with rate/12 or (1 + rate)^(1/12) - 1.
Leaving blank cells in the range: NPV skips blanks and shifts later cash flows earlier, so enter 0 for empty periods.

Practice NPV in Excel

These drills check your answer on live data, free, no account needed.

NPV, IRR and profitability indexThe project costs the year 0 amount in B5 and returns cash in years 1 to 5. Find the NPV at the rate in B2 (B7, using NPV), the IRR (B8, using IRR) and the profitability index (B9: present value of the inflows divided by the initial cost).
NPV across discount ratesBuild a sensitivity row: the NPV of the cash flows in row 5 at each discount rate in row 7. Write B8 with NPV and fill right to F8.

Questions

What is the difference between NPV and PV in Excel?

PV handles a single level payment stream or lump sum; NPV handles a list of different cash flows. XNPV handles cash flows on specific dates.

What does a negative NPV mean?

The project returns less than the discount rate: it destroys value at that required return. NPV = 0 means the project earns exactly the discount rate, which is the IRR.

IRR in ExcelIRR is the discount rate at which NPV equals zero. How =IRR(values) works in Excel, exact worked examples, the guess argument, MIRR and fixing #NUM! errors.
XNPV and XIRRXNPV and XIRR discount cash flows on their real dates using a 365-day year, with the first date as time zero. Syntax, exact worked examples and common errors.
Discount factorA discount factor converts a future cash flow into present value: DF = 1 / (1 + r)^t. Worked examples, the Excel formula with absolute references, mid-year.
Payback periodPayback period = years before full recovery + unrecovered amount / cash flow in the recovery year. Simple and discounted versions, exact examples, formula.