NPV Function

Calculates what a series of future cash flows is worth today.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A net present value

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

NPV discounts future cash flows back to today at a chosen rate (your cost of capital). A positive result after subtracting the up-front cost means the project earns more than that rate.

The catch: NPV assumes the first value arrives one period from now. An investment made today must be added outside the function, not included in it.

Syntax

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

Arguments

ArgumentRequired?What it does
rateYesThe discount rate per period.
value1, value2, …YesCash flows, one per period, in order — as ranges or values.

Examples

Example 1 — Should Northstar open the new store?

The fit-out costs $150,000 today and is expected to return four years of cash. Northstar's cost of capital is 8%.

B7fx=NPV(8%,B3:B6)+B2
AB
1YearCash flow
20 (today)-$150,000
31$40,000
42$50,000
53$60,000
64$55,000
7NPV$17,960.55

Positive, so the store beats an 8% return by about $18,000 in today's money.

Example 2 — The classic mistake

Including today's $150,000 inside NPV discounts it by a year, as if it were spent in year 1.

B2fx=NPV(8%,-150000,40000,50000,60000,55000)
AB
1FormulaResult
2Wrong: cost inside NPV$16,630.14
3Right: cost added after$17,960.55

Common mistakes

  • NPV is understated. The time-0 investment was put inside the function. Add it separately.
  • Irregular dates. NPV assumes equal spacing. Use XNPV when cash arrives on specific dates.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.