XIRR

G

Guest

I am getting a really weird XIRR result for the following series of cash flows:

4/27/07 $43,750.00
9/11/07 $(437,500.00)
10/11/07 $(433,008.33)
11/11/07 $(428,217.22)
12/11/07 $(424,025.00)
1/11/08 $(418,934.44)
2/11/08 $(414,293.06)
3/11/08 $(411,448.33)
4/11/08 $(405,010.28)
5/11/08 $(401,566.67)
6/11/08 $(395,727.50)
7/11/08 $(392,583.33)
8/11/08 $(386,444.72)
9/11/08 $(381,803.33)
10/11/08 $(379,108.33)
11/11/08 $(372,520.56)
12/11/08 $(370,125.00)
1/11/09 $74,262.22
2/11/09 $74,262.22
3/11/09 $67,075.56
4/11/09 $74,262.22
5/11/09 $71,866.67
6/11/09 $74,262.22
7/11/09 $71,866.67
8/11/09 $74,262.22
9/11/09 $74,262.22
10/11/09 $71,866.67
11/11/09 $74,262.22
12/11/09 $7,071,866.67

TOTAL CF $1,465,811.67
XIRR = 305,076.9%

This can't be right, can it? It's only net ~$1.5mm...that doesn't strike me
as a > 300,000% return. Help?
 
J

Jim Cone

Maybe...
http://support.microsoft.com/kb/925797/en-us
"The Yield function results in an unexpectedly large value..."
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jami"
wrote in message
I am getting a really weird XIRR result for the following series of cash flows:

4/27/07 $43,750.00
9/11/07 $(437,500.00)
10/11/07 $(433,008.33)
11/11/07 $(428,217.22)
12/11/07 $(424,025.00)
1/11/08 $(418,934.44)
2/11/08 $(414,293.06)
3/11/08 $(411,448.33)
4/11/08 $(405,010.28)
5/11/08 $(401,566.67)
6/11/08 $(395,727.50)
7/11/08 $(392,583.33)
8/11/08 $(386,444.72)
9/11/08 $(381,803.33)
10/11/08 $(379,108.33)
11/11/08 $(372,520.56)
12/11/08 $(370,125.00)
1/11/09 $74,262.22
2/11/09 $74,262.22
3/11/09 $67,075.56
4/11/09 $74,262.22
5/11/09 $71,866.67
6/11/09 $74,262.22
7/11/09 $71,866.67
8/11/09 $74,262.22
9/11/09 $74,262.22
10/11/09 $71,866.67
11/11/09 $74,262.22
12/11/09 $7,071,866.67

TOTAL CF $1,465,811.67
XIRR = 305,076.9%

This can't be right, can it? It's only net ~$1.5mm...that doesn't strike me
as a > 300,000% return. Help?
 
J

joeu2004

I am getting a really weird XIRR result for the following series of cash flows:
[....]
XIRR = 305,076.9%
This can't be right, can it?
Click to expand...

Nope! The correct answer is approximately 13.7753885454%.

My first thought was: this is why XIRR() has a "guess" parameter.
But XIRR() still returns the wrong value even when I set "guess" to
the right answer.

This is clearly a defect in XIRR(). When the first cash flow is
negative, XIRR() returns a reasonable result.

FYI, when I use XIRR() with your cash flows, I get 0.0000002980%, not
the result you get. I am using Excel 2003 (11.5612.5606). No
matter: it is just as wrong.

PS: Excel Solver had no trouble determining the correct answer. That
might be a work-around for you.
 
J

joeu2004

This is clearly a defect in XIRR(). When the first cash flow is
negative, XIRR() returns a reasonable result.
[....]
PS: Excel Solver had no trouble determining the correct answer.
That might be a work-around for you.
Click to expand...

Arguably a better work-around: add a cash flow of -1 one day before
the first cash flow (4/26/07). XIRR() returns 13.7753754854% -- the
same as my result to 4 decimal places (i.e. 13.7754%).

BTW, the difference might not be entirely due to the extra cash flow.
I notice a small difference between XIRR() and manual results with
your original data when I change your first cash flow to negative
(just to isolate the XIRR defect).



I am getting a really weird XIRR result for the following series of cash flows:
[....]
XIRR = 305,076.9%
This can't be right, can it?
Click to expand...

Nope! The correct answer is approximately 13.7753885454%.

My first thought was: this is why XIRR() has a "guess" parameter.
But XIRR() still returns the wrong value even when I set "guess" to
the right answer.

This is clearly a defect in XIRR(). When the first cash flow is
negative, XIRR() returns a reasonable result.

FYI, when I use XIRR() with your cash flows, I get 0.0000002980%, not
the result you get. I am using Excel 2003 (11.5612.5606). No
matter: it is just as wrong.

PS: Excel Solver had no trouble determining the correct answer. That
might be a work-around for you.


I am getting a really weird XIRR result for the following series of cash flows:
Click to expand...
4/27/07 $43,750.00
9/11/07 $(437,500.00)
10/11/07 $(433,008.33)
11/11/07 $(428,217.22)
12/11/07 $(424,025.00)
1/11/08 $(418,934.44)
2/11/08 $(414,293.06)
3/11/08 $(411,448.33)
4/11/08 $(405,010.28)
5/11/08 $(401,566.67)
6/11/08 $(395,727.50)
7/11/08 $(392,583.33)
8/11/08 $(386,444.72)
9/11/08 $(381,803.33)
10/11/08 $(379,108.33)
11/11/08 $(372,520.56)
12/11/08 $(370,125.00)
1/11/09 $74,262.22
2/11/09 $74,262.22
3/11/09 $67,075.56
4/11/09 $74,262.22
5/11/09 $71,866.67
6/11/09 $74,262.22
7/11/09 $71,866.67
8/11/09 $74,262.22
9/11/09 $74,262.22
10/11/09 $71,866.67
11/11/09 $74,262.22
12/11/09 $7,071,866.67
Click to expand...
TOTAL CF $1,465,811.67
XIRR = 305,076.9%
Click to expand...
This can't be right, can it? It's only net ~$1.5mm...that doesn't strike me
as a > 300,000% return. Help?
Click to expand...
Click to expand...
 
Top