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 **X****NPV 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**.

**Table of Contents**hide

## 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:

** **

*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:

** **

## 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**.

- 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

- 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**.

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**.

- 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**.

## 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:

- 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. **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.**XNPV**returns the more accurate results as it takes the actual set of dates.- 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.

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 🙂