Taryn Nefdt
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:
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.
Read More: How to Use PV Function in Excel (3 Examples)
Similar Readings
- How to Use Excel PRICE Function (3 Appropriate Examples)
- How to Use IPMT Function in Excel (8 Examples)
- Use EFFECT Function in Excel (2 Examples)
- How to Use Excel PPMT Function (3 Suitable Examples)
- How to Use SLN Function in Excel (3 Examples)
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 🙂