Function: NPV

by | Jan 29, 2019 | Excel Formulas

NPV-01

The Excel NPV function is a finance function that calculates the net present value (NPV) of a series of cash flows based on a specified discount rate. In this guide, we’re going to show you how to use the Excel NPV function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Excel NPV Function Syntax

NPV(rate, value1,[value2],…)

Arguments

rate Discount rate over one period.
value1 The first value of cash flows. A range can be used to refer 1 to 254 arguments representing the payments and income.
[value2],… Optional. The second value of cash flows. This can be omitted by using a range for value1.


Examples

We are going to be using named ranges in our examples to make the formulas easier to read, but this is not required.

Example 1

=NPV(rate,cash_flow)
 formula returns value of 10,652.59 by calculating NPV for each period and adding them up. The mathematical formula for the NPV function is defined as NPV = F / [ ( 1 + I ) ^ n ] where F, I, and n stand for future payment (cash flow), rate, and number of periods respectively.

NPV-01

Example 2

=NPV(rate2,cash_flow2)+initial2
formula demonstrates how the Excel NPV function can be used with an initial cost of investment. Although, NPV refers to "net" present value, the NPV calculation is based on present value of uneven future cash flows. If there is an initial investment, the investment value must be added to the NPV result, and excluded from the function arguments.

Download Workbook

Tips

  • The Excel NPV function uses the order of value1, value2, ... when calculating the outcome. Therefore, you must pay attention to the order that you enter the payment and income values.
  • Arguments that are empty cells, logical values, text representations of numbers, error values, or a text string that cannot be translated into numbers will be ignored.
  • If an argument is an array or reference, only numbers in that array or reference will be counted. Empty cells, logical values, text, or error values in the array or reference are omitted.
  • If you have the exact dates of cash flows, and need to be very precise with your calculations, use XNPV instead of the NPV function.