XIRR and IRR

[Dan]

I am trying to understand the differences between IRR and XIRR in Excel.

If my data is as follows:

1/1/1998 -10,000
1/2/1998 10,005

The IRR for that data is .05% for the 1 period return - which I assume means
that the annual return is 365 times the 1 period return or 18.25%

The XIRR for that data is 20.01594%.

Shouldn't the IRR percentage and the XIRR percentage be the same for a
calculation?

If I put in a series of payments that exactly match a 365 day period between
payments, the IRR and XIRR are the same. For instance:

12/31/2000 -70,000
12/31/2001 12,000
12/31/2002 15,000
12/31/2003 18,000
12/31/2004 21,000
12/31/2005 26,000

The IRR for that series is 8.663% and the XIRR is 8.660%. I guess since the
IRR assumes each period to be a year and since XIRR calculates each
difference in date to be a year, I understand why it works - but I don't
understand why it does not work if the period is something different than an
exact year.

Any insights?

 

[Jay Petrulis]

Hi Dan,

To annualize the 1 period return, you would use geometric linking
rather than arithmetic. Your one period IRR annualized becomes...

(1+IRR)^(periods/yr) - 1

In your case...

(10005/10000)^365 - 1 = 20.01594%

Regards,

Jay Petrulis

 

[Alok]

Dan,
You have already figured out that IRR and XIRR produce the same result. The
only shortcoming of the IRR is that it expects the negative cash flows and
positive cash flows are occuring at a gap of 0ne year. You will notice that
the IRR function has no input for the dates and only for values, very unlike
the XIRR function. This is also clear from the Help on IRR and XIRR in Excel.
Alok Joshi