---
title: "Use the XNPV Function to Evaluate Investments with Irregular Dates"
slug: "xnpv-function"
description: "Learn to use XNPV to calculate net present value from irregular cashflows and specific dates in Pigment."
updated: 2026-01-20T12:15:09Z
published: 2026-01-20T12:15:09Z
---

> ## 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.

# XNPV function

## Description

Calculates the net present value of an investment based on future cashflows on specific dates for a given time.

$\mathrm{XNPV} = \sum_{i=1}^{n} \frac{P_i}{(1+r)^{\frac{d_i - d_1}{365}}}$

## Syntax

`XNPV(Rate Metric, Payment Metric [, Compute All Cells] [, Ranking Dimension] [, Days Used])`

| Argument | Type | Dimensions | Description |
| --- | --- | --- | --- |
| *Rate Metric* (required) | Number or Integer | No Dimension or subset of Dimensions of *Payment Metric* | This is the rate that will be used as the discount rate. If 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 all cashflow of the investment. |
| *Compute All Cells* (optional) | boolean | No Dimension | Defines if the computation should be done on all *Ranking Dimension*’s items. If TRUE, it should be done from the first non empty *Ranking Dimension* item until the last non empty. If FALSE, the computation should be done only on the first non empty item of *Ranking Dimension*. By default, set to FALSE. |
| *Ranking Dimension* (optional) | Time Dimension |  | Optional only if *Payment Metric* is defined on one dimension only |
| *Days Used* (optional) | date | *Ranking Dimension* | Should be a property of *Ranking Dimension*. Enables to define exactly on which days of the *Ranking Dimension* the payments/investments are made. It is mandatory if *Ranking Dimension* is not a dimension from the Calendar. If *Ranking Dimension* is a dimension from the calendar and *Days Used* is not indicated, Pigment will take the *Start Date* of the *Ranking Dimension.* |

## 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 evenly spaced dates or on other type of items, you can use the NPV formula.

> The formula only considers the numeric values, and if there are blanks or text values, these would be ignored.

> [!TIP]
> When using the *Days used*argument, if the dates are not ordered chornologicaaly, the formul will return blank values. Also if there are black cells in the days used argument, the corresponding payment will be ignored.

The Pigment calculation of XNPV mimics the one done on most other softwares meaning that it does not behave like the NPV: no investment/payment is ignored and no adjustment is needed.

| Formula | Result | Description |
| --- | --- | --- |
| `XNPV('Single Variable','Investment Cashflow',TRUE)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/4ab5754f-d836-4c6e-9b53-38de55112ee5.png) Example with single Rate Metric | 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 values starting in Jan 24, as this is when the first data is in the *Payment Metric.*Because *Compute all cells*is**`TRUE`**the calculation is performed for every item. Because the dimesonality of the *Payment Metric*****uses only `Month` which is from the calendar, *Ranking Dimension* and *Days Used* is optional. |
| `XNPV('Variable Rates over time','Investment Cashflow',TRUE)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/81d9ef41-3890-4580-ae3d-74fe0406ffc7.png) Example with variable Rate Metric | This example uses a variable discount rate in the*Rate Metric.* The function returns the values starting in Jan 24, as this is when the first data is in the *Payment Metric.*Because *Compute all cells*is**`TRUE`**the calculation is performed for every item. Because the dimesonality of the *Payment Metric*****uses only `Month` which is from the calendar, *Ranking Dimension* and *Days Used* is optional. |
| `XNPV('Single Variable','Investment Cashflow by Country',TRUE,Month,Month.'Start Date')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/8f5f58d3-2ed8-4b69-8f44-b751ecec559b.png) Example with single variable and multiple dimensions used in Payment Metric | 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 `Month` being the ranking dimension. *Compute all cells* is set to `TRUE` so calculations are performed across all items. |
| `XNPV('Variable Rates over time','Investment Cashflow by Country',TRUE,Month,Month.'Start Date')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/ff4fc2ac-12d4-48cd-8cf4-fa792160cbeb.png) | This example uses a variable value *Rate Metric*. The *Payment Metric* in this example is dimensioned by `Month` and `Country`, with `Month` being the ranking dimension. *Compute all cells* is set to `TRUE` so calculations are performed across all items. |

## 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:

[NPV](/v1/docs/npv-function)