Learn how you can use the XIRR function to calculate the internal rate of return for cash flows that occur at irregular intervals.
- [Announcer] When you calculate a projects internal rate of return, you find the discount rate, that is the risk-free interest rate, at which the project would have a net present value of zero. One important restriction of the straight up internal rate of return calculation is, that all the cash flows must occur at regular intervals, every year, every month, that sort of thing. In this movie, I will show you how to calculate the internal rate of return of cash flows that happen on an irregular basis.
As my sample file, I will use the IrregularIRR workbook, and you can find that in the chapter two folder of your exercise files collection. To calculate the internal rate of return using irregular return dates, you need to know two different things, and a third argument for the formula is optional. The first thing you need to know, are your cash flows. So you have the amounts here, in cells B4 through B11. And you also need to know the dates on which the cash flows occur.
So here we have the first one, which is the investment, the negative amount, happening on August 2nd, 2017, and then the remaining cash flows happen on the dates noted in column A. Finally, and this is an optional argument, you can give the XIRR function a guess at what you think the internal rate of return might be. If you don't give it that argument, then it assumes 10 percent, but I have made that value here explicit, in cell B12, just so we'll know what's going into the formula.
I'll create my calculation in cell B14, so there I'll type an equal sign to start the formula, and the function I'll use, is the name in cell A14, so that's XIRR, then after the left parentheses I need to enter in the values. Those were cells B4 through B11, then a comma. The dates are in the cells right next to the cash flows in this case, so A4 through A11, then a comma, and I'll enter in the cell reference for the guess, which is in cell B12.
Type a right parentheses, and press enter, and I see an internal rate of return of 40 percent. Assuming that my estimates are accurate, this result means that the investment, assuming the cash flows are correct, would have a net present value of zero, if the discount rate, that is the risk free investment rate, were 40 percent. Given that most government bonds, which are considered risk free, pay at most three and a half percent, this 40 percent looks like a very good proposition.
- 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