Home |
Search |
Today's Posts |
#1
|
|||
|
|||
XIRR and IRR
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 "Dan" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|