To calculate net present value in Excel, enter the discount rate in one cell, put the initial investment and later cash flows in separate cells, then use =NPV(D1,B3:B5)+B2 when D1 is the rate, B2 is the time-zero investment, and B3:B5 are end-of-period cash flows. Excel’s NPV function discounts the later cash flows, so the initial investment is added separately.
That timing detail is the most important part of an NPV analysis in Excel. The function is straightforward once the cash-flow schedule is arranged correctly, but including the initial outlay inside the wrong range can change the answer substantially. The sections below explain the setup, interpretation, alternatives, and common checks.
What NPV means
Net present value, or NPV, compares the present value of expected future cash inflows and outflows with the amount invested today. A dollar received in the future is worth less than a dollar available now when the discount rate is positive, because money available today can earn a return and because future cash flows involve uncertainty. NPV expresses the discounted result in today’s currency.
A positive NPV means that, under the cash-flow estimates and discount rate used, discounted inflows exceed discounted outflows. A negative NPV means they do not. An NPV of zero means the cash flows exactly meet the return represented by the chosen discount rate. These are conclusions about the assumptions in the spreadsheet, not guarantees about what will happen.
Organizations use NPV to assess projects, equipment purchases, business initiatives, and other choices that involve money now in exchange for expected cash flows later. It is useful because it considers both the size and timing of cash flows and reports the result as a currency amount. The method itself does not determine whether the forecast or discount rate is realistic.
Arrange the cash-flow schedule
In Microsoft Excel for Microsoft 365 on Windows, a simple schedule can use column A for the period and column B for cash flow. Put the initial investment in period 0, then list each later period in order. Put the discount rate in a separate cell, such as D1. For example:
- A2 contains 0 and B2 contains -1000, representing an initial investment of 1,000.
- A3:A5 contain 1, 2, and 3; B3:B5 each contain 400, representing expected receipts at the end of each year.
- D1 contains 10%, the illustrative annual discount rate.
Use negative numbers for cash paid out and positive numbers for cash received. Keep periods consistent: if the cash flows are annual, the rate should be an annual rate; if the schedule is monthly, the periods and rate need to correspond to monthly timing. Labels and currency formatting make the model easier to audit, but the NPV calculation uses the numeric cell values.
Calculate NPV with Excel’s NPV function
Excel’s NPV function discounts a series of cash flows assumed to occur at regular intervals, with the first value in its argument treated as a cash flow at the end of period 1. It does not treat the first value as occurring today. That is why a time-zero investment normally sits outside the function’s values range.
With the example layout above—discount rate in D1, initial investment in B2, and period 1 through period 3 cash flows in B3:B5—enter this formula in a result cell:
=NPV(D1,B3:B5)+B2
The result is approximately -5.26. The NPV of the three future receipts alone is about 994.74; adding the 1,000 initial outlay gives -5.26. The negative result is small relative to the investment, but it is still below zero at a 10% discount rate. Displaying the result to two decimal places is a formatting choice; Excel retains more precision internally.
For a simple schedule with a single initial cash flow, another valid arrangement is to keep the initial investment in its own cell and add it to the NPV result, as above. Avoid putting the time-zero amount into the NPV range and then adding it again, which would count it twice.
Step-by-step in Excel
- Enter the initial cash flow as a negative amount in the period 0 row.
- Enter each later expected cash flow in a separate row, ordered by period, and make sure the intervals are equal.
- Enter the discount rate in a clearly labeled cell. Apply Percentage formatting if useful.
- Select an empty result cell and enter the NPV formula, using the rate cell and only the future cash-flow cells inside NPV.
- Add the initial cash flow cell outside the NPV function, then press Enter.
- Check the formula range against the schedule and confirm that the result is displayed in the expected currency.
For the example, the formula result can be checked independently: 400 divided by 1.10 is about 363.64; 400 divided by 1.10 squared is about 330.58; and 400 divided by 1.10 cubed is about 300.53. Those present values total about 994.74. Subtracting the initial 1,000 gives approximately -5.26. This check helps catch a misplaced cash flow or an incorrect discount rate reference.
When to use XNPV instead
Standard NPV assumes equally spaced cash flows. That is appropriate for annual forecasts with one cash flow at each year-end, or for another regular schedule whose periods and rate are matched. If receipts or payments occur on specific, uneven dates, Excel’s XNPV function is usually a better fit because it uses the dates supplied in the model.
With dates in A2:A5, cash flows—including the initial amount—in B2:B5, and the annual rate in D1, enter this formula in a result cell:
=XNPV(D1,B2:B5,A2:A5)
For this formula to represent the example correctly, A2 should be the initial cash-flow date, B2 should be -1000, and the later rows should contain the dated receipts of 400. XNPV discounts each cash flow according to the number of days from the first date, using a 365-day year. Use actual Excel date values, not text that merely looks like a date, and check that each cash flow is paired with its correct date.
Do not use XNPV simply because dates appear in the sheet. If the cash flows are deliberately modeled at regular period ends, standard NPV may be clearer. The choice is about timing assumptions, not which function is more sophisticated.
How to interpret the result
NPV is not a percentage return. It is a currency amount representing the value left over after discounting the modeled cash flows at the selected rate. In the example, an NPV near -5.26 says that the forecast cash flows fall short of a 10% required return by about 5.26 in the same currency units as the investment.
A positive NPV can be a useful signal that a project clears the chosen return threshold under the model’s assumptions. When comparing alternatives, calculate them using consistent timing, currency, and discount-rate assumptions. If projects differ greatly in scale, a larger NPV may reflect a larger project rather than a better percentage return. NPV alone also does not show how quickly cash is recovered, how much funding is needed at each date, or how uncertain the estimates are.
The discount rate is an input, not an output of the NPV calculation. It should reflect the purpose and risk context of the analysis. A small change in the rate can alter the result, especially when substantial cash flows arrive far in the future. For real financial decisions, verify the assumptions and terms with the relevant organization, lender, or a qualified professional; a spreadsheet calculation is not financial advice.
Common mistakes and checks
- Including the initial investment in NPV incorrectly: Standard NPV treats its first listed value as occurring one period from now. Put a time-zero investment outside the function and add it separately.
- Counting the initial investment twice: If the formula already adds the initial cell separately, do not also include that same cell in the NPV range.
- Mixing period lengths and rates: Annual cash flows discounted using a monthly rate, or monthly flows discounted with an annual rate, produce a misleading result. Match the rate to the interval.
- Using uneven dates with regular-period NPV: If timing varies materially, consider XNPV and provide the actual dates.
- Using the wrong sign: An investment paid out is usually negative and money received is positive. Reversing the signs reverses the interpretation.
- Confusing zero with a blank: A zero cash flow explicitly records no amount in a period. A blank may be ignored in some formula contexts and can make the schedule harder to review, so use a consistent convention.
- Assuming the answer proves the forecast: Excel calculates from the values provided; it does not validate estimates, inflation assumptions, taxes, or project risks.
For a quick audit, trace the formula’s referenced cells, verify the first future cash flow is discounted for one period, and compare the result with a hand calculation for a small example. You can also change the discount rate temporarily to see whether the result responds in a sensible direction. For positive future cash flows, a higher discount rate should generally reduce their present value.
NPV, IRR, and payback compared
NPV and internal rate of return (IRR) answer different questions. NPV gives a currency value at a discount rate selected by the analyst. IRR estimates the rate at which the NPV of a cash-flow series is zero. A project can therefore have an NPV at a chosen hurdle rate and an IRR as a separate measure. IRR can be harder to interpret when cash flows change sign more than once, and it does not replace examining the cash-flow amounts and timing.
Payback period estimates how long it takes to recover an initial outlay. It can be useful for understanding liquidity or exposure, but simple payback does not account for the time value of money, and it can ignore cash flows after the recovery point. Discounted payback accounts for discounting but still focuses on recovery time rather than the total value represented by NPV. These measures can complement one another rather than serving as interchangeable answers.
Frequently Asked Questions
Should the initial investment be included in Excel NPV?
Usually, keep a time-zero investment outside the NPV function and add it to the function’s result. Excel NPV treats the first value in its range as a period-one cash flow, not a payment made today.
Why does Excel NPV give a different result than my manual calculation?
Check whether the initial amount was included or excluded consistently, whether the rate is entered as a percentage such as 10%, and whether the cash-flow range and period intervals are correct. Also check for sign errors and rounding in the manual calculation.
Can Excel NPV handle monthly cash flows?
Yes, if the cash flows are equally spaced monthly and the discount rate is expressed for the matching monthly period. If you start with an annual effective rate, convert it appropriately rather than simply assuming that the annual percentage is the monthly rate.
When should I use XNPV instead of NPV?
Use XNPV when cash flows occur on specific dates that are not evenly spaced, such as irregular project payments. Use NPV for regularly spaced periods when its equal-interval assumption matches the schedule.
Can NPV be negative even if a project makes money?
Yes. A project can have positive cash receipts and still have negative NPV if those receipts, after discounting at the selected rate, are smaller than the initial and later outflows. NPV evaluates value relative to the chosen discount rate, not whether any cash is received at all.
Related
- How to Add Headers in Excel on SharePoint and Format Them
- Changing Date Format in Excel: Steps, Options, and Fixes
- How to Add the Same Header in Excel on Every Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for the Larger of Two NumbersTo return the larger of two numbers in Excel, use the MAX function. With the values in A1…
- Excel FLOOR Function: How to Round Decimal Values DownTo round a decimal down in Excel, use FLOOR.MATH with the increment you want. With a value of…
- SUMIF Excel Examples With Two Criteria to Match RowsTo total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8,…
- VLOOKUP Showing Formula? Causes and Fixes in ExcelIf VLOOKUP is showing as text instead of returning a result, first turn off Formulas > Show Formulas;…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
- Find Unique Names in Excel: 3 Reliable MethodsTo find a list of distinct names in Excel, enter =UNIQUE(A2:A100) in an empty cell if you use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.