XIRR versus IRR

[Cheryl]

In a period of cash flows that occur at each month end
over a short time frame, what would cause the results of
these two formulas to differ dramatically? Why does the
magnitude of the dollar values impact the result of XIRR?

For example, if you take a stream of cash flows starting
with an initial outflow on 6/1/04 of -200,000 followed by
24 inflows calculated at a 5% interest rate (8,774.28) the
IRR is 5% and the XIRR is 5.11%. But when I multiply the
entire stream of cash flows by 10 (-2 million followed by
24@ 87,742.78) the IRR remains 5% but the XIRR becomes
356.16%. The IRRs used in the example are annualized.

 

[Norman Harker]

Hi Cheryl!

Your trouble is with your annualizing.

A1: 6-Jun-2004
Edit Fill Series to 6-Jun-2006
B1: -200000
B2: =PMT(5%/12,24,$B$1,0,0) [returns 8774.27794681365]
Copied down to B25

IRR
=(1+IRR(B1:B25))^12-1
Returns: 5.11618978817308%

XIRR
=XIRR(B1:B25,A1:A25)
Returns: 5.11207848787308%

XIRR returns the annual effective rate. IRR returns the effective rate
per period of the cash flow. Multiply the IRR by the frequency of cash
flow produces the APR12 or Nominal compounded Monthly.

The difference between results is caused by XIRR using exact day
differences between your monthly cash flows.

Your 8774.28 was calculated using 5%/12 per month which is

=(1+5%/12)^12-1
Returns: 5.11618978817334%