Excel NPV Function

NPV discounts a series of equally spaced future cash flows and adds their present values.

Use it to compare a project’s future receipts and expenses with its cost today. The future amounts can vary from period to period.

1
Set the discount rate Use a constant discount rate per cash-flow period.
2
Enter the future cash flows List equally spaced amounts in chronological order. The first amount occurs at the end of period 1, the next at the end of period 2, and so on.
3
Calculate their value today NPV discounts each amount by its period number and adds the signed present values.

NPV syntax & arguments

Syntax

=NPV(rate, value1, [value2], ...)
Required Optional
  1. 1

    rate

    Required

    The discount rate per cash-flow period. Use an annual rate for annual cash flows or a monthly rate for monthly cash flows. To convert an annual rate to a monthly rate, divide by 12 if it is a nominal annual rate compounded monthly. If it is an effective annual rate, use (1+annual_rate)^(1/12)-1 to find the equivalent monthly rate.

  2. 2

    value1

    Required

    The first future cash flow or range of future cash flows, in chronological order. The first value occurs at the end of period 1. Use negatives for outflows and positives for inflows.

  3. 3

    value2

    Optional Repeatable

    Additional future cash flows or ranges, continuing the same equally spaced timeline.

Example

=NPV(10%,1100,1210)

Return the present value of 1100 received at the end of year 1 and 1210 received at the end of year 2, given a constant annual discount rate of 10%.

NPV caveats

  • What positive and negative amounts mean

    Positive cash flows are money you receive; negative cash flows are money you pay out. NPV adds their signed present values. Unlike PV, it does not reverse the sign to return a balancing cash flow.

  • The first value is in the future

    Add today’s signed cash flow to the NPV result. For example, =NPV(10%,1100,1210)-1500 returns 500 for receipts of 1100 after one year and 1210 after two years, with a cost of 1500 paid today.

    Putting today’s cost first inside NPV would incorrectly discount it by one period and shift both later receipts one period forward.

  • Empty cells do not preserve a period

    Text and empty cells in a referenced range are ignored. Enter a numeric 0 for a period with no cash flow so later amounts keep their correct dates.

  • Cash flows must be equally spaced

    NPV uses each amount’s position to determine its timing and has no date argument. Cash flows must occur at equal intervals, such as each month or each year. Irregular dates require a date-aware calculation.

  • Rate and cash flows must use matching units

    For annual cash flows, use an annual discount rate; for monthly cash flows, use a monthly discount rate. For a nominal annual rate of 6% compounded monthly, use 6%/12. Entering 6% directly would discount each month at 6%. If 6% is an effective annual rate, use (1+6%)^(1/12)-1 to find the equivalent monthly rate.

  • Cash-flow amounts can vary

    Unlike PV, NPV does not require equal payments. Each period can have a different receipt or expense, but the discount rate stays constant.

  • The result is an amount, not a return rate

    NPV returns a monetary amount, not a percentage return. After including the initial cost separately, a positive result means the discounted future cash flows exceed that cost at the chosen discount rate.

  • Zero discount rate means no discounting

    At rate=0, NPV adds the future cash flows without discounting them. For example, =NPV(0,200,300)-400 returns 100 after subtracting the cost paid today.

Finding the break-even discount rate? Use IRR with the initial investment included as the first cash flow.

Intro NPV practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Intro Project Values Pro problem

NPV
Intro ☆☆☆
Pending

Advanced NPV practice problems

Use NPV alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced NPV problems are currently available.