what is difference in IRR vs XIRR ? I'm getting different results.

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Sep 15, 2004.

  1. Guest

    Guest Guest

    Using the same data dates and amounts
    I get one result using the IRR function and
    a different result using the XIRR function

    What would cause the difference ??
     
    Guest, Sep 15, 2004
    #1
    1. Advertisements

  2. Guest

    Alan Guest

    "rbm418" <> wrote in message
    news:...
    >
    > Using the same data dates and amounts
    > I get one result using the IRR function and
    > a different result using the XIRR function
    >
    > What would cause the difference ??
    >


    If the data set is large, try running both functions on smaller and
    smaller subsets (remove one period / time entry at a time, or five at
    a time or whatever) until the error goes away, then you'll know which
    part of the data is actually causing the problem. That might help you
    track down the reason.

    You could also post the data, or a subset that you find still contains
    the difference so that we can see what you mean.

    HTH,

    Alan.
     
    Alan, Sep 15, 2004
    #2
    1. Advertisements

  3. Guest

    Alan Guest

    "rbm418" <> wrote in message
    news:...
    >
    > Using the same data dates and amounts
    > I get one result using the IRR function and
    > a different result using the XIRR function
    >
    > What would cause the difference ??
    >


    Another idea:

    It could be that there are too many arguments for one or other of the
    functions.

    Read the online help for both to see if there is any mention of that.

    HTH,

    Alan.
     
    Alan, Sep 15, 2004
    #3
  4. Guest

    Don Guest

    Dates are not entered for IRR, so how can you be using the same data for
    both?
    Please show your cash flows and results.

    Don


    "rbm418" <> wrote in message
    news:...
    > Using the same data dates and amounts
    > I get one result using the IRR function and
    > a different result using the XIRR function
    >
    > What would cause the difference ??
     
    Don, Sep 15, 2004
    #4
  5. On Tue, 14 Sep 2004 16:27:15 -0700, rbm418 <>
    wrote:

    >Using the same data dates and amounts
    >I get one result using the IRR function and
    >a different result using the XIRR function
    >
    >What would cause the difference ??


    They are measuring two different things.

    I would expect them to be the same if you have only two entries, and the two
    entries span a 365 day year.


    --ron
     
    Ron Rosenfeld, Sep 15, 2004
    #5
  6. IRR does not take dates as input and assumes a 365 day year (does no
    consider leap years). XIRR calculates on actual dates and so the tw
    results will match as long as there is no leap year in your dates.

    - Manges

    --
    Message posted from http://www.ExcelForum.com
     
    mangesh_yadav, Sep 15, 2004
    #6
  7. Guest

    Don Guest

    Re: what is difference in IRR vs XIRR ? I'm getting different resu

    Both rates seem to be correct.

    17.418% is the monthly compound equivalent of 18.878% compounded annually .
    If I calculate IRR as =(1+(IRR(b8:b54),.01)))^12-1 = 18.878%.

    The table below shows the compound equivalents of the same 18.878%
    compounded differently.

    18.878% = Annual Compounding
    18.062% = Semi-Annual Compounding
    17.672% = Quarterly Compounding
    17.418% = Monthly Compounding
    17.355% = Twice-a-Month Compounding
    17.297% = Daily Compounding
    17.293% = Continuous Compounding


    Don Pistulka

    "rbm418" <> wrote in message
    news:...
    > attached is my data thanks for your help
    >
    >
    > Test Data
    >
    > Date of Investment
    > Investment or Return on Investment
    > Return
    >
    > 11/1/2000 (670,000)
    > 12/1/2000 (64,554)
    > 1/1/2001 (76,168)
    > 2/1/2001 (50,737)
    > 3/1/2001 (138,541)
    > 4/1/2001 -
    > 5/1/2001 -
    > 6/1/2001 -
    > 7/1/2001 -
    > 8/1/2001 -
    > 9/1/2001 -
    > 10/1/2001 -
    > 11/1/2001 -
    > 12/1/2001 -
    > 1/1/2002 -
    > 2/1/2002 -
    > 3/1/2002 -
    > 4/1/2002 -
    > 5/1/2002 -
    > 6/1/2002 -
    > 7/1/2002 -
    > 8/1/2002 -
    > 9/1/2002 -
    > 10/1/2002 -
    > 11/1/2002 -
    > 12/1/2002 -
    > 1/1/2003 -
    > 2/1/2003 -
    > 3/1/2003 -
    > 4/1/2003 -
    > 5/1/2003 -
    > 6/1/2003 -
    > 7/1/2003 -
    > 8/1/2003 -
    > 9/1/2003 -
    > 10/1/2003 -
    > 11/1/2003 117,970
    > 12/1/2003 -
    > 1/1/2004 -
    > 2/1/2004 62,895
    > 3/1/2004 26,955
    > 4/1/2004 -
    > 5/1/2004 666,554
    > 6/1/2004 59,305
    > 7/1/2004 -
    > 8/1/2004 12,581
    > 9/1/2004 899,171
    >
    > IRR 17.418% Formula =IRR(b8:b54,.01/12)*12
    >
    > XIRR 18.8713% Formula =XIRR(b8:b54,a8:a54)
    >
    >
    >
    >
    >
    >
    >
    > "Don" wrote:
    >
    >> Dates are not entered for IRR, so how can you be using the same data for
    >> both?
    >> Please show your cash flows and results.
    >>
    >> Don
    >>
    >>
    >> "rbm418" <> wrote in message
    >> news:...
    >> > Using the same data dates and amounts
    >> > I get one result using the IRR function and
    >> > a different result using the XIRR function
    >> >
    >> > What would cause the difference ??

    >>
    >>
    >>
     
    Don, Sep 16, 2004
    #7
  8. Guest

    Myrna Larson Guest

    Re: what is difference in IRR vs XIRR ? I'm getting different resu

    You aren't converting the result of IRR correctly.

    If i is the number returned by IRR, that is a monthly rate. You are
    calculating the annual rate as i*12. That's not correct.

    The correct formula is =(1+i)^12-1

    Combining it all into one formula,

    =(1+IRR(B8:B54,0.1/12))^12-1

    That gives 18.88%

    On Wed, 15 Sep 2004 07:25:06 -0700, rbm418 <>
    wrote:

    >attached is my data thanks for your help
    >
    >
    >Test Data
    >
    >Date of Investment
    >Investment or Return on Investment
    >Return
    >
    >11/1/2000 (670,000)
    >12/1/2000 (64,554)
    >1/1/2001 (76,168)
    >2/1/2001 (50,737)
    >3/1/2001 (138,541)
    >4/1/2001 -
    >5/1/2001 -
    >6/1/2001 -
    >7/1/2001 -
    >8/1/2001 -
    >9/1/2001 -
    >10/1/2001 -
    >11/1/2001 -
    >12/1/2001 -
    >1/1/2002 -
    >2/1/2002 -
    >3/1/2002 -
    >4/1/2002 -
    >5/1/2002 -
    >6/1/2002 -
    >7/1/2002 -
    >8/1/2002 -
    >9/1/2002 -
    >10/1/2002 -
    >11/1/2002 -
    >12/1/2002 -
    >1/1/2003 -
    >2/1/2003 -
    >3/1/2003 -
    >4/1/2003 -
    >5/1/2003 -
    >6/1/2003 -
    >7/1/2003 -
    >8/1/2003 -
    >9/1/2003 -
    >10/1/2003 -
    >11/1/2003 117,970
    >12/1/2003 -
    >1/1/2004 -
    >2/1/2004 62,895
    >3/1/2004 26,955
    >4/1/2004 -
    >5/1/2004 666,554
    >6/1/2004 59,305
    >7/1/2004 -
    >8/1/2004 12,581
    >9/1/2004 899,171
    >
    >IRR 17.418% Formula =IRR(b8:b54,.01/12)*12
    >
    >XIRR 18.8713% Formula =XIRR(b8:b54,a8:a54)
    >
    >
    >
    >
    >
    >
    >
    >"Don" wrote:
    >
    >> Dates are not entered for IRR, so how can you be using the same data for
    >> both?
    >> Please show your cash flows and results.
    >>
    >> Don
    >>
    >>
    >> "rbm418" <> wrote in message
    >> news:...
    >> > Using the same data dates and amounts
    >> > I get one result using the IRR function and
    >> > a different result using the XIRR function
    >> >
    >> > What would cause the difference ??

    >>
    >>
    >>
     
    Myrna Larson, Sep 16, 2004
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Liz

    IRR/XIRR

    Liz, Apr 26, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    284
    Harlan Grove
    Apr 26, 2004
  2. Jen

    XIRR vs. IRR

    Jen, Jun 7, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    836
    Fred Smith
    Jun 9, 2004
  3. Guest

    MORE XIRR vs. IRR help

    Guest, Jun 8, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    11,062
    Guest
    Jun 8, 2004
  4. Jonathan

    To XIRR or Not To XIRR

    Jonathan, May 16, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    421
    Jonathan
    May 17, 2006
  5. Guest

    IRR & XIRR - Different Results

    Guest, Jan 31, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    1,896
    Guest
    Feb 14, 2007
Loading...

Share This Page