## Excel XNPV function

The XNPV function calculates the net present value (NPV) for a series of cash flows that may or may not be periodic.

#### Syntax

XNPV(rate, values, dates)

#### Arguments

• Rate (required): The discount rate to apply to the cash flows.
• Values(required): An array or a range of cells that represents the series of cash flows.

The series of values must contain at least one positive and one negative value:

-- The negative value: represents the cost or payment;
-- The positive value: represents the income.
• Dates (required): A series of dates that corresponds to the values.
1. The date of the initial investment must be first in the array;
2. All other dates must be later than the initial date, and can be in any order;
3. Dates should be entered:
-- By using the DATE function;
-- As references to cells containing dates;
-- As results returned from other formulas.

#### Remarks

1. In Excel, dates can be used in calculations as they are stored as sequential numbers. By default, 1/1/1900 is serial number 1, so 12/1/2021 is serial number 40877 because it is 40876 days after 1/1/1900;
2. Dates are truncated to integers if containing time;
3. The #NUM! error occurs when either of the following conditions is met:
-- The arguments “values” and “dates” arrays have different dimensions;
-- Any of the given “dates” precedes the starting date.
4. The #VALUE! error occurs when either of the following condition is met:
-- Any of the supplied dates is an invalid date;
-- Any of the supplied “rate”, “values” and “dates” is non-numeric.
5. The XNPV is calculated by the following equation:

Where:
-- Pi is the ith or last cash flow (payment);
-- di is the ith or last payment date;
-- d1 is the 0th payment date.

#### Return Value

It returns a numeric value.

#### Example

As shown in the screenshot below, there is a 1-year investment project, which is expected to generate the following cash flows based on a discount rate of 8%. To calculate the net present value using the XNPV function, you can do as follows.

1. Select a blank cell, copy or enter the formula below and press the Enter key to get the result.

=XNPV(C5,C8:C14,D8:D14)

Then you can change the cell format to currency if you need.

Note: If the function returns a positive number, the investment is feasible. Otherwise, the investment is infeasible.

#### Related Functions

Excel NPV function
The NPV function returns the net present value of an investment based on a discount or interest rate and a series of future cash flows: payments (negative values) and income (positive values).

### The Best Office Productivity Tools

#### Kutools for Excel - Helps You To Stand Out From Crowd

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.