--- title: "Use NPV Function to Evaluate Investments with Regular Dates" slug: "npv-function" description: "Learn to use XNPV to calculate net present value from regular cashflows and specific dates in Pigment." updated: 2026-01-20T12:08:34Z published: 2026-01-20T12:08:34Z --- > ## Documentation Index > Fetch the complete documentation index at: https://kb.pigment.com/llms.txt > Use this file to discover all available pages before exploring further. # NPV function ## Description Calculates the net present value of an investment based on future cashflows evenly spaced for a given time. $\mathrm{NPV} = \sum_{i=1}^{n} \frac{P_i}{(1+r)^i}$ ## Syntax `NPV(Rate Metric, Payment Metric [, Compute All Cells] [, Ranking Dimension])` | Argument | Type | Dimensions | Description | | --- | --- | --- | --- | | *Rate Metric* (required) | Number or Integer | No Dimension or subset of Dimensions of *Payment Metric* | This is the discount rate. When defined on *Ranking Dimension*, a different rate will be used for each item of *Payment Metric* | | *Payment Metric* (required) | Number or Integer | *Ranking Dimension* | This represents the cashflow of the investment. Cashflow can be positive if there is income or negative if payments are needed. | | *Compute All Cells* (optional) | boolean | No Dimension | Defines if the computation should be done on all *Ranking Dimension*’s items. If TRUE, starts at the first non empty *Ranking Dimension* item until the last non empty. If FALSE, only on the first non empty item of *Ranking Dimension is computed*. By default, set to FALSE. | | *Ranking Dimension* (optional) | Dimension (any kind) | | Required when *Payment Metric* is defined on multiple dimensions. Defines the dimension used to perform the calculation on. | ## Return type | Type | Dimensions | | --- | --- | | Number | No Dimension, or subset of *Payment Metric*’s Dimensions | If *Compute All Cells*is FALSE, will return for the first item of *Ranking Dimension* for which *Payment Metric was not empty*the sum of all future discounted payments. If *Compute All Cells*is TRUE, will return for all items of *Ranking Dimension*the sum of future discounted payments. > [!WARNING] > In case you have a dataset where the payments & incomes happens on specific dates (and are not evenly spaced), you can not use the NPV formula. In that case, you need to use the XNPV formula. > The formula only considers the numeric values, and if there are blanks or text values, these would be ignored The Pigment calculation of NPV excludes the initial investment and calculates the NPV for all the other future cash flows, if your cashflow starts during the same period of your initial investment it must be added. . ## Examples | Formula | Result | Description | | --- | --- | --- | | `NPV('Single Variable','Investment Cashflow')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/db4c7134-19ce-441c-92f3-aaeb8f7e144e.png) Example with single Rate | This example uses a single discount rate in the*Rate Metric* that has no dimensions, which is why its listed under Total. The function returns the value in Jan 24, as this is when the first data is in the *Payment Metric*. However, you could also use this formula in a Metric without dimensions. | | `NPV('Variable Rates over time','Investment Cashflow')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/7d7a6f7b-55e4-4115-af2f-1df3c6e14375.png) Example with variable Rate | This example uses a *Rate Metric* that has variable discount rates over time. The function returns the value in Jan 24, as this is when the first data is in the *Payment Metric*. However, you could also use this formula in a Metric without dimensions. | | `NPV('Single Variable','Investment Cashflow by Country',TRUE,Country)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/35513220-19c9-41be-8336-b6f9d9b0e296.png) Example with Single Rate, Multiple Dimensions in Payment Metric and Calculate all Cells True | This example uses a single discount rate in the*Rate Metric* that has no dimensions, which is why its listed under Total. The *Payment Metric* in this example is dimensioned by `Month` and `Country`, with Country being the ranking dimension. *Compute all cells* is set to `TRUE` so calculations are performed across all items in the *Ranking Dimension*, in this example `Country`. | | `NPV('Single Variable','Investment Cashflow by Country',FALSE,Country)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/d33a5c63-5994-4f32-a717-fa8d5444afdd.png) Example with Single Rate, Multiple Dimensions in Payment Metric and Calculate all Cells False | This example uses a single value *Rate Metric* that has no dimensions, which is why its listed under Total. The *Payment Metric* in this example is dimensioned by `Month` and `Country`, with Country being the ranking dimension. *Compute all cells* is set to `FALSE` so calculations are performed on the first item with data in the *Ranking dimension*, in this example `UK`. | ## See also Excel: [NPV](https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568), [XNPV](https://support.microsoft.com/en-us/office/xnpv-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7) Related articles: [XNPV](/v1/docs/xnpv-function)