XIRR Calculation in Excel

We explain XIRR calculation logic in this article. As we know, Excel's IRR function is used to calculate internal rate of return for a series of cash flows that occur at regular (uniform) intervals. XIRR is a more powerful function in Excel for calculating internal rate of return or annualized yield for a schedule of cash flows occurring at irregular intervals. However, this function can be used only if the Analysis ToolPak Add-in is installed and is enabled (Tools/Add-ins/Analysis ToolPak); How do you calculate XIRR if the Analysis ToolPak Add-In is not installed and you are not able to trace your MS Office CD readily. Here is a workaround solution; The attempt here is not so much to offer a substitute for the Add-In but to give an insight into how Excel handles irregular intervals of time to compute (X)IRR; In fact, it is interesting to note that it is much simpler than one would have thought as explained below:

Demystifying XIRR calculation:

Lets say we have a set of cash flows like those in the table below:

As you may notice from the above table, the cash flows are occurring at irregular intervals. If you have Analysis Toolpak installed, you can use XIRR function to compute the IRR for these cash flows using "=XIRR(cash flow range, date range)" formula.

In the alternative approach or the workaround that I mentioned above, we need to build a new daily schedule of dates in the spreadsheet in a new column starting from the investment date i.e.1-Jan-07 in the above example - through every incremental date i.e., 2-Jan-07, 3-Jan-07 etc., in each succeeding row - until the last date of the cash flows i.e., 15-Jun-08 in this example. Yes, the cell range will be too large, in fact as large as the number of days between the start date and end date of cash flows; One can use Excel Fill Series (Date) option to enter these dates. Then, enter relevant cash flow values against these dates in adjacent column; enter zeroes (0) against dates that do not have any cash outflow or inflows; it is important not to leave these cells blank. Now, calculate IRR for these cash flow values (just ignore the dates in the column on the left) using normal IRR function (which may look like "=IRR(values, guess rate)";

In Step 2, we need to multiply this value of IRR by 365 to get annual IRR (since, these are daily cash flows).

Finally, in Step 3, the result in Step 2 is further refined using the formula =( 1+ R / 365) ^ 365 - 1), where R is the the value obtained in Step2. The resulting value in fact is exactly same as the one computed by Excel using XIRR function for the original set of cash flows. Yes, we have calculated XIRR without the Analysis Toolpak Addin ! The actual working explained in this example may be seen in this spreadsheet.

If you want to quickly calculate IRR of a series of irregular cash flows without using using Excel, you can use this Online XIRR Calculator.