Excel NPV Function — Net Present Value (with Examples)

What does it do?

NPV (Net Present Value) calculates the present-day value of a series of future cash flows, discounted at a given rate. A positive NPV means the investment is expected to generate more value than it costs — it is worth proceeding. A negative NPV means the opposite. NPV is the cornerstone of capital budgeting and investment appraisal.

Syntax

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

Argument	Description
=NPV(rate, value1, [value2], ...)
rate required	The discount rate per period. Use your cost of capital or required rate of return.
value1 ... required	Cash flows at the end of each period. Can be a range. Must be equally spaced in time.

Real examples

Example 1

Basic NPV calculation

=NPV(10%, B2:B6)

Discounts 5 years of cash flows at 10%. B2 is year 1, B6 is year 5.

Example 2

NPV with initial investment

=NPV(10%, B3:B7) + B2

B2 is the initial investment (negative). Add it separately as NPV assumes flows start at end of period 1.

Example 3

Compare two investments

=NPV(8%, C2:C6) > NPV(8%, D2:D6)

Returns TRUE if project C is worth more than project D.

Example 4

NPV with growing cash flows

=NPV(B1, C2:C7) + B2

B1 = discount rate, C2:C7 = projected cash flows, B2 = upfront cost.

Example 5

Decision formula

=IF(NPV(10%,B3:B7)+B2>0,"Accept","Reject")

Automatically flags whether the investment meets the hurdle rate.

FAQ

Why do I add the initial investment separately?

Excel's NPV assumes the first value (value1) is one period from now — not today. If your initial investment happens today (period 0), add it separately outside the NPV function: =NPV(rate, future_flows) + initial_investment.

What discount rate should I use?

Typically the weighted average cost of capital (WACC) or the required rate of return on equity. For a business loan comparison, use the loan interest rate.

What is the difference between NPV and IRR?

NPV tells you the dollar value created by an investment at a given discount rate. IRR tells you what discount rate would make NPV equal zero — the break-even rate. Use both together for investment decisions.

NPV in real investment decisions

NPV answers the question: "Is this investment worth making at my required rate of return?" If you require a 10% annual return on your money, any investment with an NPV above zero at a 10% discount rate meets your threshold. An NPV below zero means the investment returns less than your required rate — you would be better off putting the money in an alternative achieving 10%.

The discount rate is crucial. It represents the opportunity cost of capital — what you could earn elsewhere at equivalent risk. For a business, this is often the weighted average cost of capital (WACC). For a personal investment, it might be the return you expect from an index fund. Changing the discount rate significantly changes whether the NPV is positive or negative.

A common mistake is treating the initial investment the same as future cash flows. If you invest £100,000 today and receive cash flows over the next 5 years, the investment happens NOW (period 0) while Excel's NPV assumes value1 is one period from now. Always: =NPV(rate, future_cash_flows) + initial_investment_as_negative_number.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

PMT IRR PV FV ROUND SUMPRODUCT

The Excel NPV Functionexplained simply