XIRR Calculating Annual Return Problem

mlarson

Active Member
Joined
Aug 25, 2011
Messages
439
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Seems like it's in the ballpark. A first-order approximation =(3802752/2255022)^(1/9) - 1 returns 6%.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 25, 2011
Messages
439

ADVERTISEMENT

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

mlarson

Active Member
Joined
Aug 25, 2011
Messages
439
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

Active Member
Joined
Aug 25, 2011
Messages
439

ADVERTISEMENT

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

Active Member
Joined
Aug 25, 2011
Messages
439
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,567
Messages
5,637,091
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top