Excel NPV Function
last modified October 5, 2026
The NPV function calculates the net present value of an investment
from a discount rate and a series of future cash flows. It discounts every cash
flow back to today and adds the results, so a positive value means the
investment earns more than the discount rate. This tutorial covers the syntax,
the period-1 discounting rule, where to place the initial cost, and how
NPV relates to XNPV and IRR.
NPV Function Basics
The NPV function sums the present values of future cash flows. The
discount rate represents the return you could earn elsewhere, so a higher rate
produces a lower present value. The syntax takes the rate first, followed by the
cash flow values.
| Component | Description |
|---|---|
| Function Name | NPV |
| Syntax | =NPV(rate, value1, [value2], ...) |
| Required Arguments | rate, value1 |
| Optional Arguments | value2 ... value254 |
| Return Value | Net present value as a number |
This table breaks down the essential components of the NPV
function. It shows the function name, syntax format, required and optional
arguments, and return value characteristics.
| Argument | Meaning |
|---|---|
| rate | The discount rate per period, entered as a decimal or a percentage,
for example 0.1 or 10%. It must match the period
of the cash flows. |
| value1, value2, ... | One to 254 cash flows, equally spaced in time and occurring at the end of each period. Outflows are negative and inflows are positive. |
The order of the values defines the order of the periods: value1
is period 1, value2 is period 2, and so on. Empty cells, text, and
logical values inside a referenced range are ignored.
The Period-1 Discounting Rule
The most important detail of NPV is that it discounts the first
value by one period, not zero. Excel treats value1 as occurring at
the end of period 1, so the underlying formula is:
NPV = value1/(1+rate)^1 + value2/(1+rate)^2 + ... + valuen/(1+rate)^n
Because the exponent starts at 1, an initial investment made today (time 0) must not be placed inside the list. Doing so discounts it once and understates the cost. Add it outside the function instead:
=NPV(0.1, 3000, 4200, 6800) - 10000 ' correct, cost paid at time 0 =NPV(0.1, -10000, 3000, 4200, 6800) ' wrong, cost discounted one period
The first formula returns $1,307.29 and treats the $10,000 as spent today. The
second returns $1,188.44 because the cost is discounted by one year, as if it
were paid a year from now. The gap between the two is exactly
10000/1.1. Use the second form only when the cost really is paid one
period from today, which is the case in Microsoft's own documentation example.
Basic NPV Example
This example demonstrates the simplest use of the NPV function with a discount rate and three future cash flows.
=NPV(0.1, 1000, 2000, 3000)
This formula calculates NPV with a 10% discount rate and three cash flows. The result is $4,815.93. The 1,000 is discounted one period, the 2,000 two periods, and the 3,000 three periods, then the three present values are summed.
NPV with Initial Investment
A common application calculates NPV including an initial investment. This requires adding the initial cost outside the NPV function.
| A | B |
|---|---|
| -5000 | Initial Investment |
| 1000 | Year 1 Cash Flow |
| 2000 | Year 2 Cash Flow |
| 3000 | Year 3 Cash Flow |
| =A1 + NPV(0.1, A2:A4) |
The table shows a typical investment scenario with an initial outlay in A1 and
future cash flows in A2:A4. Because the outlay happens at time 0, it stays out
of the range passed to NPV and is added to the result.
=A1 + NPV(0.1, A2:A4)
This formula adds the initial investment (-$5,000) to the NPV of future cash flows. The result is -$184.07, indicating a slightly negative net present value at 10% discount rate.
NPV with Uneven Cash Flows
NPV handles uneven cash flow patterns effectively. This example shows NPV with varying positive and negative cash flows.
| A | B |
|---|---|
| -10000 | Initial Investment |
| 5000 | Year 1 |
| -2000 | Year 2 |
| 8000 | Year 3 |
| 4000 | Year 4 |
| =A1 + NPV(0.12, A2:A5) |
This table demonstrates NPV calculation with an initial investment and uneven future cash flows, including a negative cash flow in Year 2.
=A1 + NPV(0.12, A2:A5)
The formula evaluates an investment with a 12% discount rate. The result is $1,106.21, indicating a positive NPV. The negative cash flow in Year 2 is discounted like any other value and handled naturally in the calculation.
NPV with Monthly Cash Flows
For monthly cash flows, adjust the discount rate to a monthly equivalent. This example shows proper rate conversion.
| A | B |
|---|---|
| -5000 | Initial Investment |
| 500 | Month 1 |
| 600 | Month 2 |
| 700 | Month 3 |
| =A1 + NPV(0.1/12, A2:A4) |
The table illustrates NPV calculation for monthly cash flows. The annual 10% rate is divided by 12 to get the monthly discount rate.
=A1 + NPV(0.1/12, A2:A4)
This formula calculates NPV for monthly cash flows using a monthly discount rate (10%/12). The result is -$3,231.22. Always match the rate period to the cash flow frequency: dividing the annual rate by 12 keeps the units consistent with the monthly values.
NPV vs. IRR Comparison
This example compares NPV with IRR (Internal Rate of Return) for decision making. Both metrics help evaluate investments but provide different insights.
| A | B | C |
|---|---|---|
| -10000 | Initial | |
| 3000 | Year 1 | |
| 4000 | Year 2 | |
| 5000 | Year 3 | |
| NPV (10%) | =A1 + NPV(0.1, A2:A4) | |
| IRR | =IRR(A1:A4) |
The table shows both NPV and IRR calculations for the same cash flows. NPV uses a 10% discount rate while IRR calculates the breakeven rate.
=A1 + NPV(0.1, A2:A4) =IRR(A1:A4)
The NPV formula returns -$210.37, a negative value at 10%. The IRR formula returns 8.90%, the rate at which NPV equals zero. Because the IRR is below the 10% discount rate, the project destroys value, and the two measures agree. Replacing 0.1 in the NPV formula with the IRR would drive the result to zero.
NPV vs XNPV vs IRR
The three functions answer related questions. NPV values a stream
of equally spaced cash flows at a rate you supply, XNPV does the
same for cash flows that fall on specific dates, and IRR finds the
rate that makes the net present value zero.
| Feature | NPV | XNPV | IRR |
|---|---|---|---|
| Purpose | Present value at a given rate | Present value with dated flows | Rate where NPV equals zero |
| Timing | Equal periods | Specific dates | Equal periods |
| Syntax | =NPV(rate, values) | =XNPV(rate, values, dates) | =IRR(values, [guess]) |
| Result | Currency amount | Currency amount | Percentage |
| First cash flow | Discounted one period | First date is time 0 | First value is period 0 |
Note the difference in the first cash flow. XNPV and
IRR both treat the first value as occurring today, while
NPV discounts it by one period. That is why a time-0 cost is added
outside NPV but included directly in the XNPV and
IRR value lists.
NPV with Growing Cash Flows
For cash flows growing at a constant rate, combine NPV with growth calculations. This example models a stream of growing cash flows.
=NPV(0.1, 1000, 1000*1.05, 1000*1.05^2, 1000*1.05^3)
This formula calculates NPV for cash flows growing at 5% annually, discounted at 10%. The result is $3,395.86. Because the growth rate is below the discount rate, the present values still shrink each period. Growth rates can be incorporated directly into cash flow projections this way.
NPV Errors and Troubleshooting
Most NPV mistakes come from the period-1 rule or from mismatched
rate and cash flow periods.
| Cause | Symptom | Fix |
|---|---|---|
| Initial cost placed inside the value list | NPV too high; the cost is discounted one period | Add the time-0 cost outside NPV |
| Annual rate used with monthly cash flows | NPV far too small | Convert the rate, for example 10%/12 for monthly flows |
| Cash flows are not equally spaced | Misleading result | Use XNPV with matching dates |
| rate is -100% (-1) | #DIV/0! error | Use a rate above -100% |
| rate is text | #VALUE! error | Make sure the rate is a number |
| Referenced range contains text or blank cells | Those cells are ignored, which can shift later periods | Keep the range numeric and contiguous |
| Values entered out of period order | Wrong result | Enter cash flows in chronological order |
When a result looks wrong, check the first value first: it is the single most common source of error. Confirm that the discount rate and the cash flow periods share the same unit, then verify the order of the flows.
The NPV function is essential for financial analysis in Excel.
From simple investment appraisal to detailed cash flow models, it turns a
stream of future payments into a single value that can be compared with a
required return. Remember the period-1 discounting rule, add the initial cost
outside the function, and match the rate to the cash flow period. When the
dates are irregular, switch to XNPV, and pair NPV
with IRR to see both the amount of value created and the rate that
creates it. Mastering these details makes NPV a dependable
decision tool.
Author
List all Excel Formulas.