How to use NPV Function in Excel

NPV in excel is also known as net present value formula in excel which is used to calculate the difference of the present cash inflow and cash outflow for an investment, it is an inbuilt function in excel and it is a financial formula which takes rate value for inflow and outflow as an input.

Syntax:= NPV (rate, value1, [value2], …)

The NPV function syntax has the following arguments:

  • Rate    Required. The rate of discount over the length of one period.
  • Value1, value2, …    Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.
    • Value1, value2, … must be equally spaced in time and occur at the end of each period.
    • NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
    • Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
    • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

Example: Let’s look at some Excel NPV function examples and explore how to use the NPV function as a worksheet function in Microsoft Excel:

Example 1:

Syntax:  =NPV(B1, B2, B3, B4, B5)

Result: $116,637.66

In this example, the initial investment of $2,000,000(shown in cell B2), was done at the end of the first period. That is why this value is considered as the first argument (i.e. value1) to the NPV function in excel.

Example 2:

Based on the Excel spreadsheet above, the following NPV examples would return:

Syntax: =NPV(B1,B3:B7)+B2
Result: $96,578.74

Syntax: =NPV(B1,B3:B7,-700000)+B2
Result: ($320,808.39)

Note that, as the initial investment of $2,000,000 (shown in cell B2), was done at the start of the first period, this value is not included in the arguments to the NPV function in Excel. Instead, the first cash flow is added to the NPV excel result

Note:

  • The NPV investment begins one period ahead of the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation on excel is based upon future cash flows. If the first cash flow is made at the beginning of the first period, the first value must be explicitly added to the NPV excel result, excluded in the values arguments.
  • Let’s say n is the number of cash flows in the list of values, the formula for NPV (Net Present Value) in Excel would be:
  • If the arguments are supplied individually, numbers, logical values, blank cells and text representations of numbers are evaluated as numeric values, while other values of cell in text and error form are ignored by the function.
  • If the arguments are supplied in a range, all non-numeric values in the range are ignored.
  • We need to enter transactions, payments and income values in the right sequence as NPV functions use the order of the 2nd argument i.e. value1, value2, … to evaluate the order of cash flows.
  • The key difference between NPV functions and PV function is that PV allows cash flows to begin either at the beginning or at the end of the period.
  • NVP (Net Present Value) function in the latest versions of Excel can accept up to 254 value arguments, but with Excel 2003, only up to 29 values can be supplied to the function.

Add a Comment

Your email address will not be published. Required fields are marked *