Cash Flow Formula in Excel for Net Present Value (NPV)

Formulas & functions · Updated November 21, 2026

For regular, evenly spaced cash flows, Excel’s net present value formula is =NPV(rate, future_cash_flows)+initial_cash_flow. Put the initial investment outside NPV, because Excel treats the first value inside the function as a cash flow received or paid one period from now, not today.

The Excel NPV formula and what it calculates

Net present value, or NPV, translates future cash flows into today’s money by discounting them at a chosen rate, then accounts for the initial cost. It is useful when comparing a project’s expected receipts and costs over time, provided the cash flows occur at regular intervals such as once a year or once a month.

In Excel, the NPV function takes a discount rate followed by one or more future cash flows. The rate and cash-flow periods need to match: use an annual rate for annual cash flows, for example. The initial amount at time zero is added separately. The formula pattern is:

  • NPV(rate, future cash flows) discounts the future-period values.
  • The initial cash flow is then added without discounting it, because it occurs today.

NPV is a value in currency units, not a percentage return. A positive result means the discounted future flows exceed the initial cost at the selected rate; a negative result means they do not. Those results depend on the assumptions entered, particularly the cash-flow estimates and discount rate.

Set up a simple cash-flow example

Suppose a project costs $10,000 now and is expected to return $4,000 at the end of each of the next three years. Assume a 10% annual discount rate. In an Excel worksheet, enter the initial outflow as a negative number in B1, the three future inflows in B2:B4, and the rate in B6. For example, B1 is -10000, B2:B4 each contain 4000, and B6 contains 10%.

With the initial outflow in B1, future annual flows in B2:B4, and the annual discount rate in B6, enter this formula in another cell:

=NPV(B6,B2:B4)+B1

The result is approximately -$52.59. The calculation discounts the $4,000 receipts by one, two, and three years, respectively, then adds the -$10,000 paid today. Before relying on the result, check that the cash-flow signs are consistent: payments or costs are usually negative, while receipts are positive.

Why the first cash flow is handled separately

The most common NPV mistake is putting the initial investment inside the NPV range. Excel assumes every value supplied to NPV arrives at the end of a future period. It therefore discounts the first value by one period, even if that value is actually a payment made today. The formula may still return a plausible-looking number, which makes this error easy to miss.

For the same example, if B1 contains -10000, B2:B4 contain 4000 each, and B6 contains 10%, this formula incorrectly treats the initial cost as a year-one cash flow:

=NPV(B6,B1:B4)

It returns approximately $856.50 rather than the correctly timed result of -$52.59. The difference is caused by shifting the initial outflow forward by one period. A useful check is to identify the date of every cash flow: if the first cost happens today, add it outside NPV.

Choose a discount rate and matching periods

The discount rate represents the return threshold or cost of capital used in the analysis. Excel does not choose a rate for you, and the correct rate depends on the purpose and assumptions of the analysis. A higher rate reduces the present value of future receipts, while a lower rate increases it. That sensitivity can change whether an NPV appears positive or negative.

Keep the rate’s time unit aligned with the cash-flow schedule:

  • For yearly cash flows, use an annual rate.
  • For monthly cash flows, use a monthly rate that corresponds to the annual assumption. Dividing an annual rate by 12 is a common nominal-rate convention, but it is not interchangeable with every effective annual rate.
  • For quarterly or other regular intervals, convert the rate consistently to that interval.

For instance, entering a 10% annual rate while listing monthly payments makes Excel discount each monthly amount as though a month were a year. The result will be distorted. Write down whether each flow occurs at the beginning or end of a period, too: standard NPV discounts the first listed future flow by one full period, consistent with an end-of-period cash-flow assumption.

When to use XNPV for irregular dates

Cash flows are not always evenly spaced. If receipts or payments happen on specific, irregular dates, Excel’s XNPV function discounts them according to the number of days between dates. It requires a rate, a range of cash-flow values, and a corresponding range of dates. Include the initial outflow as the first value on its actual date; unlike the usual NPV setup, XNPV can include that time-zero cash flow in its values range.

For an illustrative schedule, enter -10000 and then three 4000 inflows in B2:B5. Put their dates in A2:A5 as January 1, 2025; January 1, 2026; January 1, 2027; and January 1, 2028, respectively. Put 10% in B6. With cash flows in B2:B5, matching dates in A2:A5, and the discount rate in B6, use:

=XNPV(B6,B2:B5,A2:A5)

The result is approximately -$52.59. Those dates are exactly 365 days apart, so this example agrees with the regular annual NPV calculation. With dates that are not evenly spaced, XNPV can produce a different and more suitable result. Dates must be valid Excel dates, and the first date anchors the timing of the calculation. XNPV is available in Microsoft 365 for Windows; it is also available in current Excel for Mac and Excel for the web, though interface details can differ.

Interpret the result without overreading it

A positive NPV means the modeled cash flows, discounted at the selected rate, add up to more than the modeled initial outlay. A negative NPV means they add up to less. Zero indicates that the modeled flows meet the selected discount rate exactly, subject to rounding. These are conclusions about the inputs and timing, not guarantees about what a project will actually earn.

It is often helpful to compare alternatives using the same time horizon, cash-flow conventions, and rate assumptions. If one option lasts longer or has a different risk profile, a simple side-by-side NPV may not settle the comparison. You can also test how the result changes when key estimates or the discount rate change. A result only slightly above or below zero deserves particular attention to assumptions and rounding.

Common spreadsheet problems to check

  • Wrong sign: A cost entered as positive instead of negative can make a project look much more attractive. Review each row as an actual payment or receipt.
  • Initial cost discounted by mistake: For ordinary NPV, keep the time-zero amount outside the NPV arguments.
  • Rate and period mismatch: Do not apply an annual rate to monthly periods without converting it appropriately.
  • Missing or misplaced flows: Confirm that each future amount sits in the correct period and that the range includes every intended cash flow.
  • Irregular dates treated as regular: Use XNPV when actual dates are uneven, rather than assuming equal spacing.
  • Percentage entered as a whole number: Enter 10% or 0.10 for a ten-percent rate, not 10, which Excel interprets as 1,000%.

Excel formulas normally use commas between arguments in the syntax shown here. Depending on Windows regional settings, Excel may display or require semicolons instead. The underlying calculation is the same; use the separator accepted by your installation. The formulas work in current Microsoft 365 Excel for Windows and are also supported in current Excel for Mac and the web.

NPV compared with related measures

NPV and internal rate of return answer different questions. NPV reports the discounted value in currency using a rate you supply. The internal rate of return is a rate derived from a cash-flow series, representing the rate that makes its NPV zero under the function’s assumptions. A percentage return can be convenient to communicate, but NPV makes the estimated value contribution more explicit.

Payback period is another related measure: it estimates how long it takes to recover an initial outlay, usually without measuring the value of cash flows after recovery. It is not a substitute for NPV when the timing and value of all modeled flows matter. These measures can complement one another, but each relies on the quality of the forecast. This explanation is for spreadsheet education, not financial, tax, investment, or legal advice; check actual terms with a lender or a qualified professional where relevant.

A practical workflow for an NPV worksheet

  1. List each cash flow in date or period order, and mark payments as negative and receipts as positive.
  2. Choose whether the intervals are regular. Use NPV for equal periods and XNPV when actual dates are irregular.
  3. Set a clearly labeled rate that matches the cash-flow intervals.
  4. Keep the initial amount outside an ordinary NPV formula; for XNPV, include it with its date.
  5. Check the sign, timing, range, and rate, then test how sensitive the result is to uncertain assumptions.

Labeling the rate, period, and initial outflow beside the formula makes a workbook easier to review and update. Avoid burying assumptions inside a long formula when a clearly labeled input cell will do. If the forecast changes, the formula can then recalculate while the assumptions remain visible.

Frequently Asked Questions

Should the initial investment be included in Excel NPV?
For the standard NPV function, no. Put the initial investment outside the function because NPV treats its first listed value as occurring one period in the future. XNPV is different: include the initial cash flow in its values range with its actual date.

Why is my Excel NPV result negative?
A negative result means the discounted future cash flows are less than the initial cost at the rate you entered. Check signs, timing, the discount rate, and the cash-flow estimates before interpreting it.

Can I use NPV for monthly cash flows?
Yes, if the cash flows are evenly spaced monthly and the rate is expressed for a monthly period. If the dates are irregular, consider XNPV instead.

What is the difference between NPV and XNPV in Excel?
NPV assumes equal time intervals and discounts by period. XNPV uses actual dates and discounts based on the days between them, making it appropriate for irregular schedules.

Does Excel NPV include the first future cash flow immediately?
No. The first value in the NPV arguments is discounted by one full period. If a receipt happens today, it is a time-zero cash flow and should not be treated as that first future value.

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.