XIRR function breaking down (#NUM)

[Wesslan]

Hi guys,

Know this is a forum for VBA questions and I have done quite a bit of
programming so that I will shoot this by the pros here

I have the following data:

Date* Value
31-Dec-06 -353,727,346
30-Nov-07 -70,866,490
31-Dec-07 333,502,702
31-Jan-08 507,604
29-Feb-08 -1,841,266
31-Mar-08 -1,125,842
30-Apr-08 -950,991
31-May-08 -193,437
30-Jun-08 -444,026
31-Jul-08 -962,122
31-Aug-08 -2,059,591
30-Sep-08 58,164
31-Oct-08 -1,040,522
30-Nov-08 -2,766,378
31-Dec-08 -46,006,913


For some reason my XIRR function will not work (even if I provide a
negative guess). I have calculated the TSR on a yearly basis which is
approx -24% and the total drop over the two years is approx -42%.

Anyone have a clue why my Excel only gives me #Num. Using VBA I have
sorted the data in terms of first date and then amount. Still have the
error...

Any help would be truly appreciated!

 

[Eric G]

For some reason, the last value is causing the #NUM error. I found that
changing the last value to -42,768,026 (which leads to XIRR = -0.59994)
works, but anything larger than that doesn't. It could be that the model is
unable to converge in 100 iterations for larger values. Seems strange that
the model breaks when you get to XIRR = -0.6.

HTH,

Eric

 

[Nigel]

Perhaps factor the data down by 3 orders of magnitude will get it to work?

--

Regards,
Nigel
(e-mail address removed)