XNPV function

The XNPV function in Excel calculates the net present value (NPV) of cash flows that occur at irregular intervals. Unlike the standard NPV function, which assumes equal periods between cash flows, XNPV accounts for actual dates.

Syntax:

XNPV(rate, values, dates)

Arguments:

1. rate: The discount rate (as a decimal or percentage) used to calculate the present value of the cash flows.
2. values: An array of cash flows. The first value is typically negative (representing an initial investment or outflow), and subsequent values are positive (inflows or returns).
3. dates: An array of dates corresponding to the cash flows. These must match the order of the values array.

Formula:

The XNPV is calculated as:

$$\text{XNPV} = \sum \left( \frac{\text{Cash Flow}_i}{(1 + \text{rate})^{\frac{\text{Date}_i – \text{Date}_0}{365}}} \right)$$

Where:

• 
  $\text{Date}_0$ 

  is the first date in the series (base date).

• 
  $\text{Date}_i$ 

  is the date of the $i$ 

  -th cash flow.

Example:

Suppose you have the following cash flow data:

Formula:

=XNPV(10%, B2:B5, A2:A5)

Where:

• 10% is the discount rate.
• B2:B5 contains the cash flows.
• A2:A5 contains the corresponding dates.

Result:

The result will be the net present value of these cash flows discounted at an annual rate of 10%.

Key Points:

• XNPV vs. NPV: Use XNPV when cash flows are irregular; use NPV when cash flows occur at regular intervals.
• The function assumes a 365-day year for date calculations.
• At least one positive and one negative cash flow is required.