To help you manage your business investments, Microsoft Excel includes many timesaving functions, including an annual growth rate formula called internal rate of return (IRR). It automatically calculates the average annual rate of return based on a list of transaction amounts where cash flows occur regularly. A second function, XIRR, gives you annual rates of return for investments where the money is paid out at irregular intervals.
TL;DR (Too Long; Didn't Read)
The IRR() function in Excel acts as an annual rate of return calculator for investments that pay out at regular intervals.
What is Annual Rate of Return?
The annual rate of return for an investment is the percentage change of the total dollar amount from one year to the next. If the investment made a profit, the percentage is positive. Investment losses give negative percentages.
Average Annual Rate of Return
The average annual rate of return of your investment is the percentage change over several years, averaged out per year. A bank might guarantee a fixed rate per year, but the performance of many other investments varies from year to year. It helps to average the percentage change so you have a single number against which to compare other investments.
Example Average Annual Rate of Return
As an example, a bank offers a 1.5% rate on a certificate of deposit. By comparison, a business investment may lose 2% one year but gain 6% the next. The total compounded gain for the investment is (1-.02) x (1+.06) or 3.88%. The average for 2 years is 3.88/2 or 1.94%, better than the bank rate. Note that you can’t simply average -2% and 6% together, such as (-2 + 6) / 2 or exactly 2; compounding affects the results.
Annual Rate of Return Excel IRR()
The Excel function IRR() takes a list of amounts that are usually set up in a column. For the function to work, the list must have at least one negative and one positive amount. The first negative amount represents the initial funds that went into the investment. The function assumes that cash flows occur on regular intervals, once per interval. For example, you enter the following dollar amounts into column A, starting at A1:
In A7, you enter the formula, IRR(A1:A6). These items represent an initial investment of $100,000 and payouts in the amounts that follow. Excel calculates the average annual rate of return as 9.52%. Remember that when you enter formulas in Excel, you double-click on the cell and put it in formula mode by pressing the equals key (=). When Excel is in formula mode, type in the formula.
Note that IRR() doesn’t assume that the interval is years. It could instead be months, in which case the return is 9.52% per month. The IRR() function doesn't "know" the interval — you do.
Function for Irregular Cash Flows XIRR()
The XIRR() function works much the same as IRR(), but doesn’t assume regular cash flows. In addition to a column of amounts, you provide a second column of corresponding dates. The function calculates the average annual return based on those two sets of data. To give an example, you have these amounts and dates. The first entry represents the start of the investment:
The amounts go into column A, starting at A1. The dates go to column B, starting with B1. Note that you can’t use dates in Excel as-is; for the math to work, you must use the date function as these examples illustrate:
In B8, the first empty cell under the dates, you enter the following equation:
Excel calculates the average annual rate of return as 0.095, or 9.5%.
An Educated Guess
Both the IRR() and XIRR() have an optional third parameter in which you can provide a “guess” value to the function. In the majority of cases, Excel can calculate the rate of return without the guess. But some sets of data present difficulties with calculations; the guess gives the software a starting point, and the function “homes in” from there. For example, if the function returns a calculation and you think the rate of return is close to 5%, use 5% for the guess, as follows:
Chicago native John Papiewski has many years' experience in IT consulting, and has worked with businesses including finance, real estate, distribution and publishing. His articles have appeared in various outlets including azcentral.com and seattlepi.com. Please, no workplace calls/emails!