The NPV function calculates the net present value for a series of positive and negative transactions with a constant interest rate.

The net present value of an investment enables you to know how much a future investment is worth today. You could use the NPV function to assess whether an investment is worthwhile while accounting for depreciation.

NPV(Discount rate, Cash flow, Dates, Transactions)

 Argument Data type Description Discount rate (required) Number The discount rate used to calculate net present value. This argument is presented as a multiplication factor, so 0.1 is equivalent to 10%. Cash flow (required) Number A series of positive and negative values that represent cash inflow and outflow. Dates Date The date associated with each value of the Cash flow argument. This argument is optional, but if used, you must provide the Transactions argument also. Transactions List A list of transactions. This argument is optional, but if used, the list must be a common dimension of the Cash flow and Dates argument.

The NPV function returns a number.

NPV is the solution to this equation:

$NPV = \displaystyle\sum_{i = 1} ^ {N} \dfrac {{P_i}}{(1 + Rate) ^ {{d_i}/{365}}}$

In this equation:

• N is the total number of cash flows.
• Pi is the ith cash flow.
• di is the number of days from the start of the first period.
• Rate is the discount rate.

You can reference the Users list with the NPV function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

This example uses two modules. The first module, Annual cash flow, contains Cash flow line item with a Time Scale of Year. The Cash flow line item contains both a positive and negative value.

 FY20 FY21 FY22 Cash flow -100,000 0 130,000

The second module contains two line items and no other dimensions. One line item contains the Discount rate, and another line item contains a formula that uses NPV with the Discount rate and the Cash flow line item from the previous module. As the NPV function returns a single value, the result does not need a time dimension.

 Discount rate 10% NPV of annual cash flow NPV(Discount rate, 'Annual cash flow'.Cash flow) 7,438.02

In this example, two modules are used. One source module, Plant Transaction Data, is dimensioned by the Transactions and Plants lists. Transactions is on rows, and Plants is on pages, with Plant 1 selected.

The Plant Transaction Data module contains the values of transactions, dates of transactions, and a description for some transactions.

 Description Cash flow Date Transaction 01 Land purchase -100,000 1/1/2011 Transaction 02 Turbine purchase -850,000 1/1/2012 Transaction 03 Energy generation revenue 200,000 1/3/2013 Transaction 04 Energy generation revenue 200,000 1/3/2014 Transaction 05 Energy generation revenue 200,000 1/3/2015 Transaction 06 Energy generation revenue 200,000 1/3/2016 Transaction 07 Energy generation revenue 200,000 1/3/2017 Transaction 08 Energy generation revenue 200,000 1/3/2018 Transaction 09 Energy generation revenue 200,000 1/3/2019 Transaction 10 Energy generation revenue 200,000 1/3/2020 Transaction 11 Energy generation revenue 200,000 1/3/2021 Transaction 12 Energy generation revenue 200,000 1/3/2022 Transaction 13 Energy generation revenue 200,000 1/3/2023 Transaction 14 Energy generation revenue 200,000 1/3/2024 Transaction 15 Energy generation revenue 200,000 1/3/2025 Transaction 16 Final energy generation revenue 200,000 1/3/2026 Total 1,850,000

The second module uses the data from the Plant Transaction Data module with the NPV function to calculate the net present value for each plant. The column for Plant 1 contains the net present value for data displayed in the Plant Transaction Data module.

 Plant 1 Plant 2 Plant 3 Plant 4 Net Present Value for Plant NPV(0.1, 'Plant Transaction Data'. Cash flow, 'Plant Transaction Data'. Date, Transactions) 445,464 325,849 194,035 86,659