I have coded the XIRR algorithm in VB, and in fact it runs a bit faster than
the one in the ATP. If you send me your data, I will see if it can handle this
data set.
myrna larson @ charter . net
without the spaces.
On Mon, 6 Jun 2005 18:41:25 +0100, Francois_Provost
<[email protected]> wrote:
>
>Thanks Myrna.
>
>Unfortunately, I've tried to specify the 3rd parameter (the guess), and
>it doesn't work. However, if I change some of the "early" dates in the
>cash flow to put them later in time, it does give me a result.
>Obviously, this is of no use to me because it does not reflect
>real-life events, but I was just curious to see if eliminating a
>"large" gap in the cash flow (4.5 years) could make XIRR work properly.
>I guess that this is not a bug in Excel XIRR function, but some
>incapacity of the "Newton algorithm" to complete properly in this kind
>of situation. In any case, thanks for your help.
>
>François
>Myrna Larson Wrote:
>> Try supplying a value for the last argument, Guess. Others have
>> mentioned that
>> -0.1 usually works in all situations.
>>
>> On Sun, 5 Jun 2005 16:33:56 +0100, Francois_Provost
>> [email protected] wrote:
>> -
>>
>> I am using XIRR to calculate the IRR on stock market investments.
>> Outflows are the result of 1) buying shares and 2) paying broker fees.
>> Inflows are the result of 1) Dividends and 2) selling shares. It
>> works
>> most of the times, but in a particular case, it fails and returns
>> #NUM!
>> (if called from VBA using application.run method, the function returns
>> "Error 2036").
>>
>> Cash flow data fed to XIRR is as follows :
>>
>> Flows : ($6,138.49), $40.00, $4,817.98, $40.00, ($1,741.00), $1,712.00
>> Dates : 24-Aug-2000, 2-Oct-2000, 22-Dec-2000, 3-Jan-2001,4-Jun-2005,
>> 5-Jun-2005
>>
>> However, if I change the last date from 5-jun-2005 to 6-jun-2005, XIRR
>> seems to work properly. Any clue anybody ?
>>
>> Thanks in advance for support.-
Bookmarks