Skip to main content

Explore the new, free version of Kutools for Outlook today!

 

Excel XNPV function

Author: Siluvia Last Modified: 2022-02-21

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.