Excel XNPV Function – NPV With Irregular Cash Flows

In our previous blog, we learned how to calculate the NPV in Excel using the NPV excel function. The disadvantage of using the NPV formula is that it assumes equal time intervals. To cater to this disadvantage of Excel, Microsoft has introduced the XNPV function in excel.

In this tutorial, we would learn how to calculate the Net Present Value (NPV) using the XNPV excel function.

Before getting into what is XNPV excel formula, let us first know some basics about what is net present value in financial terms.

Net Present Value (NPV) – Definition, Meaning & Calculation

In finance terms, the term Net Present Value refers to the discounted value of the expected future cash flows (PVCF) minus its initial investment amount.

Formula to calculate NPV is as follows:

Net Present Value = Total PVCF - Initial investment

Here, PVCF = Cash Flow / (1 + r)n

The PVCF of all the future year cash flows are totalled to find the total PVCF. From this total PVCF, the initial investment amount is subtracted to find the NPV value. 

When To Use XNPV Function in Excel

Like the NPV function, the XNPV formula in excel is also used to calculate the net present value (NPV) of a project or investment proposal taking into consideration expected future cash flows.

This function forms part of the financial excel function group.

Syntax and Arguments

=XNPV(rate, values, dates)

The below points describe the arguments of the excel XNPV formula:

  • rate – In this argument, specify the discounting rate in percentage (like 20%) or decimal format (like 0.20) only.
  • values – In this argument, specify the cell range containing the series of cash flow values. This argument may include negative (cash outflows and initial investment) or positive values (cash inflows).
  • dates – In this argument, specify the dates cell range corresponding the cash flow values.

All the above three arguments are mandatory arguments of the XNPV function.

Example of Using XNPV Excel Function

In this section of the tutorial, we would learn how to use the XNPV excel function with the help of a very example.

Let’s say, an 8 years investment project is expected to generate the following cash flows. The project has a discount rate of interest of 15%.

Sample Data for XNPV Function Excel

In the above example, the negative value of 8,00,000 dated 01-01-2021 is the initial investment amount. The XNPV function always considers the first value as the initial outlay value at year 0 (beginning).

To calculate the NPV using the XNPV formula in excel, simply use the following formula:

=XNPV(B2,B5:B13,A5:A13)

As a result, excel returns the output NPV value as 1,19,194.20. Since the NPV value is positive, the investment proposal is worth accepting. 😉

Calculation NPV using XNPV function

Explanation – The XNPV formula considers the irregular time intervals in the cell range A5:A13 and discounts the corresponding cash flows in B5:B13 using the discount rate in cell B2.

Note that, in the case of irregular cash flows, the NPV formula results in an incorrect value, whereas XNPV returns a far more accurate result.

Infographic - XNPV Formula Function in Excel

NPV vs XNPV Excel Formula

The below points describe the difference between NPV and XNPV in Excel:

  • NPV formula returns the total of the present value future cash flows. To calculate the net present value under this formula, subtract the initial investment from its result.

    Whereas, XNPV function directly returns the net present value without any need to do any additional subtraction.

    See the below formula to understand the difference in NPV calculation between NPV and XNPV formulas:
    Net present value under NPV formula = NPV(rate, value1) minus initial investment
    Net present value under XNPV formula = XNPV(rate, values, dates)
  • The NPV function is suitable for the evenly-distributed cash flows. NPV function does not give a correct result for irregular cash flows.

    On the other hand, the XNPV function is suitable for both, evenly as well as uneven cash flows.
NPV vs XNPV Excel Formula
  • The XNPV function considers first value in the values argument as cash investment amount at the beginning. However, the rest values are considered based on 365 days per year with the correspnding dates.

Thank You 🙂

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.