Comparison Between Functions in Excel: XNPV vs NPV

In case of investing money in a business, we need to consider many factors related to the investments and keep up to date. Net Present Value which is generally known as NPV  is also a financial factor. In Microsoft Excel, we can calculate Net Present Value using both the XNPV function and the NPV function. In this article, I will try to show a comparison between XNPV vs NPV functions while calculating Net Present Value.


Download Working File


Introduction of NPV

A financial indicator used to evaluate the profitability of an investment is the Net Present Value (NPV). It is estimated by subtracting the investment’s initial cost from the present value of the future cash flows. As it takes into account the time value of money, the NPV calculation is crucial.

The NPV formula is mentioned below:

NPV = Present Value of Future Cash Flows – Initial Cost

We can calculate the present value based on future cash flows using the following formula:

XNPV vs NPV

Here, Future Value is expected net cash inflow or outflows, r is the discount rate, t is the number of time periods.

This basic strategy is used by NPV to bring all future cash flows to a single point in time. The expanded formula for NPV is as follows:

XNPV vs NPV


Syntax of NPV function

The NPV function is defined by the following syntax in Excel:

=NPV (rate, value1, [value2], ...)

 Arguments Explanations:

Argument Required/Optional Explanation
rate Required Discount rate over one period.
value1 Required First value(s) representing cash flows.
value2 optional Second value(s) representing cash flows

Syntax of XNPV function

We can also calculate NPV with the XNPV function. The XNPV function follows the following syntax:

=XNPV(rate, values,dates)

 Arguments Explanations:

Argument Required/Optional Explanation
rate Required defines the discount rate that will be applied over the cashflows
values Required represents cash flows.
dates Required Cashflow dates

Calculating Net Present Value with NPV Function

We can easily calculate the Net Present Value with the NPV function. For this, we just need to assign the values according to the syntax.

Steps:

  • Create a dataset with cashflows over a certain period of time. I have arranged them in the Periods and Cashflows I have also considered the Annual Discount Rate.

 XNPV vs NPV

  • Now, input the following formula to calculate Net Present Value with NPV function:
=NPV(D11,D5,D6,D7,D8,D9)

Here,
D11 = Annual discount rate
C5, C6, C7, C8, C9 = Cashflows over a certain time interval

Calculating Net Present Value with NPV Function

  • Press ENTER to have the Net Present Value. You can modify the cells according to your preferences.

We can cross-match the result with the basic arithmetic formula. For this, I have separately calculated the Present Value over the time period and used summation to have the Net Present Value.

Calculating Net Present Value with NPV Function

We can see that both the results are same. In order to lessen our work hassle, we can use the NPV function to calculate Net Present Value.


Calculating Net Present Value with XNPV Function

We can also calculate the Net Present Value with the XNPV function. Just follow the steps mentioned below and you will have your desired result.

Steps:

  • Arrange the Date and Cashflows in separate columns.
  • Consider the Discount Rate.

Calculating Net Present Value with XNPV Function

  • Next, apply the following formula to have the Net Present Value:
=XNPV(C11,C5:C9,B5:B9)

Here,
C11 = Discount Rate
C5:C9 = Cashflows at a different time period
B5:B9 = Date

  • Finally, press ENTER to have the Net Present Value.

Calculating Net Present Value with XNPV Function


Comparisonal Observations from the Applications of XNPV vs NPV Functions

From the above discussions, we can say that we can calculate Net Present Value with NPV function as well as XNPV function. But there is a lot of difference between these functions. They are listed below:

  1. The main difference between the NPV and the XNPV functions is that the values input in the NPV function are equally spaced in terms of time period, whereas the values input in the XNPV function are not equally spaced.
  2. Net Present Value calculation with NPV function does not require dates. But It is a must to have an actual set of dates with the cashflows in the XNPV function.
  3. XNPV returns the more accurate results as it takes the actual set of dates.
  4. Warning due to error is very common in terms of working with the XNPV function as it follows a specific date pattern. On the other hand, there is no issue with the date pattern in NPV function as there is no use of date in it.

Conclusion

At the end, I like to add that I have just tried to explain the comparison and use of the XNPV vs NPV function while calculating Net Present Value. You can apply any of these functions to calculate Net Present value considering the merits and dermits mentioned above. If you have any further queries, comment below. You can also visit our site to have more knowledge related to Excel.


Related Articles

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. Thanks a lot Taryn!!!

    I m a Finance Student and need this formulas a Lot!!!!

    You have very easily explained the concepts.!!!

    Looking foreword for more:):):):):):)

    • You are most welcome. We are intending to do a financial functions tutorial series so lookout for that 🙂 Best of luck with your studies as well 🙂

Leave a reply

ExcelDemy
Logo