NPV function

NPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.

=NPV(rate, value1, ...)

Generate a NPV formula

Describe what you need. The generator will reach for NPV where NPV is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How NPV reads its arguments
raterequiredvalue1requiredNPV →
ArgumentRequiredDescription
rateRequiredThe discount rate per period, expressed as a decimal (0.1 for 10%). If rate is exactly -1, NPV returns #NUM! error.
value1RequiredThe first cash flow, typically an upfront cost (negative) or benefit (positive). Must be a number; text causes #VALUE! error.
...RepeatingAdditional cash flows for periods 2, 3, 4, and beyond. Each can be positive (inflow) or negative (outflow) or omitted if not applicable.

Returns

A number representing the discounted value of all future cash flows in today's dollars.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Evaluate reorder investment for SKU001

SKUWarehouseOn HandReorder PointCost
001A5030$100
002B2025$250
003A10040$50
004C1520$150
005B8050$75
=-4000 + NPV(9%, 1200, 1200, 1200, 1200)

Result: -$112.11

Reordering 40 more SKU001 units costs $4,000 upfront but avoids $1,200/year in stockout losses for 4 years. At a 9% discount rate, the discounted benefits ($3,887.90) don't cover the cost—NPV is negative, so the reorder is not justified financially.

2. Evaluate warehouse management system ROI

SKUWarehouseOn HandReorder PointCost
001A5030$100
002B2025$250
003A10040$50
004C1520$150
005B8050$75
=-12000 + NPV(10%, 3000, 4000, 4500, 5000)

Result: $827.93

Implementing a $12,000 warehouse management system generates growing annual savings: $3,000 labor savings in year 1, $4,000 from reduced waste in year 2, climbing to $5,000 by year 4. With a 10% discount rate, the discounted benefits ($12,827.93) exceed cost by $827.93—positive NPV means the investment should be approved.

3. Compare warehouse consolidation scenario

SKUWarehouseOn HandReorder PointCost
001A5030$100
002B2025$250
003A10040$50
004C1520$150
005B8050$75
=-8000 + NPV(8%, 2500, 3000, 3500, 4000)

Result: $2,605.28

Consolidating WarehouseB and WarehouseC into one location costs $8,000 upfront but saves $2,500 in year 1 (reduced staffing), increasing to $4,000 annually as efficiencies compound. The 8% discount rate reflects operational risk. Positive NPV of $2,605.28 indicates consolidation creates substantial value.

Common errors

Which NPV error are you seeing?
NPV returned an error#NUM!
Use a rate of -0.99 or -1.01 instead, or reconsider the logic—a discount rate of -1 is not realistic. If testing edge cases, verify your rate input.
#VALUE!
Convert all text numbers to actual numbers. Check source cells for text formatting using ISNUMBER(). If pulling from formulas, ensure those formulas return numbers, not text.
#REF!
Re-enter the formula with valid cell references. Use a named range to make references more stable, or copy the values directly if the source data is stable.
ErrorWhy it happensHow to fix it
#NUM!The rate parameter equals exactly -1, which makes (1 + rate) equal zero in the present-value denominator, causing division by zero.Use a rate of -0.99 or -1.01 instead, or reconsider the logic—a discount rate of -1 is not realistic. If testing edge cases, verify your rate input.
#VALUE!One of the value parameters contains text instead of a number, such as NPV(9%, "1200", 1200, 1200) or a cell containing text like 'Loss'.Convert all text numbers to actual numbers. Check source cells for text formatting using ISNUMBER(). If pulling from formulas, ensure those formulas return numbers, not text.
#REF!A cell reference in the value parameters is broken, usually because the referenced cell or column was deleted or the sheet was removed.Re-enter the formula with valid cell references. Use a named range to make references more stable, or copy the values directly if the source data is stable.

Tips and when to use something else

  • NPV > 0 means the investment creates value at your discount rate; NPV < 0 means it destroys value. Compare projects by NPV, not just by total return—the one with highest NPV wins.
  • Critical: NPV(rate, value1, value2, ...) discounts from period 1 onward. Year 0 (today's cost) is never discounted, so add it separately: =Year0Cost + NPV(rate, Year1, Year2, ...).
  • Higher discount rates compress NPV because future cash flows are worth less. Use 10–15% for operational projects, lower rates for long-lived assets, higher rates if you expect significant risk or have better alternative uses for capital.
  • When cash flows don't align to regular periods (e.g., monthly settlements in an irregular schedule), use XNPV with specific dates instead; to find the discount rate that makes NPV = 0, use IRR or XIRR.

Related functions

PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.XNPVXNPV returns the net present value of cash flows occurring at irregular intervals, discounting each flow by actual elapsed time.IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.XIRRXIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.

Frequently asked questions

What discount rate should I use for NPV?
Use your organization's cost of capital or required rate of return. For operational warehouse decisions, 8–12% is typical; for long-term infrastructure, 5–8%. Ask your finance team for your company's weighted average cost of capital (WACC). Higher rates reflect higher risk or opportunity cost—they penalize distant cash flows more heavily.
How is NPV different from IRR?
NPV tells you how much value (in today's dollars) an investment creates at a specific discount rate. IRR finds the discount rate at which NPV = 0—it's the 'breakeven' return rate. Use NPV to decide yes/no on a single project; use IRR to rank competing projects when discount rate is uncertain, or to find the effective return rate.
Can NPV be negative, and what does that mean?
Yes. Negative NPV means the investment's returns don't justify its cost at your discount rate—you'd earn more money elsewhere at that rate. Reject negative-NPV projects unless there's a strategic or non-financial reason (compliance, customer retention, etc.). A negative NPV project is a bad financial deal.
Why don't I include year 0 inside NPV()?
Because NPV() automatically discounts all values as if they occur at the end of periods 1, 2, 3, etc.—never at period 0 (today). Adding year 0 inside NPV() would incorrectly discount it. The correct form is =YearZeroCost + NPV(rate, Year1, Year2, ...) to ensure today's cost is added without discounting, and future cash flows are discounted properly.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17