XIRR – A godsend for calculating Return on Investment

Most users of Excel would be familiar with the IRR function. This function returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. The catch here is regular intervals.

Consider the following example:

Assume that you have an investment pattern which is based upon having surplus cash at the end of the month. You have chosen to invest your surplus amount in a mutual fund. Bear in mind that this is an adhoc investment and is done only if you have surplus cash available at the end of the month. Let us assume on 1-Jan-2008, you had 500 Rs surplus and invested it in buying 50 units of a chosen MF at a NAV of Rs 10. You did not have any surplus cash during January and February 08 and your next investment was only in March 08 where you used a surplus of 1000 to by 80 units of the MF at a NAV of Rs 12.5. You also ended up with surplus cash in April to the tune of 550 Rs and bought 50 units of the MF at a NAV of Rs 11.

So your net investment pattern would be as shown in the table below

Let us assume that the NAV of the mutual Fund on 1-May-08 is Rs 15. This means that the Net Value of the overall investment on 1-May-08 = (180*15) = 2700. IRR will not help out here as the irregular nature of the payments will result in a wrong value. Step in Mr. XIRR. The formula takes in 2 sets of parameters, an array called values and an array called Dates. So to model this example, consider the following usage in Excel

As you can see, IRR assumes a yearly pattern by default while XIRR factors into account, the periodicity element by using the dates.