NPV (net present value): what it is, the formula and how to calculate it

NPV (net present value) is the sum of a project’s future cash flows converted into today’s money at a discount rate, minus the investment.

If NPV is above zero, the project earns more than the required return.

For example, €150,000 invested and €30,000–60,000 a year for five years at a 12% rate give an NPV of +€16,439.

Below: what NPV means in plain terms, the formula and a worked example, how to calculate NPV in Excel and Google Sheets, how to choose a discount rate, how NPV differs from IRR and where it misleads. The page includes an NPV, IRR and payback calculator.

What NPV means in plain terms

A euro in five years is worth less than a euro today: the money could be invested elsewhere, there is inflation, and there is a risk the promised cash never arrives.

NPV converts all of a project’s future money into today’s money at a rate that reflects the cost of money and the risk, and compares it with the investment.

• NPV above zero — the project returns the investment, covers the required return and creates value on top.
• NPV of zero — the project earns exactly the required return.
• NPV below zero — at this rate, the alternative investment is better.

A negative NPV does not mean the project makes a loss. It means the project falls short of the return you require for your risk.

The NPV formula

NPV = CF₀ + CF₁ / (1 + r) + CF₂ / (1 + r)² + … + CF_n / (1 + r)ⁿ

• CF₀ — the initial investment, as a negative number;
• CF₁…CF_n — the net cash flow of each year: receipts minus payments, after corporate tax;
• r — the annual discount rate;
• n — the horizon in years.

The factor 1 / (1 + r)^t is the discount factor: it shows what one euro received in t years is worth today.

How to calculate NPV: an example

An illustrative project: €150,000 invested, cash flows of €30,000, €40,000, €50,000, €60,000 and €60,000 by year, a 12% discount rate.

Without discounting, the project brings in €90,000 above the investment; once the cost of money is taken into account, €16,439. That is the value the project creates at a required return of 12%.

How NPV depends on the rate: €36,700 at 8%, €26,132 at 10%, €3,344 at 15% and minus €15,239 at 20%. The rate at which NPV becomes zero is the IRR: 15.8% for this project.

NPV, IRR and payback calculator

Enter the investment, the rate and the cash flows by year.

The calculator shows NPV, IRR, the profitability index and the payback period — simple and discounted — and the table below shows each year’s flow in today’s money.

NPV in Excel and Google Sheets

The main trap: the NPV function discounts the very first value of the range as if it arrived after one year. So the year-0 investment is left out of the range and added separately.

For the example above: −150,000 in A1, the flows in A2:A6, and =NPV(12%, A2:A6) + A1 gives €16,439. =NPV(12%, A1:A6) would understate the result at about €14,678.

If payments are irregular — monthly, with delays — use XNPV with the date of each payment.

How to choose the discount rate

The rate is NPV’s key assumption: at 10% the example project creates €26,132, at 20% it destroys €15,239. The rate comes from what money costs whoever is investing:

• A project funded with debt — at least the loan rate, net of the tax saving on interest.
• A company project — the weighted average cost of capital (WACC): the shares of equity and debt, each at its own price.
• The owner’s own money — the return they would earn on another investment with similar risk, plus a premium for the project’s risk.
• A venture investor in a startup — a much higher target return, because most startups do not return the money.

The riskier and more distant the flows, the higher the rate.

Good practice is to calculate NPV at two or three rates and see where it turns negative: that is the project’s safety margin.

NPV vs IRR: which to use

NPV shows how many euros of value a project creates; IRR shows the return the invested money earns.

For a single project they give the same answer: NPV is positive exactly when IRR is above the rate.

When you must choose one project from several, rely on NPV and use IRR and the payback period as supporting measures.

Where NPV misleads

• The cash-flow forecast. NPV is only as accurate as the revenue and costs in the financial model. Inflated growth in the later years inflates the result.
• The rate. A small change in the rate moves the NPV of long projects a lot.
• Terminal value. If the last year includes selling the business or the building, it often makes up half of the NPV — calculate it separately and conservatively.
• Inflation. Keep flows and rate in the same terms: both in nominal prices with inflation, or both in real terms.
• Taxes and working capital. Flows are after corporate tax and include money tied up in stock and customer receivables.

NPV of a project in Spain

• Flows are calculated after corporate tax at the 2026 rates: 25% standard, 23% for turnover under €10m, 19% on the first €50,000 and 21% above for turnover under €1m, 15% for a new company in its first profitable year and the next.
• VAT is left out of the flows: the company passes it on to the tax agency. But the timing gap between collecting and paying VAT affects cash.
• When buying an existing business, calculate NPV on revenue from bank statements and VAT returns, not the seller’s presentation. How this looks for specific sectors is shown in Buying a café or bar in Spain and Buying a small hotel in Spain.

How to build taxes and their timing into the model is covered in Financial model calculations and scenarios.

Frequently asked questions

What is NPV in simple terms?

NPV is how much value a project creates above the required return once all its future flows are converted into today’s money. A positive NPV means the project pays at the chosen rate; a negative one means the alternative is better.

How do you calculate NPV?

Divide each year’s cash flow by (1 + r) to the power of the year number, add them up and subtract the investment. In Excel: =NPV(rate, flows of years 1…n) + year-0 investment as a negative number.

What is a good NPV?

Any NPV above zero means the project covers the required return. To compare projects, use the profitability index — NPV per euro invested — and the rate at which NPV turns negative.

What is the difference between NPV and IRR?

NPV shows the value created in euros at a given rate; IRR is the rate at which NPV equals zero. For choosing between projects NPV is more reliable: it accounts for scale and is unambiguous with complex flows.

Which discount rate should I use?

The cost of money for whoever is investing: the loan rate, the company’s weighted average cost of capital, or the return on an alternative investment with similar risk plus a premium for the project’s risk. The riskier the project, the higher the rate.

Why is NPV lower in Excel?

The NPV function assumes the first value in the range arrives after one year. If the year-0 investment is included in the range, it gets discounted too and NPV comes out too low. Add the investment separately, after the function.

Key points about NPV

• NPV is future cash flows in today’s money at the discount rate, minus the investment.
• NPV above zero — the project creates value above the required return.
• The key assumptions are the cash-flow forecast and the rate: calculate NPV at several rates.
• In Excel, add the year-0 investment separately from the NPV function.
• When choosing between projects, NPV is more reliable than IRR.

Sources

• Microsoft — NPV function: support.microsoft.com; XNPV function — support.microsoft.com.
• Google — NPV and XNPV in Sheets: support.google.com.
• Ley 27/2014 del Impuesto sobre Sociedades, art. 29 and transitional provision 44 — BOE.