XIRR versus IRR help!

[Seth M]

Hi:

I am trying to figure out how to calculate the IRR of a series of
cashflows. There are 177 cashflows of $41,422, and the up-front cost
is $3,330,786. I tried using an IRR, but that just gives me an error I
think because there are too many cashflows. I used an XIRR, but this
compounds daily, and thus gives me a different result than an HP-12C.
Please advise!

 

[Ron Rosenfeld]

Hi:

I am trying to figure out how to calculate the IRR of a series of
cashflows. There are 177 cashflows of $41,422, and the up-front cost
is $3,330,786. I tried using an IRR, but that just gives me an error I
think because there are too many cashflows. I used an XIRR, but this
compounds daily, and thus gives me a different result than an HP-12C.
Please advise!

When you get the #NUM error, it usually means you need to use a guess that is
closer to your expected return than the default of 10%. (See HELP for the IRR
worksheet function).

Using a guess of 1%, I get a result of 1.0467%

However, since your cashflows are all the same, you could also use the RATE
function:


=RATE(177,41422,-330786,0)

--ron

 

[Seth M]

When you get the #NUM error, it usually means you need to use a guess that is
closer to your expected return than the default of 10%.  (See HELP for the IRR
worksheet function).

Using a guess of 1%, I get a result of 1.0467%

However, since your cashflows are all the same, you could also use the RATE
function:

=RATE(177,41422,-330786,0)

--ron

Thanks, that helps. Is my initial attempt using the XIRR function
incorrect because of the daily compounding?

 

[JoeU2004]

Is my initial attempt using the XIRR function
incorrect because of the daily compounding?

No one can say for sure because you do not show how you used XIRR and IRR,
and you do not tell us exactly what the results of XIRR and IRR were.

But note that Excel XIRR returns an annualized rate, whereas Excel IRR and
RATE return the periodic rate for each cash flow, presumed to be equally
spaced, as does the HP 12C IRR.

To convert the XIRR result to a periodic rate, we need to know the frequency
of the cash flows per year. For example, if they are monthly, then:

=(1 + XIRR(...))^(1/12) - 1

where "..." are the arguments that you passed to XIRR.

Nonetheless, yes, the periodic rate derived from XIRR will probably be
slightly different than the rate returned by IRR or RATE (or the HP 12C).
As you say, this because XIRR presumes daily compounding based on the exact
dates.

For example, if you have monthly cash flows on the first of Jan, Feb, Mar,
Apr and May, IRR and RATE presume that the cash flows are equally spaced,
whereas XIRR will treat them as 31, 28 or 29, 31 and 30 days apart.

But there are other usage errors that you might have made.

First, note that in Ron's use of RATE, the initial cash flow is negative and
the subsequent cash flows are positive. The point is: net inflows and net
outflows must have opposite signs.

Since the HP 12C has the same requirement, we might assume you used IRR,
XIRR and RATE correctly in that respect.

Second, we do not really know that your cash flows are equally spaced. You
did not say. Since the HP 12C IRR presumes equally-spaced cash flows, we
might assume that they are. But that assumes that you know how to use IRR
correctly in the first place.

You know what they say about "ass-u-me" ;-).

If you have any doubts about your use of these functions, I suggest that you
post usage, especially how you used XIRR.


----- original message -----

When you get the #NUM error, it usually means you need to use a guess that
is
closer to your expected return than the default of 10%. (See HELP for the
IRR
worksheet function).

Using a guess of 1%, I get a result of 1.0467%

However, since your cashflows are all the same, you could also use the
RATE
function:

=RATE(177,41422,-330786,0)

--ron

Thanks, that helps. Is my initial attempt using the XIRR function
incorrect because of the daily compounding?

 

[Ron Rosenfeld]

Thanks, that helps. Is my initial attempt using the XIRR function
incorrect because of the daily compounding?

They have different purposes. XIRR returns the internal rate of return for a
schedule of cash flows that is not necessarily periodic. To calculate the
internal rate of return for a series of periodic cash flows, use the IRR
function. And if the cash flows are identical, you can use the RATE function.
--ron

 

[JoeU2004]

Clarification....

Nonetheless, yes, the periodic rate derived from XIRR
will probably be slightly different than the rate
returned by IRR or RATE (or the HP 12C). As you say,
this because XIRR presumes daily compounding based on
the exact dates.

Well, it is because XIRR computes the annualized rate based on the exact
days between the initial cash flow and the i-th cash flow.

In contrast, IRR and RATE compute the periodic rate based on the assumption
that cash flows are equally spaced.

To illustrate, the IRR and RATE results are about the same as the XIRR
result when you consider annual cash flows on Jan 1 of 2009, 2010, 2011 and
2012. Each cash flow is 365 days apart, equally spaced as IRR and RATE
assume.

In contrast, consider the annual cash flows on Jan 1 of 2008, 2009, 2010 and
2011. Now the XIRR differs from the IRR and RATE results the cash flows are
not really equally spaced; one cash flow is 366 days apart.


----- original message -----

 

[JoeU2004]

PS....

To convert the XIRR result to a periodic rate,
we need to know the frequency of the cash flows
per year. For example, if they are monthly, then:
=(1 + XIRR(...))^(1/12) - 1

Conversely, you can annualize the IRR or RATE result. Again, we need to
know the frequency of the cash flows per year. The following assumes
monthly.

To get an annualized rate comparable to XIRR, you could compound the monthly
rate, thus:

=(1+IRR(...))^12 - 1

where "..." represents the arguments to the IRR() function.

However, it is not uncommon to simply multiply the monthly rate, thus:

=12*IRR(...)

In that case, you might see a significant difference with the XIRR result.
And that difference is indeed due to differences in assumptions about the
compounding frequency.


----- original message -----