NPV

Discount a series of future cash flows to today's value at a given rate. The initial investment belongs outside the function.

Syntax

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

Argument	Required	Description
`rate`	Required	The discount rate per period.
`value1`	Required	Cash flows, one per period, in order. Values or a range.
`value2`	Optional	Further cash flows.

Examples

Every example below was executed against VisiGrid engine 0.54.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=NPV(0.1,B3:B5)+B2`	`-21.04`	The right way — discount the future flows, then add the outlay made today.
`=NPV(0.1,B2:B5)`	`-19.12`	The common mistake. NPV treats the first value as a year away, so the outlay gets discounted too.
`=NPV(0.1,300,400,500)`	`978.96`	The three returns alone, in today's money.

The first value is a period away

NPV assumes every value arrives at the end of a period, so the first one is discounted once, the second twice, and so on. That is right for returns that start a year out and wrong for an investment made today, which should not be discounted at all.

Leave today’s cash flow out of the range and add it separately:

=NPV(rate, year1_to_yearN) + year0

Putting the outlay inside the range is the most common spreadsheet finance error there is. It shrinks the investment by one period’s discount, and on the example above it flips the answer from −21.04 to −19.12 — small here, large on a big project with a high rate.

Reading the answer

A positive NPV means the cash flows beat the discount rate; negative means they fall short of it. The rate at which NPV is exactly zero is the IRR.

Excel compatibility

Matches Excel, including discounting the first value by one full period.