|
XIRR question
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. |
XIRR question
On Wed, 7 Oct 2009 13:27:46 -0700, ORLANDO VAZQUEZ
wrote: 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 |
XIRR question
"Ron Rosenfeld" wrote:
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 ----- ORLANDO VAZQUEZ wrote: 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 ----- "Ron Rosenfeld" wrote in message ... On Wed, 7 Oct 2009 13:27:46 -0700, ORLANDO VAZQUEZ wrote: 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 |
XIRR question
Hi Ron,
Does the 12.4% make sense ? "Ron Rosenfeld" wrote: On Wed, 7 Oct 2009 13:27:46 -0700, ORLANDO VAZQUEZ wrote: 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 |
XIRR question
On Wed, 7 Oct 2009 21:26:01 -0700, ORLANDO VAZQUEZ
wrote: Hi Ron, Does the 12.4% make sense ? Yes. See JoeU2004's discussion. --ron |
XIRR question
Thanks Joe !
"JoeU2004" wrote: "Ron Rosenfeld" wrote: 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 ----- ORLANDO VAZQUEZ wrote: 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 ----- "Ron Rosenfeld" wrote in message ... On Wed, 7 Oct 2009 13:27:46 -0700, ORLANDO VAZQUEZ wrote: 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 |
| All times are GMT +1. The time now is 08:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com