Financial

NPV Function in Excel

Calculates the net present value of an investment based on a discount rate and future cash flows.

Syntax

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

Arguments

  • rate (required): The discount rate over one period
  • value1 (required): First cash flow (at end of period 1)
  • value2 (optional): Additional cash flows

Examples

  • =NPV(10%, 30000, 35000, 40000, 45000) - NPV of future cash flows - Result: $119,834
  • =-100000 + NPV(10%, B2:B5) - NPV including initial investment - Result: Net present value

Excel NPV function documentation

  • NPV calculates net present value of cash flows discounted at a constant rate per period.
  • Syntax: =NPV(rate, value1, [value2], ...). First value1 is period 1 — not time 0.
  • Add initial investment separately: =NPV(rate, flows) + initial_at_time_0.
  • Related: [IRR](/functions/irr/), [XNPV](/functions/xnpv/), [PV](/functions/pv/), [FV](/functions/fv/).

NPV syntax and period timing

  • rate: discount rate per period (e.g. 0.08 for 8% annual if periods are years).
  • Cash flows must be equally spaced — use XNPV for dated irregular flows.
  • Common mistake: omitting year-0 outlay outside NPV parentheses.
  • NPV > 0 generally means project adds value at that discount rate.

Step-by-step: NPV of five-year project

  • Step 1 — Discount rate 10% in E1.
  • Step 2 — Year 0 cost -50000 in B2 (outside NPV).
  • Step 3 — Years 1–5 inflows B3:B7.
  • Step 4 — =B2+NPV(E1, B3:B7).
  • Step 5 — Compare projects by ranking NPV — highest wins holding rate constant.

NPV vs XNPV vs PV

  • NPV — series of periodic flows + manual time-0 adjustment.
  • XNPV — date column for each flow.
  • PV — single future/present amount annuity style.
  • Pair NPV with [IRR](/functions/irr/) for rate that makes NPV zero.

Worked examples to copy

  • Simple NPV: =NPV(0.1, 10000, 12000, 15000).
  • With initial: =-50000+NPV(0.08, B3:B12).
  • Accept rule: =IF(-C0+NPV(r,flows)>0,"Go","No").
  • Monthly rate: use rate/12 if flows are monthly.

People also ask

  • Why NPV ignores first cash flow timing? — Excel NPV treats first arg as end of period 1.
  • NPV #NUM!? — Invalid rate argument.
  • NPV vs IRR? — NPV needs discount rate; IRR finds break-even rate.
  • Annual vs monthly NPV? — Rate must match period length of cash flows.

Common errors

  • NPV assumes cash flows at END of periods
  • Initial investment should be added separately

Use cases

  • Investment valuation
  • Project comparison
  • Capital budgeting
  • Business valuation

Frequently asked questions

  • Why add initial investment separately? Excel's NPV function assumes the first cash flow occurs at the end of period 1. Since initial investments typically occur at time 0 (today), you should add them separately: =InitialInvestment + NPV(rate, future_cash_flows).
  • What discount rate should I use? Use your company's weighted average cost of capital (WACC) or required rate of return. For personal investments, use your opportunity cost - what you could earn elsewhere with similar risk.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use NPV

  • Investment valuation — common Financial scenario for NPV.
  • Project comparison — common Financial scenario for NPV.
  • Capital budgeting — common Financial scenario for NPV.
  • Business valuation — common Financial scenario for NPV.

NPV in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • NPV syntax: =NPV(rate, value1, [value2], ...)
  • rate (required): The discount rate over one period
  • value1 (required): First cash flow (at end of period 1)
  • value2 (optional): Additional cash flows
  • Confirm NPV arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm NPV arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IRR (/functions/irr/): Returns the internal rate of return for a series of cash flows.
  • XNPV (/functions/xnpv/): Returns the net present value for a schedule of cash flows with specific dates.
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now.

Errors to watch for

  • NPV assumes cash flows at END of periods — review causes on linked error pages in the directory.
  • Initial investment should be added separately — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter NPV on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

NPV worked examples to copy

  • =NPV(10%, 30000, 35000, 40000, 45000) — NPV of future cash flows. Expected result: $119,834.
  • =-100000 + NPV(10%, B2:B5) — NPV including initial investment. Expected result: Net present value.

NPV reference summary for crawlers and offline review

  • NPV belongs to the Financial category in Excel. Calculates the net present value of an investment based on a discount rate and future cash flows.
  • Full syntax: =NPV(rate, value1, [value2], ...). Open /functions/npv/ for parameters, FAQs, and related pages.
  • Common mistakes: NPV assumes cash flows at END of periods; Initial investment should be added separately
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.