Excel Formula for NPV: How to Use It

Formulas & functions · Updated November 22, 2026

The Excel formula for net present value (NPV) is =NPV(rate, value1, [value2], ...). For example, if your discount rate is in cell B1 and your cash flows are in B2:B6, you would enter =NPV(B1, B2:B6) to get the present value of those cash flows. However, the built-in NPV function has a critical quirk: it assumes each cash flow occurs at the end of the period, so if your first cash flow happens immediately (time 0), you must add it separately outside the function.

What NPV Actually Calculates

Net present value is a financial metric that converts future cash flows into today’s money. It accounts for the time value of money—a dollar today is worth more than a dollar next year because you could invest it and earn a return. The formula discounts each future cash flow back to the present using a discount rate (often the cost of capital or required rate of return).

In Excel, the NPV function does not include the initial investment automatically. It only calculates the present value of a series of future cash flows. To get the true net present value, you subtract the initial investment (which is usually a negative cash flow at time 0) from the result of the NPV function.

How the NPV Function Works in Excel

The syntax is =NPV(rate, value1, [value2], ...) where:

  • rate – the discount rate for one period (e.g., 10% as 0.10 or 10%).
  • value1, value2, … – the cash flows, which must be equally spaced in time and occur at the end of each period. You can enter them as individual numbers or as a range reference.

Excel’s NPV function uses the formula: NPV = Σ (value_i / (1 + rate)^i) for i = 1 to n. The first value is discounted by one period, the second by two, and so on. This means if your first cash flow is at the end of year 1, it’s correct. But if your first cash flow is today (time 0), the NPV function will discount it incorrectly—it would treat it as if it occurs one year from now.

Correcting for Immediate Cash Flows (Time 0)

Most investments have an initial outlay at time 0. To calculate the true NPV, you need to add that initial cash flow (usually negative) to the NPV result. For example, suppose you invest $100,000 today, and you expect to receive $30,000 at the end of each year for 5 years, with a discount rate of 8%.

Set up your spreadsheet like this:

  • A1: Rate, B1: 0.08
  • A2: Initial Investment, B2: -100000
  • A3:A7: Cash Flows, B3:B7: 30000 each

Then enter in B8: =B2 + NPV(B1, B3:B7). The result is $19,781.14 (positive, meaning the investment is profitable). If you used =NPV(B1, B2:B7) instead, you’d get $7,780.68, which is wrong because it discounts the initial investment as if it occurred at the end of year 1.

NPV vs. XNPV: Which One Should You Use?

Excel also offers the XNPV function for cash flows that are not evenly spaced. XNPV requires a schedule of dates and uses the exact number of days between dates. Its syntax is =XNPV(rate, values, dates). Use XNPV when your cash flows occur at irregular intervals (e.g., a project with payments in months 3, 7, and 12). XNPV is more accurate for real-world scenarios where cash flows don’t neatly align to yearly periods.

For example, if you have dates in A2:A6 and cash flows in B2:B6, and the rate is in B1, use =XNPV(B1, B2:B6, A2:A6). Note that XNPV includes the initial investment if it’s in the cash flow range, as long as its date is included. This is different from NPV, which excludes time 0.

How to Choose the Discount Rate

The discount rate is arguably the most important input. It should reflect the opportunity cost of capital—the return you could earn elsewhere with similar risk. Common choices include:

  • Weighted average cost of capital (WACC) for a company.
  • Required rate of return for an investor.
  • Interest rate on borrowed funds for a personal project.

There is no single “correct” rate; it depends on your situation. A higher discount rate reduces the NPV, making projects less attractive. Sensitivity analysis—testing different rates—can help you understand how robust your decision is. For instance, if NPV is positive at 8% but negative at 12%, the project is sensitive to the discount rate assumption.

Common Errors and Pitfalls

Several mistakes are common when using NPV in Excel:

  • Including the initial investment inside the NPV function – as noted, this understates the true NPV by discounting the initial outlay.
  • Using a rate that doesn’t match the period – if cash flows are monthly, use a monthly discount rate (annual rate divided by 12).
  • Blank cells in the cash flow range – Excel treats blank cells as zero, which can distort results. Use 0 explicitly if no cash flow occurs.
  • Sign errors – cash outflows should be negative, inflows positive. Mixing signs can lead to nonsensical NPVs.

Also, remember that NPV assumes cash flows occur at the end of each period. If they occur at the beginning (e.g., lease payments paid in advance), you need to adjust by shifting the function or using a different approach.

Practical Example with a Worked-Out Calculation

Let’s walk through a complete example. Suppose you’re evaluating a project that requires an initial investment of $50,000 and is expected to generate cash flows of $15,000, $20,000, $25,000, and $30,000 at the end of years 1 through 4. Your discount rate is 10%.

In Excel:

  • A1: Rate, B1: 0.10
  • A2: Initial Investment, B2: -50000
  • A3:A6: Cash Flows, B3:B6: 15000, 20000, 25000, 30000
  • In B7, enter: =B2 + NPV(B1, B3:B6)

The NPV function returns $68,862.34 (present value of future cash flows). Adding the initial investment gives $18,862.34. Since this is positive, the project is expected to add value. If you had used =NPV(B1, B2:B6), you’d get $13,862.34, which still positive but incorrect.

Frequently Asked Questions

What is the difference between NPV and IRR?

NPV tells you the dollar value added by an investment at a given discount rate. IRR (Internal Rate of Return) is the discount rate that makes NPV equal to zero. They answer different questions: NPV says “how much value?” while IRR says “what return rate?” Excel’s IRR function is =IRR(values) and assumes regular intervals, similar to NPV.

Why does my NPV function return a #VALUE! error?

This usually happens if the rate argument is non-numeric, or if any of the value arguments contain text or empty cells that are not numeric. Ensure the rate is a number (like 0.10) and that all cash flow cells contain numbers or are explicitly 0.

Can I use NPV for monthly cash flows?

Yes, but you must adjust the discount rate to a monthly rate. For example, if your annual discount rate is 12%, the monthly rate is 1% (12%/12). Use that monthly rate in the NPV function, and ensure your cash flows are monthly and evenly spaced.

Does NPV include the initial investment?

No, the NPV function only discounts future cash flows. To get the net present value, you must add the initial investment (usually a negative number) separately. This is a common source of confusion.

What is the formula for NPV in Excel 2019 or older?

The NPV function has been available in all modern versions of Excel, including 2019, 2016, and earlier. The syntax is identical. XNPV is also available in Excel 2007 and later.

How do I calculate NPV if cash flows are at the beginning of the period?

If the first cash flow occurs today, you can adjust by not using NPV for that first cash flow. For example, if you have cash flows at the start of each year, you can use =B2 + NPV(rate, B3:B6) where B2 is the first cash flow at time 0. Alternatively, you can use the PV function for each cash flow individually.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.