Using NPV in Excel: Formula, Examples, and Timing Guide

Formulas & functions · Updated November 20, 2026

To calculate net present value in Excel, use the NPV function for future cash flows, then add the initial investment separately. With the initial outflow of -$10,000 in B2, future inflows of $4,500 in B3:B5, and a 10% discount rate in D1, enter =NPV(D1,B3:B5)+B2; the result is approximately $1,190.83.

What NPV Means

Net present value, usually shortened to NPV, expresses a series of future cash flows in today’s dollars after discounting them at a chosen rate. A dollar received in the future is worth less than a dollar received now because money available today can be invested, and because future receipts carry uncertainty. The discount rate represents the return required for the analysis or the cost of tying up money, according to the assumptions chosen for the project.

NPV brings the discounted value of incoming and outgoing cash flows together. A positive result means the discounted inflows exceed the outflows included in the calculation; a negative result means they fall short. A result of zero is the break-even point at the selected discount rate. These interpretations depend on the cash-flow estimates and rate being appropriate, so an NPV result is not a guarantee of an outcome.

Set Up the Cash-Flow Example

Start with one row or column for each cash-flow period. For this example, the initial investment happens today, followed by three equal annual inflows. Put the rate in a separate cell so it is easy to change and test different assumptions.

  • Enter -10000 in B2 for the investment made today.
  • Enter 4500 in each of B3, B4, and B5 for the inflows at the end of years one, two, and three.
  • Enter 10% in D1 as the annual discount rate.
  • Use labels in column A, such as Initial investment, Year 1, Year 2, and Year 3, to make the timeline clear.

Cash paid out is entered as a negative number and cash received as a positive number. Using consistent signs makes the result easier to read and helps avoid accidentally treating an investment as income. In an actual model, use the amounts and timing relevant to the case being analyzed rather than assuming equal annual receipts.

Enter the NPV Formula

With the initial investment in B2, the three future cash flows in B3:B5, and the discount rate in D1, enter =NPV(D1,B3:B5)+B2 in an empty cell. The formula returns approximately $1,190.83. Excel discounts the $4,500 receipts at the ends of years one, two, and three, totaling about $11,190.83 in present value, and then includes the $10,000 investment made today.

Excel’s NPV function is written as NPV(rate, value1, [value2], …). The rate is the discount rate for each period, and the value arguments are the cash flows to discount. The square brackets in Excel’s function description indicate an optional argument; they are not typed around a range in the formula. A single contiguous range, such as B3:B5, is usually convenient, though the function can also accept multiple value arguments.

The important detail is that Excel treats the first value supplied to NPV as a cash flow at the end of period one, not at the valuation date. That is why the initial investment at time zero is outside the NPV range in this example and is added separately. The plus sign in the formula adds B2, which contains a negative amount.

Choose a Discount Rate That Matches the Period

The rate and cash-flow spacing must use the same period. If the cash flows are annual, enter an annual discount rate. If they are monthly, use a monthly rate and put each month’s cash flow in the corresponding period. Applying an annual rate to monthly cash flows would discount them too heavily if Excel were meant to treat every month as a full year.

Be clear about what a stated annual rate means. A nominal annual rate divided by 12 and an effective annual rate converted to a monthly rate are not necessarily equivalent. Use a rate consistent with the convention behind the assumptions, and document that convention in the worksheet. If cash flows occur quarterly, use a quarterly rate; if timing is irregular, consider XNPV instead of forcing the amounts into equal periods.

It is useful to test more than one reasonable rate when the answer is sensitive to this choice. For example, a higher discount rate reduces the present value of later receipts more than it reduces the value of near-term receipts. That sensitivity does not make one rate automatically correct; it shows how much the conclusion depends on the rate assumption.

Use XNPV for Irregular Dates

NPV assumes equal time intervals between cash flows. If receipts arrive on specific dates that are not evenly spaced, Excel’s XNPV function discounts them according to the dates supplied. This can be a better fit for project schedules with partial years, uneven payment dates, or an initial payment made on a date other than the start of a standard period.

For an equivalent date-based version of the example, put the dates 1 January 2025, 1 January 2026, 1 January 2027, and 1 January 2028 in A2:A5. Put -10000, 4500, 4500, and 4500 in B2:B5, and keep the 10% rate in D1. With those dates, cash flows, and rate in those cells, enter =XNPV(D1,B2:B5,A2:A5). The result is approximately $1,190.83. XNPV uses the first date as the valuation date and discounts the later cash flows based on their number of days from it, using a 365-day year. Dates should be real Excel date values, not text that merely looks like a date.

When the cash flows follow exact, equally spaced periods, ordinary NPV is usually simpler. XNPV does not make the estimates more accurate; it changes how the timing is represented. If you use XNPV, check that each amount is paired with its intended date and that the dates are in the correct order.

Interpret the Result and Compare Alternatives

For the example, a positive NPV of about $1,190.83 means the modeled inflows exceed the initial cost by that amount in present-value terms at the selected 10% rate. It does not mean the project produces exactly $1,190.83 in cash profit, nor does it say what the actual future receipts will be. It is a comparison of estimated cash flows after applying the discount-rate assumption.

NPV can help compare alternatives when each option is evaluated using a consistent rate, time horizon, and treatment of cash flows. A larger NPV indicates more present value under those shared assumptions, but comparisons can be misleading if one option has a different risk profile, useful life, or scale. Include relevant cash flows across the same basis and review the assumptions behind the estimates.

IRR is another Excel measure often considered alongside NPV. It estimates the rate at which a cash-flow series has an NPV of zero. NPV answers how much value remains at a specified rate; IRR gives a rate implied by the cash flows. They answer different questions, and IRR can be harder to interpret when cash flows change sign more than once or when projects differ in size. For a decision based on a required return, NPV directly applies that chosen rate.

Common NPV Errors to Avoid

  • Including the initial cost as the first NPV value: NPV treats the first listed value as arriving at the end of the first period. Add a time-zero investment separately, as in the worked example.
  • Mixing periods and rates: Match monthly, quarterly, or annual cash flows to a rate for the same period.
  • Reversing cash-flow signs: Enter payments as negative amounts and receipts as positive amounts. Check the sign of each amount before interpreting the result.
  • Using NPV for uneven dates: NPV assumes regular periods. Use XNPV when the actual dates matter and are irregular.
  • Leaving out cash flows: Check that the selected range includes every relevant receipt and payment in the chosen analysis period, without including headings or unrelated numbers.
  • Assuming a positive value guarantees success: The calculation depends on estimates and a chosen rate. Review those assumptions and consider how the result changes when they vary.

If a result looks unexpectedly small or large, first inspect the rate cell, the values included in the range, and the timing convention. A common diagnostic is to calculate the present value of future receipts separately from the initial outlay, then verify that the net result is their sum. Also confirm that percentage values are stored as percentages or decimals correctly: 10% in Excel is numerically equivalent to 0.1.

Frequently Asked Questions

Does Excel NPV include the initial investment?
No. Excel discounts the first value supplied as a cash flow at the end of period one. If the initial investment occurs today, keep it outside the NPV range and add it separately, using its negative sign.

Why is my NPV result different from an online calculator?
Check whether the other calculator treats the initial investment as occurring today or at the end of the first period, and whether it uses the same rate, cash-flow dates, signs, and rounding. A different timing convention can change the result even when the cash amounts match.

Can I use NPV with monthly cash flows?
Yes, provided the cash flows are equally spaced by month and the rate is a monthly discount rate. Use one value for each month, including zero values for months with no cash flow if needed to preserve the timeline.

What is the difference between NPV and XNPV in Excel?
NPV discounts values at regular, equal intervals. XNPV uses a series of actual dates, which makes it suitable when the intervals are uneven or exact payment dates matter. Both depend on the cash-flow assumptions and discount rate.

Can NPV be negative?
Yes. A negative NPV means that, at the rate entered, the discounted cash inflows are less than the cash outflows included in the calculation. It is a result under those assumptions, not a prediction that the project will necessarily lose that amount in cash.

Should I use NPV or IRR?
Use NPV when you want to assess cash flows at a specified discount rate. IRR estimates the rate that makes NPV zero. They can complement one another, but NPV is the more direct measure when a required rate is already established.

These calculations are illustrative spreadsheet methods, not financial, tax, investment, or legal advice. For a real decision, verify the cash-flow estimates and discount-rate assumptions, and consult a qualified professional where appropriate.

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.