On May 22, 6:06*pm, Csmithers
wrote:
'joeu2004[_2_ Wrote:









;1601997']"Csmithers" wrote:-
I have an out flow and then monthly inflows for a month.
They are the same time every month the first of the month.
When I use IRR I get 18%, when I use XIRR I get 640%.
If i multiply 18% by 12 I get 217% which seems right ,
but doesn;t take into account compounding. *If use this
=(iRR(C4:C16)+1)^12-1) I get 633% which is very close to
640%-

All are right and wrong!

First, IRR returns the periodic rate. *If you have monthly cash flows,
IRR
returns a monthly rate.

In contrast, XIRR always returns an annual rate.

That explains why XIRR is so much larger than the (monthly) IRR.

Second, there is no general agreement on how to annualize a periodic
rate.
To some degree, it depends on what financial securities you are
modelling,
the industry conventions applied to those securities, and even
applicable
regional laws. *Sometimes it is correct to multiply a monthly IRR by 12;

sometimes it is correct to compound it over 12 periods; sometimes we
multiply by other factors.

In contrast, XIRR always compounds daily.

That partly explains why even when you compound the monthly IRR over 12

periods, it is not exactly the same as daily-compounded XIRR.

Third, by definition, IRR treats each month as the same length when
using
monthly cash flows. *But XIRR uses the actual number of days.

That further explains why the monthly compounded IRR over 12 periods
does
not agree exactly with the XIRR.

Finally, note that neither the IRR nor the XIRR can be computed
algebraically (unless all cash flows are the same, and they occur
regularly). *Instead, each function uses some algorithm to "home in on"
the
rate that causes the (X)NPV to be close to zero. *Microsoft
documentation
indicates that starting with Excel 2003, IRR and XIRR use the
Newton-Raphson
method.

But each algorithm is implemented differently, which gives rise to
differences in the results due to arithmetic anomalies (due to binary
floating-point) as well as diffences in the tolerances and the
underlying
differential formulas.

That is another reason why the monthly compounded IRR over 12 period
does
not agree with the XIRR.

In actual practice, it is best not to read too much into the detailed
numerical results. *They are all only estimates anyway. *Choose
whichever
function is more appropriate for the data that you have.

PS: *I might note that the Excel IRR and XIRR functions are simply two
ways
to calculate the "internal rate of return" (IRR). *In other words, when
I
speak of "IRR", you need to decide by context whether I am speaking of
the
Excel function or the financial concept.

"Csmithers" wrote:-
I get 633% which is very close to 640% but seems way to
high. *I haven't even tripled my investment, which was
-130,000 while making 440,00 total over the year. *Can
someone tell me which number is correct and why?-

Yes, that is the fallacy of annualizing periodic rates, IMHO.

For example, if the value of a security changes 1% in a day, surely you
do
not believe we can expect its value has grown (will grow) at a annual
rate
of 3678%. *That is indeed what (1+1%)^365-1 is. *But that is not a
realistic
assessment of the change in value.

So even though it is common practice to annualize periodic rates, I try
to
avoid it -- although there are applications of the rate of return where
we
must annualize.

Instead, I prefer to specify appropriate periodic rates of return. *So
instead of annualizing a periodic IRR, I prefer to "de-compound" the
annual(ized) XIRR rate. *For example, (1+XIRR(...))^(1/12)-1.

Nevertheless, that monthly XIRR will not be the same as the periodic IRR

based on monthly cash flows for all of the reasons given above.

Thanks for the help. *Here are the monetary inputs.

1-Jan-12 * * * * (130,000)
31-Jan-12 * * * *6,685
29-Feb-12 * * * *13,133
31-Mar-12 * * * *19,262
30-Apr-12 * * * *24,986
31-May-12 * * * *30,386
30-Jun-12 * * * *35,549
31-Jul-12 * * * *40,478
31-Aug-12 * * * *45,168
30-Sep-12 * * * *49,682
31-Oct-12 * * * *54,075
30-Nov-12 * * * *58,357
31-Dec-12 * * * *62,535

I guess my follow up question now is how do I explain in laymans terms
to superiors that it is an annual 640%? *When we haven't even increased
the original outlay by 640%. *They seem to think that it should mean we
should make 6.4 * 130,000. for it to be a 640% return. *They think that
the 217% makes the most sense.

--
Csmithers

Well you made a common error in selection of the date for cash flow at
time period t=0 thus your IRR and XIRR numbers mismatched

The date for time period t = 0 would be 12/31/2011 rather than
1/1/2012

Once you make this correction the XIRR would equal 633.07% which is
the same as the annualized IRR you calculated

And the monthly IRR would equal the IRR you calculated by using Excel
IRR function

(1 + i)^12 - 1 = 633.07%
(1 + i)^12 = 6.3307 + 1
(1 + i)^12 = 7.3307
(1 + i) = (7.3307)^1/12
1 + i = 1.1805800877156739686170250080055
i = 1.18058 - 1
i = 0.18058
i = 0.1806
i = 18.06%