XNPV in Excel returns the net present value (NPV) of an investment but unlike the regular NPV function, this accounts for the discount rate and a series of cash flows that occur at irregular intervals in any investment. Hence, it is recommended to use the XNPV function rather than NPV function for the precision in the result and more accurate prediction.
Calculating the Net Present Value is an ideal function in Excel to calculate XNPV. It is the method or function that allows us to calculate the net present value of unequal cash flows in the mentioned period. In simple terms, it’s the function of Excel to find out the Net Present Value (NPV) of cash flows which may not be periodic.
Using the XNPV function it’s possible to determine the Net Present Value of any data. Besides, here we need to take the help of different syntax to find it. With the percentage of discount, values of the cash flows, and date mentioned we can find out the Final XNPV within a few seconds.
Recommended Read – NPV | How to calculate Net Present Value in Excel
Syntax and Arguments in XNPV
The XNPV function can be calculated/determined by using the syntax:
=XNPV(rate, values, dates)
- Rate = It’s the percentage of discount given or needed to apply in the syntax. The Discount rate is the rate to apply to the given cash flow.
- Values = The value is the given column with the cash values in cash flows. In short, it’s the value showing the flow of cash in your worksheet.
- Dates =The particular period given on the opposite or corresponding side of the cash flow
XNPV is the financial function for finding out the Net Present Value. This function is based on the given rate of discount and the cash flows on the worksheet. You will be more clear after learning the steps on how to calculate XNPV in Excel.
Steps to Calculate XNPV in Excel
Let’s now calculate the XNPV in Excel step-by-step. Follow through to calculate the XPNV.
Creating the Worksheet with the Cashflows
Open your Excel and create the requirements as shown in the image.
Enter the date on the left side, cash flow on the right side, and percentage of rate of discount. Here we’ve entered the date, cashflows with a 10% discount rate
The XNPV function
- To calculate the XNPV in Excel we have to enter the syntax, =XNPV(rate, values, dates).
- In the adjacent cell for XNPV result, we determine the Net Present Value. So, type the syntax =XNPV(select the date cells, select the cashflow column, select the date column).
Rate = select the cell having discount percent. In our case, we offer a 10% discount. So, here we select the cell having 10%. It’s in B10.
Values = After selecting the rate of discount, select the cash flow column. In our case, we select the cash flow column from B5 to B9.
Dates = Now, select the dates column. In our example, our date starts from A5 to A9.
- We use, =XNPV(B10,B5:B9,A5:A9).
- After hitting enter, we are presented with the resultant XNPV value, as shown:
Here we get the XNPV resultant value which is = 685,874 for our sample data.
Conclusion
Hope you have learned well about the calculation of the XNPV in Excel and also, why it is preferred over the regular NPV function to estimate.