# 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. ### GuestGuest

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

2. ### AlanGuest

"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

3. ### AlanGuest

"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.

HTH,

Alan.

Alan, Sep 15, 2004
4. ### DonGuest

Dates are not entered for IRR, so how can you be using the same data for
both?

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
5. ### Ron RosenfeldGuest

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

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

--

7. ### DonGuest

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?
>>
>> 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
8. ### Myrna LarsonGuest

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?
>>
>> 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