English

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)

Arguments

ArgumentData typeDescription
Discount rate (required)NumberThe discount rate used to calculate net present value. This argument uses percentage format, so 0.1 is equivalent to 10%.
Cash flow (required)NumberA series of positive and negative values that represent cash inflow and outflow.
DatesDate

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.

TransactionsList

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.

Additional information

How NPV is calculated

NPV is the solution to this equation:

NPV=Σi=1N(1+Rate)di/365PNPV = \Sigma_{i = 1}^{N} \quad_{(1 + Rate) ^{d}i/365} ^ {\quad \quad P}

In this equation:

  • N is the number of payments in and out over the time scale
  • Pi is the payment in the ith period
  • di is the number of days from the start of the first period
  • Rate is the discount rate

Use NPV with the Users list

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.

Excel equivalent

NPV

Examples

Example of NPV with timescale

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.


FY20FY21FY22
Cash flow-100,0000130,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 IRR function returns a single value, the result does not need a time dimension.



Discount rate10%

NPV of annual cash flow

NPV(Discount rate, 'Annual cash flow'.Cash flow)

7,438.02

Example of NPV with dates

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.


DescriptionCash flowDate
Transaction 01Land purchase-100,0001/1/2011
Transaction 02Turbine purchase-850,0001/1/2012
Transaction 03Energy generation revenue200,0001/3/2013
Transaction 04Energy generation revenue200,0001/3/2014
Transaction 05Energy generation revenue200,0001/3/2015
Transaction 06Energy generation revenue200,0001/3/2016
Transaction 07Energy generation revenue200,0001/3/2017
Transaction 08Energy generation revenue200,0001/3/2018
Transaction 09Energy generation revenue200,0001/3/2019
Transaction 10Energy generation revenue200,0001/3/2020
Transaction 11Energy generation revenue200,0001/3/2021
Transaction 12Energy generation revenue200,0001/3/2022
Transaction 13Energy generation revenue200,0001/3/2023
Transaction 14Energy generation revenue200,0001/3/2024
Transaction 15Energy generation revenue200,0001/3/2025
Transaction 16Final energy generation revenue200,0001/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 internal rate of return for data displayed in the Plant Transaction Data module.


Plant 1Plant 2Plant 3Plant 4

Net Present Value for Plant

NPV(0.1, 'Plant Transaction Data'. Cash flow, 'Plant Transaction Data'. Date, Transactions)

445,464325,849194,03586,659

Disclaimer

We update Anapedia content regularly to provide the most up-to-date instructions.