Strange behaviour from XIRR

[DRSteele]

Can someone please help me?

This template shows how XIRR yields some baffling results even when all the rules are followed for its use. Here, there is a strange inflection point where the function shows wrong results (shown in red) and right results (in green) when one value is changed slightly.

What could be happening?

MrExcel posts19.xlsx
	D	E	F	G	H
2	Date	Value1	Value2	Value3	Value4
3	4/12/2021	-3,309.95	-3,309.95	-3,309.95	-3,309.95
4	4/26/2021	-2,249.95	-2,249.95	-2,249.95	-2,249.95
5	5/25/2021	-3,409.95	-3,409.95	-3,409.95	-3,409.95
6	7/12/2021	-1,909.95	-1,909.95	-1,909.95	-1,909.95
7	8/25/2021	-9,809.95	-9,809.95	-9,809.95	-9,809.95
8	1/7/2022	1.00	14,069.8490	14,069.8500	100,000.00
9
10	XIRR	0.000000298%	0.000000298%	-53.129091989%	1634.566221237%
Sheet1 (2)

Cell Formulas
Range	Formula
F3:F7	F3	=H3
G3:G7	G3	=F3
H3:H7	H3	=E3
E10:H10	E10	=XIRR(E3:E8,$D3:$D8)

 

[BSALV]

complet logic.
You have 5 cash out's for total almost 21.000 and 1 cash income.
If that one is greater then the sum Excel can calculate a positive irr.
Otherwise it's like your money undergoes a devaluation, i don't know if that is compounded

 

[DRSteele]

complet logic.
You have 5 cash out's for total almost 21.000 and 1 cash income.
If that one is greater then the sum Excel can calculate a positive irr.
Otherwise it's like your money undergoes a devaluation, i don't know if that is compounded

Thank you for contributing.

If you look closely enough, you will see that the cash flows are identical except for one: moving from cell F8 to G8 is a difference of a mere tenth of a cent. That inflection point is where the function does not work to where it does.

 

[BSALV]

=XIRR(E3:E8,$D3:$D8,IF(E8>14000,0.1,-0.3))

give excel a hand, make the last argument -0.3 (or -30%) and i goes on until -100%

fermantation.xlsm
	K	L	M	N	O	P	Q	R	S
1	E8	XIRR
2	23000	6,68%
3	22750	5,97%
4	22500	5,26%
5	22250	4,54%
6	22000	3,82%
7	21750	3,10%
8	21500	2,37%
9	21250	1,65%
10	21000	0,91%
11	20750	0,18%
12	20500	-0,56%
13	20250	-1,30%
14	20000	-2,05%
15	19750	-2,80%
16	19500	-3,55%
17	19250	-4,31%
18	19000	-5,07%
19	18750	-5,84%
20	18500	-6,60%
21	18250	-7,38%
22	18000	-8,16%
23	17750	-8,94%
24	17500	-9,72%
25	17250	-10,51%
26	17000	-11,31%
27	16750	-12,11%
28	16500	-12,91%
29	16250	-13,72%
30	16000	-14,53%
31	15750	-15,35%
32	15500	-16,17%
33	15250	-17,00%
34	15000	-17,84%
35	14750	-18,68%
36	14500	-19,52%
37	14250	-20,37%
38	14000	-21,23%
39	13750	-22,09%
40	13500	-22,96%
41	13250	-23,84%
42	13000	-24,72%
43	12750	-25,61%
44	12500	-26,50%
45	12250	-27,40%
46	12000	-28,31%
47	11750	-29,23%
48	11500	-30,15%
49	11250	-31,09%
50	11000	-32,03%
51	10750	-32,97%
52	10500	-33,93%
53	10250	-34,90%
54	10000	-35,87%
55	9750	-36,86%
56	9500	-37,85%
57	9250	-38,86%
58	9000	-39,88%
59	8750	-40,90%
60	8500	-41,94%
61	8250	-42,99%
62	8000	-44,05%
63	7750	-45,13%
64	7500	-46,22%
65	7250	-47,32%
66	7000	-48,44%
67	6750	-49,58%
68	6500	-50,73%
69	6250	-51,90%
70	6000	-53,08%
71	5750	-54,29%
72	5500	-55,52%
73	5250	-56,77%
74	5000	-58,04%
75	4750	-59,34%
76	4500	-60,66%
77	4250	-62,01%
78	4000	-63,40%
79	3750	-64,82%
80	3500	-66,27%
81	3250	-67,77%
82	3000	-69,31%
83	2750	-70,90%
84	2500	-72,55%
85	2250	-74,27%
86	2000	-76,06%
87	1750	-77,95%
88	1500	-79,93%
89	1250	-82,06%
90	1000	-84,35%
91	750	-86,89%
92	500	-89,78%
93	10	-0,99084
94	0	#GETAL!
Blad1