Search code examples
excelexcel-2011

Dynamically construct range to use in XIRR formula

I have a sheet in this form:

Fund | Date      | Amount
A    | 10-Jan-05 | -5000
A    | 10-Feb-05 | -5000
A    | 08-Oct-13 | 12500
B    | 10-Sep-05 | -5000
B    | 10-Oct-05 | -5000
B    | 10-Nov-05 | -5000
B    | 08-Oct-13 | 22500

I'm looking for an output column that provides the XIRR for each fund. The XIRR function takes values and dates as ranges. I need a way to dynamically construct the range using the fund name as a search criteria. 

Fund | XIRR
A    | ...
B    | ...

I could manually specify the range for each fund after sorting it by name, but its not a scalable solution.

Solution

  • You can use OFFSET to get the correct ranges, e.g. this formula

    =XIRR(OFFSET(C$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)),OFFSET(B$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)))

    see screenshot

    enter image description here