Please review https://docs.google.com/spreadsheets/d/1cWIAdWylD5Mx8zP5gFwkSVkAYLwXuFopoH_9mi2tGWg/edit#gid=0. It contains some dates and corresponding investment amounts. My goal is to have XIRR() compute the rate of return. While it is obvious that the result must be negative, the cell C1, with the formula XIRR(B3:B16,A3:A16), produces a huge positive number.
Thanks in advance.
Asked
Active
Viewed 539 times
0
krbabu
- 51
- 1
- 5
-
whats the expected value? – player0 Jul 08 '20 at 20:24
-
2I understand that you want to use XIRR to analyze cash flows, but I strongly advise against it because singular large cash lumps intraperiod increase volatility. This behaviour comes from the exposure of XIRR to considerable unusual movements, making it unsuitable for portfolios cash flows. In this case, the repeated big negative benefits increased the value to unusual highs. This is a [known feature](https://www.financialwisdomforum.org/gummy-stuff/XIRR-bug.htm) of XIRR. – Jacques-Guzel Heron Jul 09 '20 at 10:43
-
1This is a reply to @player0: A straightforward approximation would regard this as a loss of $25k on a principal of about $55k, in about a year, or -45%. – krbabu Jul 09 '20 at 23:26
1 Answers
0
I just realized that I didn't provide a 3rd argument, rate_guess, in the original invocation of XIRR(). (Thanks to @player0 for prompting me). With rate_guess provided, the result is believable.
krbabu
- 51
- 1
- 5