Dynamically construct range to use in XIRR formula

excel excel-2011
Karthikeyan picture Karthikeyan · Oct 8, 2013 · Viewed 7.1k times · Source

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.

Answer

barry houdini picture barry houdini · Oct 8, 2013

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

Related questions

Reordering Chart Data Series

How does one reorder series used to create a chart in Excel? For example, I go to the chart, right click > Select Data. In the left column I see series 1, series 2, to series n. Say, I want to move …

Read More
Disable all dialog boxes in Excel while running VB script?

I have some code in VB that saves all XLSM files as XLSX. I already have the code that will do that for me, but dialog boxes show up for every action. This was fine for a few dozen files. …

Read More
Array formula on Excel for Mac

I need to call LINEST in Excel 2011 for Mac, I would say - this was a detective story. My laziness, my curiosity, all was here! I had a homework from university course to use LINEST. I even stopped trying to …

Read More