Excel NPV of a Series of Cash Flows: Formula and Examples

Formulas & functions · Updated November 18, 2026

To calculate the net present value of a cash-flow series in Excel, use =NPV(B1,B3:B5)+B2 when the discount rate is in B1, the initial cash flow at time zero is in B2, and the later, equally spaced cash flows are in B3:B5. In this example, the formula discounts the later amounts and then adds the initial investment separately; that timing distinction is essential to getting the right answer.

What Excel NPV measures

Net present value, or NPV, expresses a series of future cash flows in today’s money after applying a discount rate. A dollar received in the future is worth less than a dollar received now because money could have earned a return in the meantime, and because future benefits may be uncertain. The discount rate represents the required return or cost of capital chosen for the analysis.

NPV brings future inflows and outflows back to a common point in time, then combines them. A positive result means the discounted inflows exceed the discounted outflows included in the calculation. A negative result means they fall short. The result is a value in the same currency as the cash flows, not a percentage return.

Excel’s NPV function assumes that the values supplied occur at regular intervals and that the first value occurs one interval after the valuation date. That is why a cash flow at time zero, such as an upfront project cost paid today, normally sits outside the NPV range and is added separately.

Worked example with an initial investment

Suppose a project requires an initial outlay of $10,000 today and is expected to return $4,000 after one year, $4,500 after two years, and $5,000 after three years. For illustration, use a 10% annual discount rate. Put 10% in B1, -10000 in B2, 4000 in B3, 4500 in B4, and 5000 in B5. The negative sign marks the initial payment as money going out.

With the discount rate in B1, the time-zero cash flow in B2, and year-one through year-three cash flows in B3:B5, enter =NPV(B1,B3:B5)+B2. The future inflows have a present value of about $11,111.95; after adding the $10,000 outlay as a negative amount, the project’s NPV is about $1,111.95.

The arithmetic behind the result is $4,000 divided by 1.10, plus $4,500 divided by 1.10 squared, plus $5,000 divided by 1.10 cubed, minus $10,000. Excel performs those discounting steps when it evaluates the function. The displayed number may differ in its last decimal place depending on the cell’s number format, but the underlying calculation uses the stored values.

To see only the present value of the future inflows, use the same layout and enter =NPV(B1,B3:B5). This returns approximately $11,111.95, not the project’s net value after the initial cost. Add all relevant time-zero amounts outside the function to arrive at project NPV.

How to enter the cash flows in Excel

A simple worksheet is easiest to audit when it labels both the timing and the amounts. For example, put the rate in a clearly named cell, list the initial cash flow as period zero, and put each subsequent period on its own row. You can use either a vertical range, as in the example, or a horizontal row. The order of values must match their chronological order.

  1. Enter the discount rate as a decimal or percentage, such as 10% in B1.
  2. Enter the initial investment or other time-zero cash flow separately, with outflows negative and inflows positive.
  3. Enter later cash flows in consecutive periods, keeping the sign convention consistent.
  4. Use NPV on the later-period values, then add the time-zero amount or amounts.
  5. Format the result as currency if appropriate, and retain sufficient decimal precision while checking the calculation.

In Microsoft Excel for Microsoft 365 on Windows, select the result cell and type the formula directly, or select the Formulas tab and use the Financial function list to insert NPV. The formula entry works the same in Excel for the web and on Mac, although ribbon layout and some menu details can vary. No special add-in is required.

Choose a rate and a matching time period

The discount rate and the cash-flow interval must use compatible periods. If the cash flows are annual, use a rate for each year. If they are monthly, use a monthly rate. Applying an annual rate to monthly cash flows discounts each month as though a full year had passed, which can substantially distort the answer.

If B1 contains an effective annual rate of 12% and the cash flows are monthly, you can convert it to an equivalent monthly rate. With the annual rate in B1, enter =(1+B1)^(1/12)-1 in another cell to calculate the monthly rate, approximately 0.9489%. Use that converted rate with monthly cash flows. This conversion assumes the annual figure is an effective annual rate; a nominal rate with a stated compounding convention may need a different conversion.

Choosing an appropriate rate is a modeling judgment, not something Excel can decide. The rate might reflect a required return, a financing cost, or another assumption relevant to the analysis. If the rate changes, NPV can change sharply, especially when much of the expected value comes from distant future cash flows. For a real financing or investment decision, verify the assumptions and terms with the lender or a qualified professional; this example is illustrative, not financial advice.

Regular periods versus irregular dates

Use NPV when the cash flows occur at consistent intervals: for example, every year, quarter, or month. It treats the first value in its arguments as occurring one full period after the valuation date, the second as occurring two periods after it, and so on. A series of annual forecasts remains regular even if a calendar year has 365 or 366 days; NPV counts periods rather than actual elapsed days.

For cash flows that occur on specific, uneven dates, Excel’s XNPV function is usually more suitable. It discounts each amount according to the number of days between its date and the first date in the schedule, using a 365-day year. With the discount rate in B1, cash flows in B2:B5, and their corresponding Excel dates in A2:A5, enter =XNPV(B1,B2:B5,A2:A5). Keep the values and date ranges the same size, and ensure the dates are real Excel dates rather than text that merely looks like a date.

XNPV uses the first date in the date range as its starting point, so a cash flow on that date is not discounted. Unlike the usual NPV setup, the first value can therefore represent a time-zero amount when its date is the valuation date. Check the date schedule and the intended starting point carefully before comparing XNPV with a regular-period NPV calculation.

Common errors and how to check them

The most frequent mistake is putting the initial outlay inside the NPV range. Excel then treats that amount as if it happened one period in the future, discounting it when it should be at time zero. Keep it outside the range for NPV and add it afterward. A related error is using an upfront cost as a positive number; use a negative value for an outflow.

  • Rate entered as 10 instead of 10%: Excel interprets 10 as 1,000%, not 10%. Enter 10% or 0.10.
  • Periods do not match: Do not apply a yearly rate to monthly flows without converting the rate or changing the model’s timing.
  • Cash flows are out of order: Arrange the values chronologically so each amount is discounted for the correct number of periods.
  • Uneven dates are treated as equal periods: Use XNPV when exact dates matter and intervals are irregular.
  • Only inflows are included: Include relevant project outflows as well as inflows, and confirm whether any costs have already been counted elsewhere.

Useful checks include calculating the present value of each period independently in a scratch area, confirming that the initial amount is not discounted, and testing how the result changes when the rate is raised or lowered. For a schedule consisting only of future positive inflows, increasing a positive discount rate should generally reduce their present value. If the result behaves unexpectedly, check the signs, rate units, range, and timing assumptions before trusting it.

NPV compared with IRR and payback

NPV and internal rate of return (IRR) answer different questions. NPV estimates value in currency at a specified discount rate. IRR is the rate that makes a set of cash flows’ NPV equal zero. IRR can be convenient for expressing a return as a percentage, but it can be ambiguous for cash flows that switch between positive and negative more than once, and it does not show the amount of value created. NPV is often easier to compare when projects differ in scale.

Payback asks how long it takes for cumulative cash flows to recover the initial outlay. It is easy to communicate but, in its basic form, ignores the time value of money and cash flows received after the payback point. Discounted payback accounts for discounting but still focuses on a recovery date rather than the total present value. These measures can provide context, but they are not interchangeable with NPV.

Frequently Asked Questions

Does Excel NPV include the initial investment?
No. NPV discounts the first value supplied as occurring one period from now. Add a time-zero investment separately, usually as a negative amount, as in the worked example.

Why is my Excel NPV result different from my expected result?
Check whether the initial cash flow was accidentally included in the function range, whether the rate is entered as a percentage or decimal, and whether the rate interval matches the cash-flow interval. Also confirm that values are in date order and use the intended signs.

Can NPV handle negative cash flows?
Yes. Negative values represent outflows and positive values represent inflows. Include each cash flow in the period where it occurs, while keeping a time-zero amount outside the NPV range in a regular-period model.

Should I use NPV or XNPV in Excel?
Use NPV for cash flows at regular intervals. Use XNPV when you have actual dates and the gaps between cash flows are irregular, or when day-based timing is important to the analysis.

Can I use NPV for monthly cash flows?
Yes, provided you use a monthly discount rate with monthly cash flows. If your available rate is annual, convert it appropriately for the compounding convention rather than applying the annual rate unchanged each month.

Make the model easy to review

Keep assumptions, dates, and cash flows visible instead of burying them inside a long formula. Label the rate and its period, show which row is time zero, and use consistent signs. A short note stating whether the rate is nominal or effective can prevent confusion later. If you revise forecasts, preserve the original assumptions or record the changes so a reader can tell why the NPV moved.

The key rule is simple: Excel NPV discounts a sequence of regular future cash flows, beginning one period from now. Separate any amount received or paid today, choose a rate that matches the interval, and use XNPV when actual dates are uneven. With those choices made explicit, the formula is straightforward and the result is much easier to interpret.

Related

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.