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 Addin is installed and is enabled (Tools/Addins/Analysis ToolPak); How do you calculate XIRR if the Analysis ToolPak AddIn 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 AddIn 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:
Date 
Cash Flows 

01Jan2007 
50000 
10Jan2007 
500 
01Jun2007 
500 
25Oct2007 
500 
31Dec2007 
500 
01Mar2008 
500 
15Jun2008 
51000 
IRR 
? 
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.1Jan07 in the above example  through every incremental date i.e., 2Jan07, 3Jan07 etc., in each succeeding row  until the last date of the cash flows i.e., 15Jun08 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.
