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
Quote:
|
Originally Posted by Myrna Larson
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
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. |