More on the IRR Function in Excel

This article is an expansion to a prior article covering the time value of money. Because many financial modelers require information yielding returns, it is important to understand better the IRR functions, not only from an Excel perspective, but also the underlying financial theory behind the functions.

The IRR function is used in Excel to calculate the internal rate of return. What is the internal rate of return? The internal rate of return (or, of course, IRR), is the rate earned on an investment over a defined period of time. In other words, let’s say that there is an investment of $10,000 at the end of the year, and in each of the five subsequent years, the payments are $500, $600, $700, $800, $900 plus the original $10,000 returned at the end of year five. The IRR would be 6.9% and represents the yield earned on the original investment based on the periodic payments and the return of the original investment.

As one may be able to discern, in the prior example the periodic payments are not the same. Thus, one cannot use the RATE function, which assumes that payments are consistent over some timeframe. So the first thing learned from this is that when there are inconsistent periodic payments, one must use the IRR function. The second concept learned is that the payments MUST BE periodic for the IRR function. In other words, cash flows need to be every year, end of quarter or end of month. The third item that needs to be explained is that there can only be on change in sign for any list of payments, e.g., the initial investment would be entered as -$10,000 (cash outflow) with the subsequent payments having a positive sign (cash inflows).

For a bit of background, an underlying assumption of the IRR is that all cash inflows (or outflows) are reinvested at the IRR. Confused? Think of it this way, if the answer to an IRR question is 8.0%, it is assumed that the periodic cash flows are being reinvested at the 8.0% rate. So, even in reality if the flows were invested at varying rates, it is implicit by the calculation that they were invested at 8.0%. For most analyses and financial modeling, and in the absence of reinvestment rate specific, the IRR will yield a result that can help to provide a solution to the project (particularly when used in conjunction with the NPV function). To avoid unnecessary complication, the IRR can be thought of as an iterative process, which thanks to computing power, is done much more quickly than we can do by hand.

If there is information on a different reinvestment rate, then one would use the modified internal rate of return function, or MIRR (=MIRR(values, finance_rate,reinvest_rate)). This function assumes that you can take your periodic cash flows and invest at some rate other than the IRR, and incorporates a finance rate, which is the rate on the initial investment. Said more simply, if the finance rate equals the reinvestment rate, you would only need the IRR function. If, for example, we revisit the original example and set the finance rate equal to the original IRR, but change the reinvestment rate to 5.0%, the overall return is reduced to 6.68% (clearly, lower reinvestment rate than the original IRR). If the reinvestment rate is 8.0%, the overall return is 6.99%. While this is a function that is likely not be used frequently, it may arise and it is helpful to know that there is a formula to handle a situation like this.

Finally, there are instances in which the cash flows are not periodic, and the IRR function will not provide an accurate result. Excel provides the XIRR function, which takes into consideration irregular payments. For example, referring to the original example, assume that those payments occurred as follows: original investment 12/31/04, subsequent payments on 3/31/05, 4/30/06, 6/30/07, 8/31/08 and final payment and return of investment on 12/15/09. Using the XIRR function, the overall return would be 7.12%. As one can see, the timing of the payments impacts the returns, a fundamental truism of the time value of money. Most of the time, the IRR function will be used, but when there is an instance of specific irregular payments, the XIRR function should be used.