NPV Formula Examples in Excel: Cash Flows Explained

In Excel, calculate net present value with =NPV(rate, future_cash_flows)+initial_cash_flow: enter the discount rate and future cash flows in order, then add the initial outlay separately. For example, at a 10% annual discount rate, an investment costing $10,000 and returning $4,000, $4,500, and $5,000 at the ends of the next three years has an NPV of about $1,111.95.

What net present value measures

Net present value, usually shortened to NPV, expresses a series of future cash flows in today’s money. It accounts for the time value of money: a dollar received now is generally worth more than a dollar received later because money available today can be put to use, while future payments carry uncertainty and opportunity costs.

To make future amounts comparable with an initial cost, NPV discounts each future cash flow using a chosen rate. That rate reflects the return an investment must earn to be worthwhile for the analysis. It might be a company’s required return or another justified benchmark; it is an input to the calculation, not a result Excel discovers automatically.

A positive NPV means the discounted future inflows exceed the outflows included in the calculation. A negative NPV means they fall short at the selected rate. NPV is a decision aid, not a guarantee: its result depends on the quality of the cash-flow estimates, the timing assumptions, and the rate selected.

How Excel’s NPV formula handles timing

Excel’s NPV function treats the first value in its cash-flow list as arriving at the end of the first period. The next value is discounted for two periods, the following value for three, and so on. If the periods are years, the rate must be an annual rate; if they are months, use a rate that corresponds to a month.

This timing convention explains a common source of errors: the initial investment is usually paid at time zero, before the first future period. It should not be placed inside the values passed to NPV as if it occurred at the end of period one. Instead, calculate the present value of the future cash flows with NPV and add the initial outflow separately. In a spreadsheet, that outflow is commonly entered as a negative number.

For a layout with the annual discount rate in A1, the initial outlay in B2, and end-of-year cash flows in B3:B5, use =NPV(A1,B3:B5)+B2. The cell B2 should contain a negative amount, such as -10000. The comma-separated argument syntax shown is for standard English-language Excel settings; some regional settings use semicolons as separators.

Example: a three-year project

Suppose a small project requires $10,000 now and is expected to return $4,000 after year one, $4,500 after year two, and $5,000 after year three. If the required annual return is 10%, arrange the worksheet like this:

A1: 10% discount rate.
B2: -10000 initial investment.
B3: 4000, B4: 4500, and B5: 5000 in expected year-end inflows.

With the rate in A1, the initial outlay in B2, and the three future inflows in B3:B5, enter =NPV(A1,B3:B5)+B2. Excel returns approximately $1,111.95. The future inflows have a combined present value of about $11,111.95; after subtracting the $10,000 cost, about $1,111.95 remains.

The result is not the project’s total cash profit. The undiscounted inflows add up to $13,500, so the nominal surplus over the initial cost is $3,500. NPV is lower because it discounts the later receipts. This difference is precisely why NPV can be more informative than simply adding expected payments and subtracting the purchase price.

Example: a positive and a negative NPV

Consider a different project with a $12,000 cost today and four expected year-end receipts of $4,000 each. At an 8% annual rate, the cash flows are discounted less heavily than in the previous example. Put 8% in A1, -12000 in B2, and the four receipts in B3:B6.

With the rate in A1, the initial amount in B2, and the equal annual receipts in B3:B6, use =NPV(A1,B3:B6)+B2. The result is approximately $1,248.51. The present value of the four $4,000 receipts is about $13,248.51, which exceeds the initial cost by that amount.

Now imagine the same four annual receipts but a $15,000 initial cost and a 10% required return. With 10% in A1, -15000 in B2, and 4500 in each of B3:B6, enter =NPV(A1,B3:B6)+B2. Excel returns approximately -$735.61. The receipts total $18,000 without discounting, but their present value at 10% is about $14,264.39, below the cost. These examples show why both the rate and the timing matter when judging a project.

Using XNPV when dates are irregular

NPV assumes that cash flows arrive at regular intervals. If the dates are uneven—for example, a payment arrives after six months, another after 14 months, and another after 30 months—an ordinary NPV calculation does not represent those exact dates. Excel’s XNPV function is designed for cash flows tied to specific dates and discounts using the actual number of days between dates, based on a 365-day year.

For example, put dates in A2:A5 and corresponding cash flows in B2:B5: January 1, 2025 with -10000; January 1, 2026 with 4000; January 1, 2027 with 4500; and January 1, 2028 with 5000. Put the annual rate, 10%, in D1. With dates in A2:A5, matching values in B2:B5, and the rate in D1, enter =XNPV(D1,B2:B5,A2:A5). The result is approximately $1,111.95 because these dates are exactly 365 days apart in this example.

Include the initial outflow in the XNPV values range, paired with its date; unlike the standard NPV setup, it is not added separately after the function. Make sure the date cells contain actual Excel dates rather than text that merely looks like dates, and ensure every value has a corresponding date. XNPV is useful for irregular schedules, but it does not make uncertain estimates more reliable—it only handles their timing differently.

Choosing a discount rate and period

The rate should match the cash-flow period. If your estimates are annual, use an annual rate; if they are monthly, use a monthly rate. Using 12% as a monthly rate would discount far more aggressively than using 12% as an annual rate. For a nominal annual rate of 12% compounded monthly, a monthly equivalent is often calculated from the annual rate according to the applicable compounding convention; use the convention relevant to the analysis rather than mixing periods casually.

Likewise, decide whether the cash-flow estimates and rate are stated in nominal or inflation-adjusted terms. Nominal cash flows, which include expected inflation, should generally be paired with a nominal rate. Cash flows expressed in today’s purchasing power should generally be paired with a real rate. Mixing the two can distort the result.

When comparing alternatives, keep assumptions consistent where appropriate: use the same valuation date, currency basis, period definition, and justified rate. A small change in the discount rate can matter substantially for projects whose benefits arrive far in the future. It can be useful to calculate several scenarios—such as a lower, central, and higher rate—rather than presenting one estimate as certain.

Common NPV mistakes and how to avoid them

Including the initial cost as a period-one flow. In a standard NPV setup, keep a time-zero outflow outside the NPV range and add it separately.
Using the wrong sign. Enter an investment payment as a negative cash flow and receipts as positive cash flows. Reversing the signs reverses the interpretation.
Misaligning rates and periods. Do not discount monthly cash flows with an annual rate as though each month were a full year.
Leaving out a cash flow or period. Check that the range includes every expected receipt and payment in chronological order, with no accidental blank or omitted year.
Confusing NPV with total profit. NPV discounts future values; it is not the same as adding all cash flows without adjusting for timing.
Treating a positive result as certainty. Forecasts can be wrong, and NPV does not measure every strategic, operational, or nonfinancial consideration.

It is also worth checking that the initial outlay and forecast amounts use the same currency and valuation basis. If taxes, maintenance, working capital, salvage value, or later replacement costs affect the project, omitting them may make the estimate incomplete. Whether those items belong in a particular analysis depends on the cash-flow question being asked.

NPV compared with IRR and payback

NPV answers a direct question: after discounting the projected cash flows at a chosen rate, how much value remains beyond the initial cost? Internal rate of return, or IRR, instead estimates the rate at which the NPV of a set of cash flows equals zero. IRR can be useful as a percentage return measure, but it can be ambiguous for cash flows that change sign multiple times and can rank projects differently from NPV, especially when their size or timing differs.

Payback period asks how long it takes for cash inflows to recover an initial outlay. It is easy to explain, but a basic payback calculation does not account for the time value of money and may ignore cash flows after the payback point. Discounted payback accounts for discounting but still focuses on recovery time rather than the total value represented by NPV. These methods answer different questions; a reader should not assume one measure replaces all the others.

For mutually exclusive projects, a higher positive NPV can indicate greater value under the same assumptions and rate, but comparisons also depend on project life, scale, and whether cash flows can be reinvested or repeated. If the alternatives have different lifespans, an NPV comparison over a common time horizon or another suitable method may be needed.

Frequently Asked Questions

Does Excel NPV include the initial investment?
No. Excel discounts the values supplied to NPV as end-of-period cash flows. For a time-zero investment, keep the cost outside that range and add it to the NPV result, usually as a negative number.

Why is my Excel NPV result different from the total of the cash flows?
NPV discounts each future cash flow according to its period and the chosen rate. The undiscounted total does not adjust for when money is received, so it will generally differ.

Should I use NPV or XNPV?
Use NPV when cash flows occur at regular, equal intervals, such as each year-end. Use XNPV when you need the calculation to reflect actual, irregular dates. In XNPV, include the initial cash flow and its date in the values and dates ranges.

What does a negative NPV mean?
It means the discounted cash inflows are less than the outflows included, using the rate and timing assumptions provided. It does not prove that an investment will lose money in every sense; it indicates that it falls short of the selected return benchmark in this calculation.

Can I use a monthly discount rate with annual cash flows?
Not without converting the rate or changing the period basis. The rate and the spacing of the cash flows must be consistent. Before using an NPV result for a real financial decision, verify the relevant assumptions and terms with a qualified professional.

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.