XIRR Calculating Annual Return Problem

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
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. 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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Errata....


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.

Great, thank you!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top