Dynamic XIRR (OFFSET) Transposing Formula Help

[privateequity]

Hi, I am trying to calculate XIRR using dynamic ranges. I found the solution in the attached URL but the data and formula would need to be transposed into a new format (according to how my model is built). Are you able to help me reconstruct the formula according to how I have my data transposed?

Here is the solution to the formula: https://stackoverflow.com/questions/19254548/dynamically-construct-range-to-use-in-xirr-formula

I'd like to transpose it so Fund, Date, and Amount are vertical along A1, A2, A3, and then the information is transposed horizontally to the right of each.

Would you be able to assist?

Thanks so much.

 

[StephenCrump]

By simply adapting the Stack Overflow formula (there may be other ways):

B11:=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)))

C11: =XIRR(OFFSET(G$3,,MATCH(A11,G$1:M$1,0)-1,,COUNTIF(G$1:M$1,A11)),OFFSET(G$2,,MATCH(A11,G$1:M$1,0)-1,,COUNTIF(G$1:M$1,A11)))

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Fund	Date	Amount			Fund	A	A	A	B	B	B	B
2	A	10-Jan-05	-5,000	10-Jan-05		Date	10-Jan-05	10-Feb-05	08-Oct-13	10-Sep-05	10-Oct-05	10-Nov-05	08-Oct-13
3	A	10-Feb-05	-5,000	10-Feb-05		Amount	-5,000	-5,000	12,500	-5,000	-5,000	-5,000	22,500
4	A	08-Oct-13	12,500	08-Oct-13
5	B	10-Sep-05	-5,000	10-Sep-05
6	B	10-Oct-05	-5,000	10-Oct-05
7	B	10-Nov-05	-5,000	10-Nov-05
8	B	08-Oct-13	22,500	08-Oct-13
9
10		Vertical	Horizontal
11	A	2.6%	2.6%
12	B	5.2%	5.2%
1

 

[privateequity]

Thank you very much. That worked.

 

[privateequity]

Hey, just wondering, what exactly is the "-1" in the formula after the MATCH?

 

[privateequity]

Don't worry. It totally works perfectly. Thanks again.

 

[StephenCrump]

You're welcome.