PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 1.00 average.

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

 
 
=?Utf-8?B?cmJtNDE4?=
Guest
Posts: n/a
 
      15th Sep 2004
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 ??
 
Reply With Quote
 
 
 
 
Alan
Guest
Posts: n/a
 
      15th Sep 2004
"rbm418" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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.


 
Reply With Quote
 
 
 
 
Alan
Guest
Posts: n/a
 
      15th Sep 2004
"rbm418" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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.


 
Reply With Quote
 
Don
Guest
Posts: n/a
 
      15th Sep 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 ??



 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      15th Sep 2004
On Tue, 14 Sep 2004 16:27:15 -0700, rbm418 <(E-Mail Removed)>
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
 
Reply With Quote
 
mangesh_yadav
Guest
Posts: n/a
 
      15th Sep 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

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
Don
Guest
Posts: n/a
 
      16th Sep 2004

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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 ??

>>
>>
>>



 
Reply With Quote
 
Myrna Larson
Guest
Posts: n/a
 
      16th Sep 2004
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 <(E-Mail Removed)>
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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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 ??

>>
>>
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
IRR and XIRR, different results... why?!?!?! loremtz General Discussion 0 23rd Aug 2011 06:35 PM
IRR & XIRR - Different Results =?Utf-8?B?QWxleCBNb3JnYW4=?= Microsoft Excel Worksheet Functions 10 14th Feb 2007 11:58 AM
To XIRR or Not To XIRR Jonathan Microsoft Excel Worksheet Functions 3 17th May 2006 03:13 PM
Re: XIRR versus IRR Norman Harker Microsoft Excel Misc 1 13th Apr 2004 02:56 AM
Re: XIRR versus IRR Kevin Stecyk Microsoft Excel Misc 0 12th Apr 2004 10:11 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:45 AM.