Net Present Value (NPV): Formula and Worked Examples

Net present value (NPV) is the sum of a project's future cash flows, each discounted back to today at the required rate of return, minus the initial investment. A positive NPV means the project is expected to earn more than the required return and add value; a negative NPV means it is expected to fall short. NPV is the most widely recommended test for investment decisions because it measures value in dollars and takes account of the timing of cash. This guide gives the formula, works through two examples with the arithmetic, shows the sensitivity of NPV to the discount rate, explains the most common Excel mistake and lists practical cautions. It is part of budgeting and forecasting, supports capital budgeting methods, and leads into internal rate of return and the payback period.

The formula

NPV = -Initial investment + Cash flow in year 1 / (1 + r) + Cash flow in year 2 / (1 + r)^2 + ... + Cash flow in year n / (1 + r)^n

Written compactly: NPV = sum of CF(t) / (1 + r)^t for t = 0 to n, where CF(0) is the (negative) investment and r is the discount rate. The idea of discounting is that a dollar received later is worth less than a dollar today, because you could invest the dollar today and earn a return, and because later cash is riskier.

Decision rule

Example 1: a five-year project

A project costs $100,000 and produces $30,000, $35,000, $40,000, $40,000 and $30,000 in years 1 to 5. The discount rate is 10 percent.

Check one line: 35,000 / 1.21 = 28,925.62. The NPV is about $32,200. The project adds roughly $32,200 of value in today's dollars after earning the 10 percent return.

Sensitivity to the discount rate

NPV falls as the discount rate rises, and reaches zero at the project's internal rate of return, about 21.7 percent. The table shows how much room there is for error in the required return: the project stays positive for any rate below 21.7 percent. See internal rate of return. The choice of the rate is a judgment, and is discussed in WACC and hurdle rates.

Example 2: an equipment replacement

A company considers a machine that costs $50,000, saves $14,000 a year in labor and scrap for five years and has a salvage value of $5,000 at the end of year 5. The required return is 10 percent.

• Present value of the savings (an annuity): $14,000 × annuity factor. The five-year annuity factor at 10 percent is (1 - 1.10^-5) / 0.10 = 3.7908. So PV = 14,000 × 3.7908 = $53,071.
• Present value of salvage: $5,000 / 1.10^5 = 5,000 × 0.6209 = $3,105.
• Total PV of inflows = $53,071 + $3,105 = $56,176.
• NPV = $56,176 - $50,000 = $6,176.

The project is acceptable, but the margin is modest: the NPV is 12 percent of the investment. A break-even check is useful: what annual savings make NPV zero? Required PV of savings = $50,000 - $3,105 = $46,895; annual savings = $46,895 / 3.7908 = $12,371. Savings can be 11.6 percent below the estimate ((14,000 - 12,371) / 14,000) before the NPV turns negative. If the savings estimate is uncertain by more than that, the decision is not safe. That is more informative than the NPV figure alone.

Using Excel correctly

Excel's NPV function assumes the first cash flow occurs at the end of period 1, not at time zero. So the initial investment should be added outside the function:

=NPV(rate, CF1:CF5) + CF0

where CF0 is the negative investment. A common error is to include the investment as the first value in the range, which makes Excel treat it as a year-1 flow and pushes every later flow back one period. For the project above, the correct formula is =NPV(10%, 30000, 35000, 40000, 40000, 30000) - 100000, which returns about 32,199. Putting -100,000 inside the range returns 32,199 / 1.10 = 29,272, an understatement of about 2,927. The error shrinks a positive NPV and shrinks a negative one toward zero, so it is easy to miss. For flows with irregular dates, use XNPV with a date column, and check which date Excel treats as time zero.

Choosing the discount rate

Use the same rate basis as the cash flows: nominal rates for nominal (inflation-inclusive) cash flows, real for real, and after-tax rates for after-tax cash flows. See WACC and hurdle rates.

What goes in the cash flows

• Incremental, after-tax cash flows only.
• The investment in working capital, and its recovery.
• Salvage or terminal value.
• No sunk costs; no interest or financing flows.
• Cash flows that reflect operating reality: volumes, prices, costs and lead times. For products, see demand planning and inventory optimization.

Cautions

Link to unit economics

The cash flows of a product or customer investment depend on contribution per unit, volume and acquisition cost. For margin analysis that feeds NPV, see unit economics.

FAQs

What is net present value?

The sum of a project's discounted future cash flows minus the initial investment; a positive value means the project is expected to add value after earning the required return.

How do you calculate NPV?

Discount each year's cash flow by (1 + r) raised to the year number, add them, and subtract the initial investment.

What does a negative NPV mean?

The project is expected to earn less than the required return, so it would reduce value.

Why does Excel's NPV function give the wrong answer?

It treats the first value as occurring at the end of period 1. Add the initial investment separately.

What discount rate should I use?

Typically the company's cost of capital adjusted for the project's risk, consistent with the cash flows used.