How to Calculate NPV in Excel: Formula and Examples

Search Fintrev

Search our guides, reviews and glossary.

To calculate NPV in Excel, list the project’s cash flows by period, with the initial investment in year 0 as a negative number. Then use =NPV(rate, year1:yearN) + year0. Excel’s NPV function discounts every value you give it, including the first one, by at least one full period. So the year 0 outlay goes outside the function and is added at the end. You can reach the same answer by discounting each cash flow yourself and summing the results, or by using XNPV when cash flows fall on specific, irregular dates.

What NPV measures

A project usually costs money up front and pays back over several years. Because money received later is worth less than money held today, NPV converts every future cash flow into today’s dollars and nets them against the investment.

The general formula is:

NPV = Σ CFₜ / (1 + r)ᵗ, summed from t = 0 to t = n

where CFₜ is the net cash flow in period t, r is the discount rate per period, and n is the final period. The year 0 cash flow is divided by (1 + r)⁰, which equals 1, so it enters at face value.

The inputs are projected net cash flows, not accounting profit, so build projections from a cash flow statement rather than the income statement. Include any terminal or salvage value in the final period’s cash flow.

Setting up the worksheet with year 0

Both Excel methods start from the same layout. The example used throughout is hypothetical: a factory costs $250,000 to set up, produces $100,000 of net cash inflow in year 1, and the inflow grows by $50,000 each year through year 5. The discount rate is 10%.

• B1: discount rate, 10%
• A3:A8: periods 0, 1, 2, 3, 4, 5
• B3: −250,000 (year 0, the initial outlay)
• B4:B8: 100,000; 150,000; 200,000; 250,000; 300,000

Enter outflows as negative numbers and inflows as positive numbers. Put the period number in its own column: the manual method uses it as the exponent, and it shows the timing of each cash flow to anyone checking the model.

Method 1: Discount each cash flow manually

In C3, enter:

=B3/(1+$B$1)^A3

Copy it down to C8. The dollar signs lock the rate cell, so every row uses the same 10%. The period number in column A sets the exponent. Year 0 gets divided by 1, year 1 by 1.1, year 2 by 1.21, and so on.

The present values work out as follows:

• Year 0: −$250,000.00
• Year 1: $100,000 / 1.1 = $90,909.09
• Year 2: $150,000 / 1.21 = $123,966.94
• Year 3: $200,000 / 1.331 = $150,262.96
• Year 4: $250,000 / 1.4641 = $170,753.36
• Year 5: $300,000 / 1.61051 = $186,276.39

Then sum the column with =SUM(C3:C8). The NPV is $472,168.74.

Method 2: Use the NPV function

Excel’s syntax is:

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

rate is the discount rate for one period. The values are the cash flows, either as separate arguments or as a range, up to 254 arguments. Excel assumes the values are evenly spaced and that each one arrives at the end of its period, with the first value one period from today.

For the factory example:

=NPV(B1, B4:B8) + B3

NPV(B1, B4:B8) returns $722,168.74, which is the present value of the five inflows. Adding B3 (−$250,000) gives $472,168.74, the same result as Method 1.

Why year 0 stays outside the function

The most common error is writing =NPV(B1, B3:B8). Excel then treats the −$250,000 as a year 1 cash flow and pushes every other flow back one year. The result is $429,244.31, which is the correct NPV divided by 1.1, because the whole stream has been discounted one period too many. The mistake is easy to miss because the number looks plausible. Whenever a cash flow happens today, add it outside the function.

Blank cells change the timing

When NPV reads a range, it ignores empty cells and text instead of treating them as zero. Suppose a project has no cash flow in year 3 and you leave that cell blank. Excel skips it, and the year 4 and year 5 amounts are discounted as if they arrived in years 3 and 4. Type a 0 in any period with no cash flow.

XNPV for cash flows on specific dates

The NPV function only works when periods are equal and cash flows land at period ends. Real projects often pay on irregular dates, and the investment may be made in installments. XNPV handles this:

=XNPV(rate, values, dates)

rate is an annual rate. values and dates are matching ranges. XNPV discounts each cash flow by (1 + rate) raised to the number of days since the first date divided by 365. The first value is not discounted, so with XNPV the initial outlay goes inside the values range, opposite to the NPV function. Dates must be valid Excel dates, and none can fall before the first date.

If cash actually comes in through the year, a midyear convention is often closer to reality. With plain NPV, approximate it by multiplying the result for future flows by (1 + r)^0.5. With XNPV, enter the dates on which you expect the cash to arrive.

Monthly cash flows

The rate argument must match the spacing of the cash flows. For monthly cash flows, convert the annual rate to a monthly rate. Dividing by 12 is the usual convention when the rate is quoted as a nominal annual rate compounded monthly. For an effective annual rate, use =(1+annual_rate)^(1/12)-1. Then list one cash flow per month.

For a retirement plan, list monthly contributions as negative values and later withdrawals as positive values, then apply NPV at the monthly rate. Anything that happens at month 0 goes outside the function.

When every payment is the same amount, as with a car loan or a fixed annuity, the PV function is simpler: =PV(rate, nper, pmt). An example: =PV(6%/12, 60, -400) gives the present value of 60 monthly payments of $400 at a 6% nominal annual rate. NPV is the tool for an unequal series of cash flows, where the amount changes from period to period.

Where the discount rate comes from

Excel does not supply the rate. The analyst chooses it, and it often moves the answer more than any formula detail. Companies commonly use the weighted average cost of capital (WACC), which blends the cost of debt and equity financing. They may also use a hurdle rate set above WACC for riskier projects. An individual might use the return available on an alternative investment of similar risk.

Keep real and nominal figures consistent. If the cash flow projections include expected price increases, use a nominal rate. If they are in today’s dollars, use a real rate that excludes inflation.

Interpreting the result

• NPV greater than 0: at the chosen discount rate, the project is expected to return more than its cost of capital and to add value.
• NPV equal to 0: the project is expected to earn exactly the discount rate. That rate is the project’s internal rate of return, which Excel’s IRR (or XIRR for dated flows) calculates.
• NPV less than 0: the project is expected to earn less than the required return. It may still produce accounting profit, but it destroys value relative to the alternative use of the money.

When comparing projects of similar size and risk, the one with the higher NPV adds more value. NPV is only as reliable as the cash flow estimates and the rate behind it, so companies usually weigh it alongside IRR, payback period and qualitative factors.

Testing different assumptions

Because the result depends heavily on inputs, Excel’s one-variable data table (Data > What-If Analysis > Data Table) shows how NPV changes as the rate or the cash flows change, without rewriting formulas:

1. List alternative discount rates in a column, for example 6% to 14%.
2. In the cell above and to the right of the list, reference the NPV result cell.
3. Select the block, open Data Table, and set the column input cell to B1.

Excel recalculates NPV at each rate. You can see how much room the project has before NPV turns negative. The same setup works with a growth assumption or the initial cost as the input cell.

Choosing a method

Manual discounting shows every discount factor and input, which makes it a common choice for financial models that will be audited. It takes more cells, though, and more chances to mistype a reference or an exponent. The NPV function needs one formula and is quick to build and change, but its timing assumptions (first value one period out, end-of-period flows, blanks skipped) are invisible in the cell, and that is where most errors come from. XNPV removes the equal-period assumption but is just as opaque.

A practical approach is to build the model with one method and check it with the other. If both return the same figure, the timing and signs are almost certainly right.

The Bottom Line

Anyone evaluating a project or investment in Excel can get a reliable NPV if the year 0 cash flow, signs and period spacing are handled correctly. The discount rate, often WACC or a hurdle rate, usually moves the answer more than any formula detail, so test a range of rates with a data table. Building the model with one method and checking it with another catches most timing and sign errors.