+ Reply to Thread
Results 1 to 5 of 5

Problem with XIRR function returning #NUM!

  1. #1
    Myrna Larson
    Guest

    Re: Problem with XIRR function returning #NUM!

    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
    <Francois_Provost.1q7wla@news.excelbanter.com> 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
    >> Francois_Provost.1q61x9@news.excelbanter.com 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.-



  2. #2
    Myrna Larson
    Guest

    Re: Problem with XIRR function returning #NUM!

    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
    <Francois_Provost.1q7wla@news.excelbanter.com> 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
    >> Francois_Provost.1q61x9@news.excelbanter.com 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.-



  3. #3
    Myrna Larson
    Guest

    Re: Problem with XIRR function returning #NUM!

    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
    <Francois_Provost.1q7wla@news.excelbanter.com> 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
    >> Francois_Provost.1q61x9@news.excelbanter.com 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.-



  4. #4
    Francois_Provost
    Guest

    Problem with XIRR function returning #NUM!


    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
    > Francois_Provost.1q61x9@news.excelbanter.com 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.-



    --
    Francois_Provost

  5. #5
    Myrna Larson
    Guest

    Re: Problem with XIRR function returning #NUM!

    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
    <Francois_Provost.1q7wla@news.excelbanter.com> 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
    >> Francois_Provost.1q61x9@news.excelbanter.com 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.-



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1