XIRR question

[ORLANDO VAZQUEZ]

Hi,

In the following data, the negative values are capital calls (outflows) and
the positive values are inflows (distributions, income, etc.) The final
value of 6/30/09 is the actual market value.

Is it true that the XIRR for this array of data = 12.38 % ??
The manager believes it is 63.6 % but I calculate 12.38%

I used this formula where col. C is values and col B is dates.
=XIRR(C4:C16,B4:B16,0.1)


11/18/2004 -500000 capcall
5/2/2005 136912 dist
9/26/2005 -500000 capcall
3/17/2006 -600000 capcall
1/30/2006 7203 dist
12/6/2006 349583 dist
5/30/2007 145488 dist
7/10/2007 -200000 capcall
7/25/2007 157844.5 dist
12/10/2007 45088 dist
9/15/2008 137013 dist
5/18/2009 -4129 mgt fee
6/30/2009 1476813 Mkt Value

Should I be reversing some signs here? As far as I can tell, capital calls
(investments) should be negative and income, etc. should be positive with the
final value positive.

Help! Thanks.

 

[Ron Rosenfeld]

Hi,

In the following data, the negative values are capital calls (outflows) and
the positive values are inflows (distributions, income, etc.) The final
value of 6/30/09 is the actual market value.

Is it true that the XIRR for this array of data = 12.38 % ??
The manager believes it is 63.6 % but I calculate 12.38%

I used this formula where col. C is values and col B is dates.
=XIRR(C4:C16,B4:B16,0.1)


11/18/2004 -500000 capcall
5/2/2005 136912 dist
9/26/2005 -500000 capcall
3/17/2006 -600000 capcall
1/30/2006 7203 dist
12/6/2006 349583 dist
5/30/2007 145488 dist
7/10/2007 -200000 capcall
7/25/2007 157844.5 dist
12/10/2007 45088 dist
9/15/2008 137013 dist
5/18/2009 -4129 mgt fee
6/30/2009 1476813 Mkt Value

Should I be reversing some signs here? As far as I can tell, capital calls
(investments) should be negative and income, etc. should be positive with the
final value positive.

Help! Thanks.

You'd best ask your manager for the math underlying his conclusion.

To me 63.6% doesn't make sense.
--ron

 

[JoeU2004]

You'd best ask your manager for the math underlying
his conclusion. To me 63.6% doesn't make sense.
Ditto.


I used this formula where col. C is values and col B
is dates. =XIRR(C4:C16,B4:B16,0.1)

XIRR does sometimes give misleading results. But not in this case.

You can confirm the result that XIRR returns by putting it into XNPV. If
your XIRR formula is in A1, then:

=XNPV(A1,C4:C16,B4:B16)

should be close to zero. I get about -0.01644. Not as close to zero as I
would like; but "close enough".

XIRR actually returns about 12.3806756734848%. When I use Solver, its best
result is about 12.3806752289008%, which causes XNPV to return
about -0.0000001275.

Much closer to zero. But note that the XIRR and Solver results are about
the same to 5 decimal places, namely 12.380675%. Again, close enough!


----- original message -----

Should I be reversing some signs here? As far as
I can tell, capital calls (investments) should be
negative and income, etc. should be positive with
the final value positive.

It has no impact on the XIRR result, as long as you reverse __all__ signs
consistently.

You can verify this quickly by putting -1 into an unused cell and copy it.
Then select column of numbers, C4:C16, right-click and select Paste Special

Multiple, and press Enter.

All the signs should reverse, but you should see no change in the XIRR
result.


----- original message -----

 

[ORLANDO VAZQUEZ]

Hi Ron,

Does the 12.4% make sense ?

You'd best ask your manager for the math underlying his conclusion.

To me 63.6% doesn't make sense.
--ron

 

[Ron Rosenfeld]

Hi Ron,

Does the 12.4% make sense ?

Yes.

See JoeU2004's discussion.
--ron

 

[ORLANDO VAZQUEZ]

Thanks Joe !

XIRR does sometimes give misleading results. But not in this case.

You can confirm the result that XIRR returns by putting it into XNPV. If
your XIRR formula is in A1, then:

=XNPV(A1,C4:C16,B4:B16)

should be close to zero. I get about -0.01644. Not as close to zero as I
would like; but "close enough".

XIRR actually returns about 12.3806756734848%. When I use Solver, its best
result is about 12.3806752289008%, which causes XNPV to return
about -0.0000001275.

Much closer to zero. But note that the XIRR and Solver results are about
the same to 5 decimal places, namely 12.380675%. Again, close enough!


----- original message -----



It has no impact on the XIRR result, as long as you reverse __all__ signs
consistently.

You can verify this quickly by putting -1 into an unused cell and copy it.
Then select column of numbers, C4:C16, right-click and select Paste Special

All the signs should reverse, but you should see no change in the XIRR
result.


----- original message -----