XIRR Calculating Annual Return Problem

[mlarson]

Hello, I have the data below and can't figure out how to get an accurate annualized rate of return. 6.3% is too low. There wasn't a large initial investment but rather some smaller ones... $6,000, then $1,300, then $700, etc. as you can see from the first positive inflows. Any suggestions? Thanks in advance for your help!

Negative amounts are outflows (withdrawals) from the account
Positive amounts are inflows (deposits) to the account
790,000 is the ending/current value (it's on the last line)

Amount Date XIRR
$6,000 03/16/09 6.300813854%
$1,300 03/20/09
$700 03/20/09
$350 03/25/09
$2,200 03/27/09
$5,000 03/27/09
$3,500 04/07/09
$3,500 04/09/09
$1,500 04/17/09
$2,000 04/21/09
$8,000 05/07/09
$9,000 06/03/09
$3,500 06/15/09
$3,000 06/25/09
$4,000 11/24/09
($1,500) 02/08/10
$2,900 04/09/10
$6,000 04/19/10
$2,000 04/21/10
$2,000 05/10/10
($3,000) 06/01/10
($3,000) 06/18/10
($1,500) 07/06/10
($1,400) 07/23/10
($3,400) 07/28/10
($535,727) 09/14/10
$604,750 09/14/10
($8,035) 09/14/10
$517 09/15/10
($2,100) 10/01/10
($1,600) 10/08/10
($1,700) 10/26/10
($1,000) 11/04/10
($1,100) 11/12/10
($3,000) 11/24/10
($1,500) 12/02/10
($1,200) 12/03/10
($3,000) 12/29/10
($6,000) 01/07/11
($5,000) 01/31/11
($31,000) 02/07/11
($2,000) 02/07/11
($4,000) 03/03/11
$10,000 03/18/11
($7,000) 03/28/11
($4,000) 04/07/11
($3,000) 04/18/11
($5,000) 05/09/11
($7,900) 05/17/11
($4,900) 06/07/11
($5,000) 06/22/11
($6,000) 07/06/11
($3,000) 07/15/11
($5,000) 07/26/11
($5,000) 08/03/11
($3,000) 08/15/11
($6,000) 08/29/11
($1,700) 09/19/11
($8,100) 09/29/11
($3,000) 10/07/11
($2,500) 10/24/11
($4,000) 11/02/11
($2,000) 11/14/11
($5,000) 11/21/11
($1,000) 12/06/11
($1,400) 12/12/11
($10,000) 12/19/11
($4,000) 12/30/11
($3,000) 01/05/12
($7,000) 01/30/12
($2,000) 02/03/12
($2,000) 02/07/12
($2,000) 02/17/12
($3,000) 02/27/12
($3,000) 03/06/12
($3,000) 03/19/12
($5,000) 04/24/12
($1,200) 05/02/12
($1,800) 05/07/12
($1,500) 05/14/12
($6,000) 05/30/12
($2,000) 06/05/12
($6,000) 06/20/12
($7,000) 07/03/12
($8,000) 07/30/12
($2,000) 08/08/12
($8,000) 08/14/12
($4,000) 08/29/12
($2,000) 09/05/12
($18,000) 09/27/12
($4,000) 10/10/12
($5,000) 10/18/12
($7,000) 11/21/12
($5,000) 12/10/12
($7,000) 12/27/12
($5,000) 01/16/13
($4,000) 01/29/13
($3,000) 02/05/13
($2,000) 02/19/13
($6,000) 02/28/13
($3,000) 03/07/13
($8,000) 03/22/13
($3,000) 04/24/13
($34,000) 05/14/13
($17,000) 05/14/13
($12,000) 05/29/13
($4,000) 06/11/13
($12,000) 06/20/13
($5,000) 07/16/13
($5,000) 07/29/13
($5,000) 08/06/13
($7,000) 08/29/13
($3,000) 01/23/14
($9,000) 01/28/14
$50,000 01/31/14
($3,000) 02/10/14
($10,000) 02/25/14
($10,000) 03/31/14
($6,000) 04/21/14
($4,000) 05/01/14
($10,000) 05/30/14
($4,000) 06/09/14
($6,000) 06/24/14
($6,000) 07/08/14
($7,000) 07/29/14
($3,000) 08/12/14
($15,000) 08/29/14
($3,000) 09/23/14
($7,000) 09/30/14
($10,000) 10/28/14
($3,000) 11/03/14
$28,000 11/17/14
($8,000) 11/28/14
($12,000) 12/12/14
($100,000) 01/20/15
$100,000 01/20/15
($10,000) 01/21/15
($10,000) 02/06/15
($9,500) 02/20/15
($25,845) 02/24/15
$25,000 02/25/15
($6,000) 03/04/15
($6,000) 04/02/15
($50,000) 04/21/15
$50,000 04/21/15
($10,000) 04/28/15
$432,000 04/30/15
($432,000) 04/30/15
$12,000 05/04/15
($8,000) 05/04/15
($12,000) 05/04/15
($447,700) 05/12/15
($10,000) 05/12/15
$447,700 05/12/15
($200,000) 05/26/15
($55,000) 05/27/15
($200,000) 05/27/15
($3,000) 06/29/15
($1,500) 06/30/15
$57,000 07/03/15
$14,000 07/09/15
($5,000) 07/22/15
($6,000) 07/28/15
($2,000) 08/10/15
($5,000) 08/25/15
($4,000) 09/09/15
$4,800 09/14/15
($4,800) 09/14/15
($3,000) 09/23/15
($4,800) 09/29/15
$15,000 10/29/15
$5,000 11/09/15
$237,905 11/16/15
($3,000) 12/28/15
$80,000 01/04/16
($5,000) 01/27/16
($15,000) 02/03/16
$5,000 02/04/16
$9,900 02/05/16
($3,620) 02/12/16
($3,500) 02/19/16
($10,000) 03/22/16
($5,000) 04/27/16
($5,000) 05/03/16
($4,000) 05/25/16
($3,000) 06/06/16
($3,000) 06/16/16
($3,000) 06/23/16
($2,000) 07/01/16
($4,000) 07/20/16
($7,000) 07/29/16
($2,000) 08/04/16
($3,000) 08/17/16
($8,000) 08/30/16
($2,500) 09/23/16
($3,000) 09/29/16
($2,000) 10/03/16
($2,000) 10/17/16
($3,000) 10/31/16
($1,700) 11/01/16
($3,000) 11/21/16
($2,500) 12/02/16
($3,500) 12/06/16
($1,000) 12/15/16
($2,500) 12/19/16
($5,000) 12/27/16
($1,000) 01/03/17
($3,000) 01/06/17
($2,400) 01/12/17
($2,500) 01/19/17
($6,000) 01/31/17
($1,000) 02/10/17
($1,000) 02/16/17
($1,000) 02/17/17
($7,000) 02/23/17
($2,000) 03/08/17
($3,000) 03/17/17
($7,000) 03/29/17
($2,700) 04/11/17
($2,000) 04/25/17
($4,000) 04/27/17
($2,000) 05/09/17
($1,300) 05/12/17
($1,850) 05/17/17
($2,000) 05/24/17
($6,000) 06/01/17
($1,100) 06/07/17
($2,000) 06/14/17
($3,000) 06/20/17
($6,000) 06/27/17
($1,000) 07/06/17
($2,000) 07/10/17
($3,000) 07/19/17
($7,000) 07/28/17
($1,600) 08/10/17
($2,990) 08/17/17
($5,900) 08/25/17
($500) 09/05/17
($1,000) 09/11/17
($1,500) 09/15/17
($1,700) 09/21/17
($6,000) 09/27/17
($1,000) 10/05/17
($1,100) 10/10/17
($1,500) 10/13/17
($1,700) 10/23/17
($6,000) 10/27/17
($2,000) 11/08/17
($1,985) 11/20/17
($5,700) 11/29/17
($3,000) 12/05/17
($3,000) 12/14/17
($790,000) 06/04/18

 

[shg]

Seems like it's in the ballpark. A first-order approximation =(3802752/2255022)^(1/9) - 1 returns 6%.

 

[mlarson]

OK, thanks!

 

[joeu2004]

Hello, I have the data below and can't figure out how to get an accurate annualized rate of return. 6.3% is too low.

I'm not convinced that 6.3% is the correct answer. How did you calculate that? Provide the formula.

When I enter the data into A1:A253 and B1:B253, =XIRR(A1:A253,B1:B253) returns 92.3802435398101%. And using XNPV with those values returns about -1.51E-04, which is relatively close to zero.

Moreover, XNPV with those values, but an IRR of 6.300813854%, returns about -991147.43, which is not relatively close to zero. So 6.3% does not seem to be another valid IRR. (Sometimes there is more than one.)

Finally, as a "second-order" approximation, when I group the data into monthly cash flows (112), Excel IRR returns a monthly IRR of 5.62442290209009%. That is an annualized IRR of 92.8289291335648%, which is close to the result from Excel XIRR.

 

[mlarson]

Hi Joeu! I used the following =XIRR(A20:A272,B20:B272,5%). The amounts start at A20 and the dates start at B20.

 

[mlarson]

Doh! I just got the 92% answer as well. One of my dates was in a =DATE format and that messed it up. Fixed and problem solved. 92% it is! Thanks!

 

[mlarson]

With that said, it is odd that if I change the ending value from 790,000 to 50,000,000 the XIRR only goes from 92% to 129%. Playing with the ending amount doesn't seem to impact the return much at all. Any thoughts on that?

 

[mlarson]

With that said, it is odd that if I change the ending value from 790,000 to 50,000,000 the XIRR only goes from 92% to 129%. Playing with the ending amount doesn't seem to impact the return much at all. Any thoughts on that?

If I change the end date and move it out 30 years (so 6-4-2048 instead of 6-4-2018) the XIRR only drops 2% (from 92% to 90%). Odd.

 

[joeu2004]

Doh! I just got the 92% answer as well. One of my dates was in a =DATE format and that messed it up.

Non sequitur!

First, =DATE is a formula, not a format. Using =DATE(...) to express a date should not make any difference to XIRR. Second, if the cell value is type numeric (date or =DATE result or whatever), the cell format should not make any difference to XIRR. Finally, if any of the "dates" (column B) are type text, XIRR returns a #VALUE error. Caveat: we can only distinguish type numeric and type text data by using ISNUMBER or ISTEXT. It is not determined by the cell format.

I thought you might mean that one date was missing (read: empty cell). But the closest I come to 6.300813854% is 6.26463800668716% by clearing the second date (3/20/2009), but leaving the value ($1300) intact.

If you would like an explanation of the root cause of the 6.30% result, upload a redacted Excel file that demonstrates the 6.30% result to a file-sharing website (e.g. box.net/files), and post the share/public URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview error.)

With that said, it is odd that if I change the ending value from 790,000 to 50,000,000 the XIRR only goes from 92% to 129%. Playing with the ending amount doesn't seem to impact the return much at all. Any thoughts on that?

If I change the end date and move it out 30 years (so 6-4-2048 instead of 6-4-2018) the XIRR only drops 2% (from 92% to 90%). Odd.

First, I would consider the difference between 129% and 92% to be very significant.

Second, remember that the last cash flow is discounted by multiplying by 1/(1+rate)^days. For rate>0, that factor decreases rapidly as days increases, especially by multiples of 365. That can greatly diminish the impact of changing the later cash flows.

Finally, the NPV formula is a multi-term polynomial. Its behavior is difficult to predict as we change parameters. And the more sign changes in the cash flow series, the more difficult it is to understand the behavior. In fact, that can result in several possible IRRs and even no computable IRR.

 

[joeu2004]

Errata....

Second, remember that the last cash flow is discounted by multiplying by 1/(1+rate)^days. For rate>0, that factor decreases rapidly as days increases, especially by multiples of 365. That can greatly diminish the impact of changing the later cash flows.

That should be 1/(1+rate)^(days/365) or 1/(1+rate)^years. I wrote the latter first, then oversimplified when I changed units. The point remains the same: changes of up to 30 years can greatly diminish the impact of later cash flows.