The NPV function

Returns the net present value of future cash flows at a discount rate. The first value is discounted one period, so a payment made today is added outside NPV.

How to use NPV

NPV tells you what future money is worth today. A dollar next year is worth less than a dollar in your hand, because you could have invested it. NPV shrinks each future payment by a discount rate, one period at a time, and adds them up. Think of it as a price tag for a stream of payments.

Use it to judge an investment: a machine that costs $20,000 now and saves money for the next five years, or a contract with payments spread over several years. The rate is the return you demand per period, and the values are the cash flows from period 1 onward, as a range or a list. =NPV(B1,B3:B7) discounts five yearly values at the rate in B1.

NPV treats the first value as one period away. A payment you make today therefore stays outside the function and is added afterward: =NPV(B1,B3:B7)+B2, where B2 holds the investment as a negative number. A result above 0 means the investment earns more than the rate you require. To get the rate itself instead of an amount, use IRR. For the payment on a loan, use PMT.

Syntax

NPV(rate, value1, [value2], ...)
rate
The discount rate per period.
value1
The cash flows, one per period from period 1, as numbers, cells or a range.
value2 (optional)
More cash flows.

Examples

F1=NPV(D1,B2:B4)+B1
ABCDEF
1Year 0-10000.0830.838795
2Year 1400
3Year 2400
4Year 3400
B1=NPV(0.1,110,121)
AB
1200

In plain words: What 110 next year and 121 the year after are worth today at a 10% discount rate.

Common NPV mistakes

  • My NPV is off by a whole year

    The investment you pay today is inside the range, so NPV discounts it one period. Leave it out of the range and add it afterward: =NPV(B1,B3:B7)+B2.

  • The result is far too low with monthly cash flows

    The rate and the periods must match. With monthly values and a yearly rate, divide the rate by 12: =NPV(B1/12,B3:B14).

  • I typed the rate as 10 and got a strange number

    A rate of 10 means 1000%. Type 10% or 0.1.

  • A month with no payment is missing from the calculation

    NPV skips empty cells and text, so later payments count one period too early. Type 0 in every period without a cash flow.

Practice NPV

Questions

Why is my NPV wrong by a year?

NPV discounts the first value one period, as if it were paid a year from now. Leave the investment you pay today out of the range and add it on after, like NPV(rate,B2:B6)+B1.

What does a positive NPV mean?

The cash flows are worth more than the investment at that discount rate, so the project earns more than the rate you require. A negative NPV means it earns less.

Which discount rate should I use?

Use the return you require. That can be the interest on a loan for the investment, what the money would earn in a safe alternative, or your company's required return. A higher rate makes future payments worth less today.

What if the payments are not evenly spaced in time?

NPV assumes equal periods. For payments on specific dates, use XNPV, which takes the dates as a separate range.

Related functions