Dynamic XIRR with zeros the numbers

[wspphish123]

Hi all - I am trying to use the xirr function but running into challenges. I am a series’s of dates starting in A1, and series of cashflow in B1. The challenge I am running into is that if I have zeros in the first cell then negative for outflow before inflow starts, excel doesn’t calculate the irr. Any suggestion for using index match or offset to make it dynamic so I am not having to drag to the array of cash flows?! Thanks

 

[BenMcBen]

If your on a recent version (16/365) then this sounds like an ideal application of the filter function (please see help for full description). You can either use this directly embedded within your xirr function, or enter it as an additional output range - passing the first cell reference followed by “#” (which tells excel to pass the entire spill range) into xirr. The first approach is more compact, but might be worth doing it the second way until you are sure the filter function is doing what you want, and then embed it inside…

Hth

Ben

 

[wspphish123]

Thank you for responding to this. Can you please provide an equation for this? I keep getting o% in E6. Thanks a bunch!

 

[BenMcBen]

No I’m afraid not - I’m more a teach a man to fish (or at least point him toward a lake). So you have tried to use the xirr function on a test data set I hope and got the result you expect. Next use the filter function to try and select the data as you expect xirr to require. Then join the two together. Someone else may be along…

 

[Eric W]

Here's a version that computes the XIRR based on the first non-zero value in the range. It's very sensitive to how you have your data organized, so if have a different layout, let me know. Also, please update your user profile with the version of Excel you're using.

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3						1/1/2021	3/1/2021	5/1/2021	7/1/2021	9/1/2021	11/1/2021	1/1/2022	3/1/2022	5/1/2022	7/1/2022	9/1/2022	11/1/2022	1/1/2023	7/1/2025
4						0	0	0	0	0	-200	0	10	10	10	5	2	20	300
5
6						0%
7						21%
8
9						21%
Sheet12

Cell Formulas
Range	Formula
F6	F6	=XIRR(F4:S4,F3:S3)
F7	F7	=XIRR(K4:S4,K3:S3)
F9	F9	=XIRR(INDEX(4:4,AGGREGATE(15,6,COLUMN(F4:S4)/(F4:S4<>0),1)):S4,INDEX(3:3,AGGREGATE(15,6,COLUMN(F4:S4)/(F4:S4<>0),1)):S3)

 

[wspphish123]

this is awesome, thank you! it works. just curious. why do you have to do the array of 3:3 or 4:4 and what is the 15 or 6 for in the equation?

sometimes i realize excel is just an amazing tool. learn something new every day. Thank you!!

 

[Eric W]

Maybe it would be a bit clearer if I wrote it like this:

=XIRR(INDEX(F4:S4,AGGREGATE(15,6,(COLUMN(F4:S4)-COLUMN(F4)+1)/(F4:S4<>0),1)):S4,INDEX(F3:S3,AGGREGATE(15,6,(COLUMN(F4:S4)-COLUMN(F4)+1)/(F4:S4<>0),1)):S3)

In this version I used the actual ranges instead of using 3:3 and 4:4, which are shortcut descriptions of saying "row 3" and "row 4". The original formula used COLUMN(F4:S4) to look at the column numbers of your range, then when it found the smallest column with a non-zero amount, plugged it into the row. The one I just posted uses the offset into the range, COLUMN(F4:S4)-COLUMN(F4)+1. The first way is a bit shorter, but the second may be clearer.

As far as the 15 and 6, they are parameters to the AGGREGATE function. AGGREGATE actually is a collection of several different functions. 15 means LEAST, and 6 means to ignore any error values in the list. I used AGGREGATE instead of LEAST because LEAST won't ignore errors, which I intentionally introduced by dividing by (F4:S4<>0).

Anyway, glad I could help!