Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Many businesses combine cash flows from several sources, such as asset sales or retail revenues, to fund other investments. These revenues don't always come in on a regular monthly or annual schedule, so you can't use the NPV or IRR functions to calculate net present value and internal rate of return. Excel does have two functions, XNPV and XIRR, that enable you to calculate net present value and internal rate of return for cash flows that occur at irregular intervals. The XNPV function has three arguments, the discount rate, the cash flows, and the dates the cash flows occur.
XIRR also has three arguments: the cash flows and the dates of those cash flows, both of which are required, and if you want, a guess at the internal rate of return. Both the XNPV discount rate and your guess, if any, at the internal rate of return, should be expressed as annual percentage rates. So let's take a look at the two examples I have here. Actually, it's the same example twice. We are just going to calculate XNPV for one and XIRR for the other. So to calculate XNPV, type =XNPV, put in the rate, which is in cell B5, which we divide by 12 because we assume interest will be compounded monthly.
Now we enter the values for the cash flows. Those run from B8 to B16. Now we can enter the dates, those go from A8 to A16, close the parenthesis, and we have an XNPV or net present value of $24,158.80. Now let's take a look at the same investment but this time in terms of its internal rate of return. Type =XIRR. We have the values from cell E8 to E16, the dates from cell D8 to D16.
We'll leave out a guess. I think Excel's default starting value will be fine. Close the parenthesis. Hit Return. This investment has an internal rate of return of 10.77%. You are very unlikely to find any sort of a guaranteed investment with that high of a rate. So it looks like this investment is a good deal, assuming the risks are in line with what your investment strategy holds. Whenever you need to evaluate investments, where cash flows occur at irregular intervals, use the XNPV and XIRR functions.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69958 Viewers
80 Video lessons · 127380 Viewers
52 Video lessons · 62336 Viewers
59 Video lessons · 48031 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.