How to dynamically use XIRR based on the investment name?

[chrishlee1228]

Hello, I'm trying to make the XIRR column dynamic by allowing just one formula, column A is hidden for confidentiality but that's the investment name. Would like to create a formula where it matches the investment name by date, and by total columns. XIRR column =XIRR(F2:F4,B2:B4), but it is time consuming to adjust the range each time for every investment, for example for the 2nd set, I have to adjust the range so it captures only 2 instead of 3, and then the third one I have to adjust the range to 4.

Date	Transaction	Cost	Value	Total	XIRR
03/12/20	Series A Preferred	(9,000,000.00)		(9,000,000.00)
09/09/20	Series B Preferred	(7,000,000.00)		(7,000,000.00)
03/31/22	FMV		81,634,031.00	81,634,031.00	140.06%​
07/15/21	Series A Preferred	(3,749,997.74)		(3,749,997.74)
03/31/22	FMV		3,749,998.00	3,749,998.00
10/22/20	Series Seed Preferred	(1,199,999.00)		(1,199,999.00)
03/31/21	Series A Preferred	(1,481,481.00)		(1,481,481.00)
06/15/21	Series A Preferred	(84,533.00)		(84,533.00)
03/31/22	FMV		9,124,017.00	9,124,017.00

 

[Domenic]

I have assumed that your data is as follows (notice I've used fake investment names) . . .

A1:G12

Investment Name	Date	Transaction	Cost	Value	Total	XIRR
Investment A	3/12/20​	Series A Preferred	-9,000,000.00​		-9,000,000.00​
Investment A	9/09/20​	Series B Preferred	-7,000,000.00​		-7,000,000.00​
Investment A	3/31/22​	FMV		81,634,031.00​	81,634,031.00​	140.06%
Investment B	7/15/21​	Series A Preferred	-3,749,997.74​		-3,749,997.74​
Investment B	3/31/22​	FMV		3,749,998.00​	3,749,998.00​	0.00%
Investment C	10/22/20​	Series Seed Preferred	-1,199,999.00​		-1,199,999.00​
Investment C	3/31/21​	Series A Preferred	-1,481,481.00​		-1,481,481.00​
Investment C	6/15/21​	Series A Preferred	-84,533.00​		-84,533.00​
Investment C	3/31/22​	FMV		9,124,017.00​	9,124,017.00​	168.35%

G2, copied down:

=IF(LEN(A2)>0,IF(LEN(A3)=0,LET(data,FILTER($A$2:$F$12,($A$2:$A$12=A2)),XIRR(INDEX(data,0,6),INDEX(data,0,2))),""),"")

You'll notice that the formula returns a value for any row where the cell in Column A contains a value, and the cell below it is blank.

Hope this helps!

 

[chrishlee1228]

I have assumed that your data is as follows (notice I've used fake investment names) . . .

A1:G12

Investment Name	Date	Transaction	Cost	Value	Total	XIRR
Investment A	3/12/20​	Series A Preferred	-9,000,000.00​		-9,000,000.00​
Investment A	9/09/20​	Series B Preferred	-7,000,000.00​		-7,000,000.00​
Investment A	3/31/22​	FMV		81,634,031.00​	81,634,031.00​	140.06%
Investment B	7/15/21​	Series A Preferred	-3,749,997.74​		-3,749,997.74​
Investment B	3/31/22​	FMV		3,749,998.00​	3,749,998.00​	0.00%
Investment C	10/22/20​	Series Seed Preferred	-1,199,999.00​		-1,199,999.00​
Investment C	3/31/21​	Series A Preferred	-1,481,481.00​		-1,481,481.00​
Investment C	6/15/21​	Series A Preferred	-84,533.00​		-84,533.00​
Investment C	3/31/22​	FMV		9,124,017.00​	9,124,017.00​	168.35%

G2, copied down:

=IF(LEN(A2)>0,IF(LEN(A3)=0,LET(data,FILTER($A$2:$F$12,($A$2:$A$12=A2)),XIRR(INDEX(data,0,6),INDEX(data,0,2))),""),"")

You'll notice that the formula returns a value for any row where the cell in Column A contains a value, and the cell below it is blank.

Hope this helps!

This works perfectly, thank you! I did change one thing though where if it says "FMV", then calculate, if not, then zero:
=IF(C2="FMV",LET(data,FILTER($A$2:$F$500,($A$2:$A$500=A2)),XIRR(INDEX(data,0,6),INDEX(data,0,2))),"")

Best,
Chris

 

[Domenic]

That's great, glad you were able to make the necessary changes to make it work.

Cheers!