1

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.

Karthikeyan
  • 143
  • 3
  • 9

1 Answers1

1

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81