Help with XIRR, Formula doesn't work

Hello,

I am trying to caculate some XIRR for some loans (negative cash flow is lent out, positive cash flow is money in). As you can see, row 3 works perfectly. but the other rows do not work. Please help! Thanks!

Originally Posted by tonylyx

I am trying to caculate some XIRR for some loans (negative cash flow is lent out, positive cash flow is money in). As you can see, row 3 works perfectly. but the other rows do not work. Please help! Thanks!

You have numerous errors, demonstrated by the table below.

	A	B	C	D
1			2.98023E-09	=XIRR(B1:B3,A1:A3)
2	1/1/2015	1200	#NUM!	=XIRR(B2:B3,A2:A3)
3	1/1/2014	-1100
4
5			2.98023E-09	=XIRR(B5:B9,A5:A9)
6	1/1/2014	-1000	19.9999994%	=XIRR(B6:B9,A6:A9)
7
8	1/1/2015	1200
9
10			#VALUE!	=XIRR(B6:B10,A6:A10)
11			FALSE	=ISBLANK(B10)
12			TRUE	=ISTEXT(B10)

The formulas in column C are shown in column D.


1. Just because XIRR returns about 0.0% (actually about 2.98023E-09) does not mean it "works", meaning it returns a correct value. In fact, it appears that XIRR returns 2.98023E-09 when there is one or more empty rows at the beginning of the ranges, as demonstrated by C1 and C5.

That is why XIRR returns about 0.0% in B3 and B4 of your example.


2. Generally, XIRR does tolerate empty rows in the middle and the end of the ranges, as demonstrated by C6. By "empty", I mean: no formula and no constant; ISBLANK returns TRUE.


3. However, XIRR returns the #VALUE error if any cells in the ranges contain text, as demonstrated by C10. Even though B10 looks empty, the formulas in C11 and C12 confirm that it contains text. B10 was created by copying the null string and pasting-special-value into B10, creating the null string constant.

That is why XIRR returns the #VALUE error in B5:B29 of your example. At least some of the apparently empty cells in column O, namely O5 and O7:O29, actually contain the null string constant. P6 also contains the null string constant, causing the #VALUE error in B6. There might be more instances of the null string constant; I did not look.


4. Although XIRR does not require dates to be in order, it does require the first date to be the earliest, as demonstrated by C2. If not, XIRR returns the #NUM error.

Note that the dates are in reverse order in row 2 of your example. So even if you corrected the errors noted above, XIRR would return a #NUM error.


5. I did not try to vet your XIRR usage with your example data if the errors noted above were corrected. So there might be other mistakes (TBD).

Thank you!