The Microsoft Excel XIRR function is a modified version of the IRR function, wherein one can also calculate internal rate of return even if the cash flow does not happen regularly. The Excel IRR function is only for calculating internal rate of return when there is a periodic flow of cash, and thus the XIRR function is capable of doing more complex calculations. It is a more useful Excell function, considering the fact that in most practical situations, the cash flows happen in frequent but irregular intervals. This function would be able to calculate based on a range of cash flow and date range.
The syntax of the function is quite simple: XIRR (amount, dates, guess). While it is mandatory to enter the range for amount and date, the parameter “guess” is optional.
Amount: The first parameter denotes the range of amounts which are entered in the Excel spreadsheet. It signifies the series of cash flows in a scheduled payment scheme. The first payment value is optional as it is basically the payment made at the beginning of the investment. The value would be preceded by a negative sign, if it corresponds to a payment. However, the series of amounts must contain a positive and a negative value. For example, if in an excel sheet, the range of values are entered from the column A1 to column A6, the parameter would be entered as A1:A6.
Dates: The values of the dates corresponding to the values entered above would be entered in this parameter through the address range of the cells where the dates are entered. However, the point to be noted here is that, it is safe to use the DATE function instead of inserting the date as a string. Therefore, in the Microsoft Excel spreadsheet, the date should be entered as DATE (2011, 08, 17) if the date to be entered is 17th of August in 2011. As for the entry in the parameter field, it would cover the address range, such as B1:B6 (extending the example used to describe the “Values” parameter. However, the first date always is indicative of the beginning of the payment schedule.
Guess: As the name suggests, this parameter is an optional field which captures the initial guess of the user. It enables the user to think and guess a value which is the nearest to the result of the function. Since it is optional, most users decide to ignore entering any value in the parameter. However, in case the guess value is not entered by the user, a default value of 0.10 is selected.
There are certain aspects of the function that needs to be properly maintained in order to ensure proper results. As mentioned earlier, there should be a positive as well as a negative cash flow. This is important as without both the cash flows, the result field would display an error. Instead of a percentage, one would get an error value such as #NUM! The same error value would be displayed if there is a date which is older than the date of start, and if the range of values and dates do not have the same total count.
However, if the date is not properly recognized by Microsoft Excel, or the function is not present in the system, users would be shown the error value of #VALUE! One must be always use the DATE function to avoid any such errors. In case the function is not available, the Analysis ToolPak add-in should be installed in order to work properly.
Let us consider the following five values against the five dates entered in an excel sheet as:
-10,000 1-Jan-10
3,000 1-Mar-10
4,000 30-Oct-10
3,000 15-Feb-11
3,000 1-Apr-11
Then, the formula for calculating the internal rate of return would be entered as (A1:A5, B1:B5, 0.1) which would fetch a result of 0.38163. This signifies that if the internal rate of return in case of the above values for the above time period is 38.16%.
There is a very complex formula working behind the function where Microsoft Excel consultants an iterative procedure to calculate the value. It takes the guess value as a starting point, but the end value does not differ much if the guess value is changed. Therefore, one may even skip entering any guess value.