How to Calculate the Average Annual Rate of Return in Excel
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.
Tip
The IRR() function in Excel acts as an annual rate of return calculator for investments that pay out at regular intervals.
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.
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.
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.
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:
-100,000.00
25,000.00
30,000.00
23,000.00
27,000.00
25,000.00
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.
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:
-25,000 7/1/22
4500 11/30/22
3250 1/1/23
4500 10/31/23
5000 2/1/24
6375 7/15/24
5000 1/31/25
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%.
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: