FormuladrillNPV
NPV(rate, value1, [value2], ...) returns the present value of a series of equally spaced future cash flows discounted at the given rate, assuming the first value arrives one period from now. The investment made today is not discounted, so add it outside: =NPV(rate, B2:B5) + B1 where B1 is the negative initial outlay.
Syntax
NPV(rate, value1, [value2], ...)| Part | What it is |
|---|---|
rate | Discount rate per period, for example 10% per year. |
value1, value2... | Cash flows at the end of periods 1, 2, 3... in order. Inflows positive, outflows negative. A range is allowed. |
Examples
=NPV(10%, 500, 500, 500)Result: 1,243.43
Three payments of 500 at the end of years 1 to 3, discounted at 10%.
=NPV(10%, 500, 500, 500) - 1000Result: 243.43
Subtracting the 1,000 invested today gives the net present value; positive means the project beats the 10% hurdle.
=NPV(8%, B2:B5) + B1Result: -41.35 when B1 is -1500 and B2:B5 are 300, 400, 500, 600
B1 is the time-zero cash flow, kept outside NPV.
=XNPV(10%, B1:B5, A1:A5)Result: The NPV using the actual dates in A1:A5, with the first cash flow at its own date
XNPV discounts by exact days and includes the first value undiscounted.
Common mistakes
- Including the initial investment inside NPV discounts it by one period and understates the outflow. Keep it outside and add it.
- The rate and the periods disagree: an annual rate with monthly cash flows needs rate/12.
- Blank cells in the range are skipped, which shifts later cash flows earlier. Put 0 in periods with no cash flow.
- Cash flows that are not evenly spaced need XNPV with their dates.
Practice NPV
These drills check your answer on live data, free, no account needed.
- Is the project worth it?The project costs 50,000 today (year 0) and returns cash in years 1 to 3. Calculate the net present value in B6 using the discount rate in B1.
- Value of an annuityWhat is the value today of receiving the yearly payment in B1 for the number of years in B3, at the rate in B2? The answer must be positive. Put it in B4.
- Growth over timeHow much will the starting amount be worth after the stated years of compound growth at the annual return? Put the result in B4.
Questions
What is the formula for NPV in Excel?
=NPV(rate, future_cash_flows) + initial_investment, where the investment is a negative number in its own cell.
Why does Excel NPV differ from the textbook NPV?
Excel's NPV assumes every value, including the first, is one period in the future. The textbook version has the first cash flow at time zero. Adding the initial outlay outside the function reconciles the two.
What does a negative NPV mean?
At that discount rate the project returns less than the rate, so on these numbers it does not meet the hurdle.
Related
- IRRHow the Excel IRR function finds the rate at which cash flows net to zero, with a worked example, the cash flow layout it needs, the #NUM! fix and XIRR.
- PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.
- ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.