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

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

 

[Alan]

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]

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.

 

[Don]

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

 

[Ron Rosenfeld]

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

 

[mangesh_yadav]

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

 

[Don]

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

 

[Myrna Larson]

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%