Dynamic XIRR for sum of CF's with Offset

[willr2819]

Hello All,

I have noticed that this has been a question in other threads on this forum; however I have been unable to locate this particular question using the formulas in the sheet below (vertically vs horizontally).

Question: Is it possible to make a dynamic XIRR formula to search for the first value < or > 0 and begin calculating it from there? Will the other formulas that are used in this table conflict with the XIRR Offset formula or whatever solution that is proposed?

Something to note is that the start date will always be 1/15/12, which is where I would like the formula to start every time. As for the end date, I can manually update the end date each quarter (12/31/22) and add in dates.

Problem: I am trying to get excel to automatically go to the first value that is found within my XIRR table (vertically) and skip all of the values that are 0's (AKA i want it to look at the first value "<>0"). Any value that is 0 after that number (31,805,623) is fine, but since this will be used on multiple projects, that value could start in a different cell on the next IRR review.

It seems that this person was on the right track for what I wanted to do; however this is a horizontal calculation vs vertical and when I put a zero for the first cash flow in their table, it calculated the IRR as 0.00% which did not make it as dynamic as I thought it would be.

Link: Dynamic XIRR (OFFSET) Transposing Formula Help

If someone can provide me the formula I would need to use in this example, I would greatly appreciate it.

Thank you so much!

EXCEL Question for MR.EXCEL.xlsx
	A	B	C	D	E							L	M	N	O	P
1				Cash Date	IRR Date							Net Amount	Equity Invested	Realized Value
2				1/6/2015	1/15/2015							(33,880,196.00)	(33,880,196.00)	-
3				1/15/2015	1/15/2015							2,074,573.00	2,074,573.00	-
4				2/15/2015	2/15/2015							(118,276.67)	(118,276.67)	-
5				4/15/2015	4/15/2015							1,592,841.00	-	1,592,841.00
6				7/15/2015	7/15/2015							1,906,930.69	-	1,906,930.69
7				10/15/2015	10/15/2015							592,903.43	-	592,903.43
8				10/15/2015	10/15/2015							1,927,885.98	-	1,927,885.98
9				1/26/2016	1/15/2016							586,641.37	-	586,641.37
10				1/15/2016	1/15/2016							1,907,524.25	-	1,907,524.25
11				4/4/2016	4/15/2016							581,316.00	-	581,316.00
12				4/15/2016	4/15/2016							1,210,142.92	-	1,210,142.92
13				4/20/2016	4/15/2016							1,890,207.00	-	1,890,207.00
14				7/25/2016	7/15/2016							1,756,952.00	-	1,756,952.00
15				10/1/2016	10/15/2016							1,893,133.05	-	1,893,133.05
16				9/30/2019	9/15/2019							2,237.82	2,237.82	-
17				12/22/2021	12/15/2021							(1,576.68)	-	(1,576.68)
18			LOC Payoff	12/31/2022	12/31/2022							-	-
19			Unrealized Value	12/31/2022	12/31/2022							250,000.00	-	250,000.00
20
21													1/15/2012	-
22													2/15/2014	-
23													1/15/2015	(31,805,623.00)
24													2/15/2015	(118,276.67)
25													3/15/2015	-
26													4/15/2015	1,592,841.00
27													5/15/2015	-
28													7/15/2015	1,906,930.69
29													8/15/2015	-
30													9/15/2015	-
31													10/15/2015	2,520,789.41
32													11/15/2015	-
33													12/15/2015	-
34													1/15/2016	2,494,165.62
35													2/15/2016	-
36													3/15/2016	-
37													4/15/2016	3,681,665.92
38													5/15/2016	-
39													6/15/2016	-
40													7/15/2016	1,756,952.00
41													8/15/2016	-
42													9/15/2016	-
43													10/15/2016	1,893,133.05
44													11/15/2016	-
45													7/15/2019	-
46													8/15/2019	-
47													9/15/2019	2,237.82
48													10/15/2021	-
49													11/15/2021	-
50													12/15/2021	(1,576.68)
51													11/15/2022	-
52													12/15/2022	-
53													12/31/2022	250,000.00
54														(15,826,760.84)
55
56													XIRR Calculation (Manual)	-34.31%	<- this is the correct XIRR
57													Automatic XIRR (Offset)
Sheet2

Cell Formulas
Range	Formula
N19	N19	=L19
M24:M27,M52,M49,M46:M47,M29:M44	M24	=DATE(YEAR(M23),MONTH(M23)+INT(1),DAY(M23)+MOD(1,1)*30)
N21:N53	N21	=SUMIF($E$1:$E$1242,M21,$L$1:$L$1242)
N54	N54	=SUM(N23:N53)
N56	N56	=XIRR(N23:N53,M23:M53)

 

[DRSteele]

I think this is helpful, if I understand what you're after.

N57: =XIRR(FILTER(N21:N53,N21:N53<>0),FILTER(M21:M53,N21:N53<>0))

 

[willr2819]

I think this is helpful, if I understand what you're after.

N57: =XIRR(FILTER(N21:N53,N21:N53<>0),FILTER(M21:M53,N21:N53<>0))

This is exactly what I was looking for thank you so much!

 

[DRSteele]

Super!

You're welcome.