To calculate net present value in Excel, place the discount rate in B1, the initial investment in B2, and the later cash flows in B3:B5; then enter =NPV(B1,B3:B5)+B2 in another cell. With the example cash flows of an initial outlay of $10,000 followed by receipts of $3,000, $4,000, and $5,000, and a 10% discount rate, the result is a negative NPV of about $210.37.
What NPV Measures
Net present value, or NPV, expresses a series of future cash flows in today’s money after discounting them at a selected rate. A dollar received later is worth less than a dollar received now because money available today could be put to another use, and because future cash flows are uncertain. The discount rate represents the return required to account for timing and risk in the analysis.
NPV is useful for evaluating a project, purchase, or other series of expected cash flows. A positive result means the discounted inflows exceed the outflows included in the calculation. A negative result means the discounted outflows exceed the inflows. A result near zero means the cash flows approximately meet the return represented by the discount rate. These are decision signals, not guarantees: a calculation is only as sound as its cash-flow estimates and assumptions.
Set Up the Cash-Flow Worksheet
Before entering a function, put the cash flows in a clear, consistent timeline. For a basic annual analysis, a small table can look like this:
- B1: discount rate, entered as 10%.
- B2: initial investment at the start, entered as -10000.
- B3: cash flow at the end of year 1, entered as 3000.
- B4: cash flow at the end of year 2, entered as 4000.
- B5: cash flow at the end of year 3, entered as 5000.
Enter investments or other payments as negative values and receipts as positive values. This sign convention makes it easier to interpret the result and is also important when comparing NPV with Excel’s IRR function. Use the same currency and scale throughout: do not enter one cash flow in dollars and another in thousands of dollars. Label the periods so that it is clear whether each amount occurs now or at the end of a year.
Calculate NPV with Excel’s NPV Function
Excel’s NPV function discounts the values supplied to it as a sequence of future, equally spaced cash flows. The first value supplied is treated as occurring at the end of period 1, not at the present date. For that reason, a cash flow at time zero, such as an upfront purchase price, is normally left out of the function’s range and added separately.
With the worksheet layout above—rate in B1, initial outflow in B2, and year 1 through year 3 cash flows in B3:B5—enter =NPV(B1,B3:B5)+B2. Excel discounts the three later receipts and then adds the immediate $10,000 outflow. The discounted future receipts total about $9,789.63, so the complete project NPV is about -$210.37.
To calculate only the present value of the future receipts, use the same layout and enter =NPV(B1,B3:B5). That returns about $9,789.63. It is not the project’s complete NPV until the initial investment is included. Keeping the initial amount in a separate cell also makes the timing assumption visible and easier to audit.
Understand the Example and Its Timing
The example’s cash flows are assumed to arrive at the end of each year. At a 10% discount rate, Excel reduces the year 1 receipt to about $2,727.27 in present-value terms, the year 2 receipt to about $3,305.79, and the year 3 receipt to about $3,756.57. Together they are worth about $9,789.63 today. Subtracting the $10,000 paid today gives the negative NPV of roughly $210.37.
Timing is not a cosmetic detail. If a receipt actually arrives at the beginning of a year, discounting it as though it arrives at year-end understates its present value. Likewise, if cash flows occur monthly, quarterly, or on irregular dates, an annual NPV calculation may not match their actual timing. Choose a period that matches the cash-flow schedule, and make the rate consistent with that period. For monthly cash flows, for example, use a monthly rate rather than entering an annual rate unchanged.
Choose and Enter the Discount Rate Carefully
The rate is an assumption, not something Excel determines from the cash flows when using NPV. Depending on the analysis, it may represent a required return, a financing or opportunity cost, or another rate appropriate to the decision. The right choice depends on the purpose and circumstances of the analysis; Excel cannot decide whether a particular rate is suitable.
Enter a percentage correctly. A cell formatted as 10% stores a value of 0.10, which is the intended input for a ten-percent rate. Typing 10 into a general-format cell instead means a rate of 10, or 1,000%, not 10%. Confirm the displayed value and cell format before interpreting the output. Also make sure the rate is expressed over the same length of time as each cash-flow period.
For a decision that depends on uncertain estimates, it can be useful to calculate NPV under more than one plausible rate or set of cash flows. This is sensitivity analysis: it shows how the result changes when an assumption changes. It does not remove uncertainty or establish which assumptions are correct, but it can reveal whether a conclusion depends heavily on a small change in the rate or forecast.
Use XNPV for Cash Flows on Actual Dates
Excel’s NPV function assumes regular intervals. When cash flows happen on specific, uneven dates, use XNPV instead. XNPV discounts each amount according to the number of days between its date and the first cash-flow date, using a 365-day year. Include the initial cash flow as the first value and give every value a corresponding valid Excel date.
For an illustration, put the rate of 10% in B1, the initial outflow of -10000 and the three receipts of 3000, 4000, and 5000 in B2:B5, and the corresponding actual dates in D2:D5. If those dates are January 1 of 2025, 2026, 2027, and 2028, enter =XNPV(B1,B2:B5,D2:D5). Because these dates are exactly one, two, and three 365-day years after the first date, the result is about -$210.37, subject to Excel’s displayed rounding.
Use actual date values in the date range, not text that merely looks like a date. The first date anchors the timing calculation, so the first value is treated as occurring on that date. If the dates are irregular, XNPV can produce a different result from NPV even when the cash-flow amounts are identical. Check the date entries and the intended day-count convention when reproducing or comparing a calculation.
Common NPV Errors and How to Check Them
A frequent mistake is including the time-zero investment inside the NPV function’s value range. With the example layout—rate in B1 and all cash flows, including the initial -10000, in B2:B5—=NPV(B1,B2:B5) returns about $698.72. That result is wrong for the stated timing because it discounts the initial outlay by one period. Use =NPV(B1,B3:B5)+B2 instead when B2 is paid immediately.
- Check the signs: investments and costs are generally negative; receipts are positive. Reversing signs can reverse the interpretation.
- Check the rate: confirm that 10% is stored as 0.10, not 10, and that the rate period matches the cash-flow period.
- Check the timeline: verify that the first value in the NPV range really occurs one full period from now.
- Check the range: make sure no cash flow has been omitted or included twice, and that the initial amount is treated separately when it occurs today.
- Check the units: use consistent currencies, signs, and scale, and do not mix annual and monthly amounts without adjusting the rate and timeline.
If Excel returns an error or an unexpected value, inspect the referenced cells and their contents. The discount rate must be greater than -100% for the usual NPV calculation to make mathematical sense. For XNPV, verify that the values and dates ranges contain the same number of entries and that the dates are valid Excel dates. For a quick independent check, calculate the present value of each future cash flow separately and compare their sum with the function result.
Interpret NPV Alongside Other Measures
NPV answers a useful question: after applying the selected discount rate, how much value remains in today’s currency after the included costs and receipts? It does not tell the whole story. A project with a higher NPV may require a much larger initial outlay, take longer to return cash, or depend on less certain forecasts than an alternative. Consider scale, timing, risk, and practical constraints as well as the total.
Internal rate of return, or IRR, is a related measure that estimates the rate at which the NPV of a series of cash flows equals zero. Unlike NPV, it is a percentage, so it can be easy to compare with a required return. However, unusual patterns of alternating inflows and outflows can make IRR difficult to interpret or produce multiple possible rates. Payback period focuses on how long it takes to recover the initial outlay, but it does not by itself account for the time value of money or cash flows after the payback point. These measures answer different questions; none should be treated as a substitute for understanding the cash-flow assumptions.
For budgeting, lending, or other financial decisions, this example is illustrative rather than advice. Real terms, taxes, fees, and risks can change the appropriate inputs and conclusions. Check actual terms with the lender or a qualified professional when those details matter.
Frequently Asked Questions
Should the initial investment be included in Excel’s NPV range?
No, not when it occurs immediately at time zero. Keep it outside the NPV range and add it to the function result. Excel treats the first value inside NPV as a cash flow at the end of period 1.
Why is my Excel NPV different from the sum of my cash flows?
NPV discounts later amounts, so it generally differs from an undiscounted total. Check the discount rate, timing, and whether the upfront amount has been handled separately. If your cash flows have irregular dates, compare using XNPV rather than regular NPV.
Can I use NPV for monthly cash flows?
Yes, if the cash flows occur at regular monthly intervals and you use a monthly discount rate. Keep the initial time-zero amount outside the NPV range. Do not use an annual rate unchanged for monthly periods.
What does a negative NPV mean?
It means the present value of the included receipts is less than the present value of the outflows at the chosen discount rate. It does not automatically establish that a decision is wrong; the rate, forecast, timing, and purpose of the analysis all matter.
When should I use XNPV instead of NPV?
Use XNPV when cash flows occur on actual dates that are not equally spaced. Use NPV for a regular sequence of periods, such as cash flows at each year-end, with a rate matched to those periods.
Related
- Best Free Excel Budget Templates for Monthly Planning
- Basic Project Timeline Template in Excel: A Simple Guide
- Basic Sum in Excel: How to Add Cells and Numbers
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS Multiple Criteria Range ExplainedThe direct answer: use =COUNTIFS(range1, criteria1, range2, criteria2, ...) where each range must be the same size and…
- Excel Power Query Table Filter: How to Apply and RefreshTo filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the…
- Round Numbers in Word Mail Merge: A Step-by-Step GuideTo round numbers in a Word mail merge, add a numeric picture switch to the merge field, such…
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
- Bing Chat Excel Formula Not Updating? Fixes That WorkIf a formula from Bing Chat is not updating in Excel, first check that it was entered as…
- Excel Round Up to Dollar: 4 Easy WaysTo round a number up to the nearest whole dollar in Excel, use the ROUNDUP function with 0…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.