# XIRR not working for this dataset. Any other method to calculate rate of return?

For this particular dataset xirr is not returning any reasonable value either in excel or google spreadsheet. What is the option to calculate the rate of return in cases like this??

Sl-no  date  amount
1  2016-02-16  -13660
2  2016-03-11  -14540
3  2016-04-20  -14960
4  2016-05-23  -15420
5  2016-06-16  -15080
6  2016-07-16  -15080
7  2016-08-16  -15400
8  2016-09-16  -15800
9  2016-10-16  -16000
10  2016-11-16  -16300
11  2016-12-16  381200
12  2017-01-16  -17050
13  2017-02-16  -17200
14  2017-03-16  -17480
15  2017-04-16  -17730
16  2017-05-16  -17730
17  2017-06-16  -18450
18  2017-07-16  -18630
19  2017-08-16  -19100
20  2017-09-16  -19200
21  2017-10-16  -19200
22  2017-11-16  -19300
23  2017-12-16  -19880
24  2018-01-16  -19900
25  2018-02-16  -20000

The reason I am expecting it to have a reasonable rate is because this other dataset which is only slightly different (only in the data of Sl no 10 and 11) seems to return a value of .344 with xirr in google sheets.

Sl-no date amount
1 2016-02-16 -13660
2 2016-03-11 -14540
3 2016-04-20 -14960
4 2016-05-23 -15420
5 2016-06-16 -15080
6 2016-07-16 -15080
7 2016-08-16 -15400
8 2016-09-16 -15800
9 2016-10-16 -16000
10 2016-11-16 381200
11 2016-12-16 -16300
12 2017-01-16 -17050
13 2017-02-16 -17200
14 2017-03-16 -17480
15 2017-04-16 -17730
16 2017-05-16 -17730
17 2017-06-16 -18450
18 2017-07-16 -18630
19 2017-08-16 -19100
20 2017-09-16 -19200
21 2017-10-16 -19200
22 2017-11-16 -19300
23 2017-12-16 -19880
24 2018-01-16 -19900
25 2018-02-16 -20000
• The calculation method for XIRR is demonstrated in this answer. I might get round to applying it to your problem, but in the meantime you could try it. Link: money.stackexchange.com/a/58278/11768 Commented May 26, 2016 at 19:56
• Thanks Chris Degnen! That was helpful. I tried that method and finding r to be between 2.75 and 2,76, which gives an annualised value of around 0.94. As I have mentioned in the question the second set of data points gives me an annualised return of .34, so I am sort of not able to understand how change in value of just 2 time periods is leading such a huge variation in returns. Any idea?? Commented May 27, 2016 at 4:58

This is the internal rate of return (IRR) calculation for the second case.

There are 731 days from 2016-02-16 to 2018-02-16.

f2 = -13660 - 20000/(1 + r) - 19900/(1 + r)^(700/731) -
19880/(1 + r)^(669/731) - 19300/(1 + r)^(639/731) -
19200/(1 + r)^(608/731) - 19200/(1 + r)^(34/43) -
19100/(1 + r)^(547/731) - 18630/(1 + r)^(12/17) -
18450/(1 + r)^(486/731) - 17730/(1 + r)^(455/731) -
17730/(1 + r)^(25/43) - 17480/(1 + r)^(394/731) -
17200/(1 + r)^(366/731) - 17050/(1 + r)^(335/731) -
16300/(1 + r)^(304/731) + 381200/(1 + r)^(274/731) -
16000/(1 + r)^(243/731) - 15800/(1 + r)^(213/731) -
15400/(1 + r)^(182/731) - 15080/(1 + r)^(151/731) -
15080/(1 + r)^(121/731) - 15420/(1 + r)^(97/731) -
14960/(1 + r)^(64/731) - 14540/(1 + r)^(24/731)

Solving f2 = 0 finds

r = 0.8079781338113063

Annualising

(r + 1)^(365/731) - 1 = 0.34406622582279 = 34.4 %

The first case does not have a perfect IRR solution.

f1 = -13660 - 20000/(1 + r) - 19900/(1 + r)^(700/731) -
19880/(1 + r)^(669/731) - 19300/(1 + r)^(639/731) -
19200/(1 + r)^(608/731) - 19200/(1 + r)^(34/43) -
19100/(1 + r)^(547/731) - 18630/(1 + r)^(12/17) -
18450/(1 + r)^(486/731) - 17730/(1 + r)^(455/731) -
17730/(1 + r)^(25/43) - 17480/(1 + r)^(394/731) -
17200/(1 + r)^(366/731) - 17050/(1 + r)^(335/731) +
381200/(1 + r)^(304/731) - 16300/(1 + r)^(274/731) -
16000/(1 + r)^(243/731) - 15800/(1 + r)^(213/731) -
15400/(1 + r)^(182/731) - 15080/(1 + r)^(151/731) -
15080/(1 + r)^(121/731) - 15420/(1 + r)^(97/731) -
14960/(1 + r)^(64/731) - 14540/(1 + r)^(24/731)

No solution for f1 = 0

The reason for such a big change when only two cashflow are swapped around is that IRR equations are sensitive to the timing of large cashflows mid-period.

• Thanks for the adding further details. So, in cases like these what else is a good method/measure to that will help me choose between two different investments. Since IRR is not possible here is there anything else that I can use?? Commented Jun 3, 2016 at 8:15
• @mankuTimma If you have any valuations at any points between the start and end you could try the linked-returns method. Commented Jun 3, 2016 at 10:49
• For an IRR (Excel XIRR) it's better to have the large cash flows (principal & balance) at the beginning and end of the equation, with the smaller transaction in the intermediate periods. Commented Jul 1, 2016 at 8:13