XNPV Function in Excel

Helps calculate the net present value (NPV) of investment by discounting its non-periodic cash flows received on specific dates

Patrick Curtis

Reviewed by

Patrick Curtis

Expertise: Private Equity | Investment Banking


October 6, 2023

What Is The XNPV Function in Excel?

XNPV is categorized as a financial function that calculates investment net present value (NPV) by discounting its non-periodic cash flows received on specific dates.

XNPV Function

If you are an investor or an analyst looking to value different potential investments, then this function in Excel is the right tool. This function is available in all versions of Excel from 2003 to 2019 and Excel 365.

It is a built-in Excel function that will return the net present value (NPV) based on non-periodic future cash flows discounted to the present value. 

Calculating the NPV can determine whether to invest in a particular asset/project or look elsewhere. As an investor, you need to make crucial decisions involving immense capital investments. 

In such cases, NPV is one of the essential tools to check the profitability of cash-flow-generating assets. NPV is also calculated in large corporations when business expansions occur and for other capital budgeting decisions.

But what exactly is NPV?

Key Takeaways

There are numerous issues that you will face while using the function, such as:

  • The function will give the #NUM! error if the range for dates and values have different lengths.
  • If any of the subsequent dates are earlier than the first cash inflow/outflow date, you will receive a #NUM! error.
  • Excel will give a #VALUE! error if it is unable to identify a valid format for the date.
  • The payment made at year 0 is optional and corresponds to the cash outflows to acquire a project or investment opportunity. If a cash outflow is made at year 0, it must be negative.

What is Net Present Value (NPV)

NPV, an acronym for net present value, is the difference between future cash inflows and future cash outflows for some time, discounted in value to the present, accounting for the time value of money. 

But why net present value and not just 'value'?

To understand better, let's see an example - Assume that you make an investment worth $10,000 that generates the following cash flows:


However, future cash flows are not an appropriate measure for checking the profitability of an investment. Currently, the cash inflows generated against a $10,000 investment are equal to $12,100. 

The profit of $2,100 doesn't seem that bad; however, $2,100 after five years is worth less than $2,100 today. 

A few factors contribute to the dollar losing its value over time, including inflation, opportunity cost, risk, etc. Hence, the cash flows are discounted to their present value using a discount rate

Alternatively, the time value of money also states that "a dollar today is worth more than a dollar in the future." By assuming that our discount rate is equal to 8%, the future cash inflows become:

Future Cash Inflows

See the massive difference in the cash flows? Initially, we were expecting $2,800 in the fifth year; however, after discounting the cash flow to its present value, we will get just $1,905.63.

If we add all the cash inflows, the total comes to $9,490.36, meaning it doesn't even return our entire principal after five years. 

We still fall $509.64 short of recovering our entire principal of $10,000. You can see the difference in the profits with regular and discounted cash flows illustrated below:

Discounted Cash Flows


We have put down the manual calculations to help explain how to derive the net present value from the cash flows. If you use the function, you don’t need to make all these calculations to get the NPV for an investment.

Interpretation of the Net Present Value

So, we have calculated the net present value of the cash inflows and the net present value by subtracting the cash inflows and the cash outflows. 

The question that arises is, what would you do? 

Would you go ahead and make the investment knowing that it might not even return the initial investment at the end of five years? 

Absolutely not! When the NPV is negative, the project should be rejected immediately since it does not generate value for the investor. However, if the NPV is positive, you can go ahead with the investment.

Another scenario - What would you do if a $10,000 investment returns net present cash inflows equalling $10,010? The investment's net present value would equal a mere $10. Would you go ahead and invest?

Well, it depends entirely on you. There is technically nothing wrong with an investment as long as the NPV is positive. In the end, $10 is much better than having nothing at all! It still adds value to the investor's portfolio.

Example of the XNPV Function in Excel

The function is straightforward to use. If it weren't for the function, the manual calculations to determine the present value of cash flows would be time-consuming. We will review a couple of examples of the function to understand it better.

The syntax for the function is:

=XNPV(rate, values, dates)


  • rate = the discount rate that will be applied to the cash flows. This is a required argument.
  • values = the range of cells that represent cash inflows and cash outflows. This is a required argument. 
  • dates = the range of cells that show dates corresponding to cash inflows and cash outflows. This is a required argument.

Example #1: Let's assume that, as the CEO of XYZ bank, you identify a possible company to acquire for $100,000. The company will generate cash inflows discounted to present value for the bank as illustrated below:

XYZ Bank

Here we won't be calculating the present value of the cash flows. Instead, excel's XNPV function automatically does it for you using the discount rate you input into the formula.

To calculate the net present value, use the formula in cell C11 as =XNPV(F4, C4:C9, B4:B9). This will give you a result of $11,657.17.


This is the difference between our cash outflow, i.e., $100,000, and the present value of all the cash inflows for five years. Therefore, since NPV is positive, the investment is profitable, and we can go ahead with its purchase.

Example #2: Let's assume that a project generates $3,000 of cash inflows yearly for an initial investment of $10,000. The cash inflows mostly occur in January, as illustrated below:

Example 2

To calculate the net present value(NPV), we will use the formula =XNPV(F4, C4:C9, B4:B9), giving us the result illustrated below:

Example 2 Result


As the XNPV function calculates the net present value for non-periodic cash flows from an investment in Excel, a similar function, NPV, calculates the net present value for periodic/equal cash flows. That's it; that's the difference between the two functions.

The syntax for the NPV function is:

=NPV(rate, values)


  • rate = the discount rate that will be applied to the cash flows. This is a required argument.
  • values = the range of cells that represent cash inflows and cash outflows. This is a required argument. 

What happens when we use the XNPV and NPV functions for the same data set? Let's find out. Assume that company ABC intends to acquire another company, X, for $500,000. The expected cash flows over five years are:


To calculate cash flows as periodic income streams, we will ignore the dates in column B. The formula you will use in cell C11 is =NPV(F4, C4:C9), giving the result of $32,692.75.

NPV Result

This method avoids changing cash flows to their present values and finding their differences. Instead, the computer does it all for you! 

Similarly, to find the NPV for non-periodic cash flows, we will consider the dates in column B, making our formula =XNPV(F4, C4:C9, B4:B9), giving the result illustrated below:


We have used the same discount rate and made no changes in the cash inflows or outflows, yet we have a difference of $1,231.21 between the two values. What could be the reason behind this?

The answer is quite simple. 

The NPV function assumes that the cash inflows in the future are made 365 days away (one year). For example, if today's date is 3rd March 2022, the first cash inflow will occur on 3rd March 2023 for $80,000. The second cash inflow will occur on 3rd March 2024, for $120,000, and so on.

However, in contrast, when we use the XNPV function, we expect Excel to calculate the present value using the date we provided in the spreadsheet. 

We had a cash flow of $175,000, which took more than a year, while another cash flow of $120,000 was received earlier than expected. Due to such fluctuations, the XNPV calculated is slightly higher than the NPV.

Why Use the XNPV Function in Excel?

There are several reasons you should prefer using the XNPV function in Excel rather than making manual calculations or the NPV function. Some of them are:

1. Precision

The primary benefit of using the XNPV function is the result's precision for net present value. 

The function uses the exact dates on which the cash flows occurred, as opposed to the NPV function that assumes the cash flows are made every 365 days, i.e., if the first cash inflow is on 3rd March 2022, the next would be on 3rd March 2023. 

A lot of capital is employed in different investments, meaning that determining the correct return on investment is essential to making effective decisions.

2. Faster calculations

A function that takes just three arguments is much faster than performing manual calculations to find the present value of cash flows and then calculating the NPV. There is a greater chance of making errors among all these manual calculations.

3. Identifying cash flows at varying times

In a real-life scenario, the timings of cash flows can be irregular. You cannot expect cash inflows to be on the same day of each month every year. 

Some organizations expect cash flows at the end of the year, some expect them at the beginning of the year, while others may have them throughout the year. 

Hence, using the function, you can correctly time cash flows, calculating an investment opportunity's net present value.

Excel Modeling Course

Everything You Need To Master Excel Modeling

To Help You Thrive in the Most Prestigious Jobs on Wall Street.

Learn More

Researched and Authored by Akash Bagul | LinkedIn

Reviewed and edited by James Fazeli-Sinaki | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: