If you have a series of cash flows that occur at irregular intervals, you need to use the XNPV function to find the net present value.
- [Instructor] In the previous movie, I showed you how to use the NPV function to find the net present value of an investment. That function assumes that all the cash flows happen at regular intervals. Every month, every two months, every year and so on. If you have a series of cash flows that occur at irregular intervals, you need to use the XNPV function to find the net present value. I'll demonstrate how to do that in this movie. My sample file is the irregular flows workbook and you can find it in the chapter two folder of the exercise files collection.
The XNPV function takes three arguments. The first is the discount rate, just called rate, which I have here in cell B3. The rate is the amount of money that you could get risk free from the investment. That could be a product line that you're very confident in, that could be a government security such as a treasury bond and so on. In this case, you're assuming 5.75%. The next set of arguments are the cash flows and the dates on which those flows will occur. So for example, you might have an original investment of $350,000 displayed as a negative number because it's cash outflow from our account.
That would happen on January 15, 2018. You can then expect cash flows at irregular intervals after that where you have $40,000 coming in on April 25, 2019, $75,000 on July 7, 2020 and so on. If you want to find the net present value, you can use the XNPV function. So in cell B13, I'll type an equal sign and then the function XNPV followed by a left parenthesis.
Now you can enter in the rate, the values, and the dates on which those flows occur. The rate is in cell B3 and we'll just assume it's an annual rate of 5.75% then type a comma. Next, we need to enter in the values and here's where we have one difference from the regular NPV function. For that, we excluded the first cash flow. In other words, the investment, and the reason we did that is because the NPV function assumed that cash flows would occur in the future.
In this case, because we have specific dates, Excel can do the hard math related to evaluating the cash flows based on those dates. So we don't need to worry about excluding the investment. So I'll select cells B6 through B11, then a comma and the dates are in A6 through A11. I'll type a right parenthesis and enter and we get the net present value for this project of $56,939.93.
Because the NPV is positive, we should strongly consider taking on this project.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart