XIRR Giving Negative Number on Positive Cash Flow Stream

[W]

I use XIRR to calculate a compound annual return on investments. This
works fine when the first value is negative and the others are positive.
However I cannot get it to work for a short sale. For a short sale, you
are paid up front, and then you pay back the investment later.

If I have a short sale like:

1/27/2012 2/3/2012
$52 -$50

XIRR tells me it is an annualized return of -87.1% (NEGATIVE return, even
though it is net positive cash)

If I reverse this:

1/27/2012 2/3/2012
-$50 $52

XIRR tells me it is 673% annualized return. But at least it is positive.

Why does XIRR have problems dealing with positive cash up front followed by
negative cash flows?

 

[joeu2004]

Please see the responses to your first posting.

In the future, please do not the same question multiple times, be it in the
same or multiple newsgroups. That usually leads to confusion, and it wastes
the time of responders.


----- original posting -----

 

[W]

Please see the responses to your first posting.

In the future, please do not the same question multiple times, be it in the
same or multiple newsgroups. That usually leads to confusion, and it wastes
the time of responders.

I have a glitch on giganews that is not allowing me to see either the
original post or any responses to it. Sorry, and it doesn't happen very
often.

 

[W]

And as a side effect of the point below, I have yet to see a single response
to the original post you refer to.

 

[joeu2004]

I have a glitch on giganews that is not allowing me
to see either the original post or any responses to
it. Sorry, and it doesn't happen very often.

Then I am not sure you will see this posting. Please acknowledge as soon as
you see this, even if you need more time to digest and respond to the
content.

For now, I suggest that you use Google Groups, if you can. Go to
http://groups.google.com and enter the newsgroup
microsoft.public.excel.misc.

Eventually, you might want to create a (free) account on
news.eternal-september.org. I don't believe I have had any trouble with not
seeing responses there. (Of course, it is difficult for me to know for sure
;->.)

The following are my responses (3) to your other thread. However, there are
responses from others that you might be interested in.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 17:05:52 -0700

what about if you have a short sale of stock, where
you receive funds before you buy and pay funds? [....]
Why can't XIRR deal with a positive followed by negatives?

It can. You are misinterpreting the reason why XIRR returns a negative IRR
in this case.

(By the way, I will use the terms XIRR and IRR interchangably. XIRR is
simply Excel's way of calculating the IRR when cash flows occur on irregular
dates.)

If I have a short sale like:
1/27/2012 2/3/2012
$52 -$50

XIRR tells me it is an annualized return of -87.1% (NEGATIVE
return, even though it is net positive cash)

And that is correct mathematically for the NPV formula that Excel uses to
calculate the (X)IRR.

The problem is that that definition of IRR is not appropriate for a short
sale. See the explanation below. But first....

If I reverse this:
1/27/2012 2/3/2012
-$50 $52

XIRR tells me it is 673% annualized return. But at least it is positive.

GIGO. That is not the "reverse" of the cash flows that model the short
sale. You had it right the first time.

-----

Returning to the original question....

The problem is: when IRR is computed using the NPV formula, the assumption
is that we have a profitable cash flow, ergo a positive IRR, when the sum of
the later cash flows is __greater__ than (minus) the initial cash flow.

That is a necessary mathematical requirement because, with a positive IRR,
the magnitudes of the later cash flows are reduced ("discounted"). If the
sum of the later cash flows were already less than the (minus) initial cash
flow, the sum of the discounted cash flows would be even smaller than the
(minus) initial cash flow. Ergo, the NPV could not be made zero with a
positive IRR.

But the point is: that assumption does not apply to short sales.

For a short sale to be profitable, the "sum of the later cash flows" (the
later purchase price less transaction costs) must be __less__ than (minus)
the initial sale proceeds (less transaction and margin costs).

So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1,
where "s" is the initial net sales proceeds, "p" is the later net purchase
proceeds, and "days" is the hold time.

For your example, that would be about 615.56% annualized IRR.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 17:29:23 -0700

So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1,
where "s" is the initial net sales proceeds, "p" is the later net purchase
proceeds, and "days" is the hold time.

For your example, that would be about 615.56% annualized IRR.

In case it is not obvious from the description, "s" and "p" are both
positive typically. That is, they are __not__ signed cash flows.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 18:32:40 -0700

So for short sales, the annualized IRR is
(1 + (s-p)/s)^(365/days) - 1, where "s" is the initial
net sales proceeds, "p" is the later net purchase proceeds, and "days" is
the hold time. For your example,
that would be about 615.56% annualized IRR.

Needless to say, the __annualized__ IRR is misleading, IMHO.

I don't know what conventional practice is for short sellers. But IMHO, for
hold times of less than 1 year, I would use the simple rate, namely (s-p)/p
formatted as Percentage.

Granted, that does not take "time value" into account. But I think any
attempt to do so is misleading.

Consider your example, but with a hold time of 14 days. The annualized IRR
would be about 167.50%. Do you really __feel__ (subjectively) that the
"rate of return" is more than 3.5 times better when the hold time is only 7
days?

 

[W]

I got your responses, thanks.

I can only say this is fairly hideous in how they implemented it. The
function has complex assumptions, and it seems like a pretty broken
implementation to have to keep all of that straight.

--
W

I have a glitch on giganews that is not allowing me
to see either the original post or any responses to
it. Sorry, and it doesn't happen very often.

Then I am not sure you will see this posting. Please acknowledge as soon as
you see this, even if you need more time to digest and respond to the
content.

For now, I suggest that you use Google Groups, if you can. Go to
http://groups.google.com and enter the newsgroup
microsoft.public.excel.misc.

Eventually, you might want to create a (free) account on
news.eternal-september.org. I don't believe I have had any trouble with not
seeing responses there. (Of course, it is difficult for me to know for sure
;->.)

The following are my responses (3) to your other thread. However, there are
responses from others that you might be interested in.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 17:05:52 -0700

what about if you have a short sale of stock, where
you receive funds before you buy and pay funds? [....]
Why can't XIRR deal with a positive followed by negatives?

It can. You are misinterpreting the reason why XIRR returns a negative IRR
in this case.

(By the way, I will use the terms XIRR and IRR interchangably. XIRR is
simply Excel's way of calculating the IRR when cash flows occur on irregular
dates.)

If I have a short sale like:
1/27/2012 2/3/2012
$52 -$50

XIRR tells me it is an annualized return of -87.1% (NEGATIVE
return, even though it is net positive cash)

And that is correct mathematically for the NPV formula that Excel uses to
calculate the (X)IRR.

The problem is that that definition of IRR is not appropriate for a short
sale. See the explanation below. But first....

If I reverse this:
1/27/2012 2/3/2012
-$50 $52

XIRR tells me it is 673% annualized return. But at least it is

positive.

GIGO. That is not the "reverse" of the cash flows that model the short
sale. You had it right the first time.

-----

Returning to the original question....

The problem is: when IRR is computed using the NPV formula, the assumption
is that we have a profitable cash flow, ergo a positive IRR, when the sum of
the later cash flows is __greater__ than (minus) the initial cash flow.

That is a necessary mathematical requirement because, with a positive IRR,
the magnitudes of the later cash flows are reduced ("discounted"). If the
sum of the later cash flows were already less than the (minus) initial cash
flow, the sum of the discounted cash flows would be even smaller than the
(minus) initial cash flow. Ergo, the NPV could not be made zero with a
positive IRR.

But the point is: that assumption does not apply to short sales.

For a short sale to be profitable, the "sum of the later cash flows" (the
later purchase price less transaction costs) must be __less__ than (minus)
the initial sale proceeds (less transaction and margin costs).

So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1,
where "s" is the initial net sales proceeds, "p" is the later net purchase
proceeds, and "days" is the hold time.

For your example, that would be about 615.56% annualized IRR.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 17:29:23 -0700

So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1,
where "s" is the initial net sales proceeds, "p" is the later net purchase
proceeds, and "days" is the hold time.

For your example, that would be about 615.56% annualized IRR.

In case it is not obvious from the description, "s" and "p" are both
positive typically. That is, they are __not__ signed cash flows.


====================
From: "joeu2004" <[email protected]>
Date: Sun, 8 Apr 2012 18:32:40 -0700

So for short sales, the annualized IRR is
(1 + (s-p)/s)^(365/days) - 1, where "s" is the initial
net sales proceeds, "p" is the later net purchase proceeds, and "days" is
the hold time. For your example,
that would be about 615.56% annualized IRR.

Needless to say, the __annualized__ IRR is misleading, IMHO.

I don't know what conventional practice is for short sellers. But IMHO, for
hold times of less than 1 year, I would use the simple rate, namely (s-p)/p
formatted as Percentage.

Granted, that does not take "time value" into account. But I think any
attempt to do so is misleading.

Consider your example, but with a hold time of 14 days. The annualized IRR
would be about 167.50%. Do you really __feel__ (subjectively) that the
"rate of return" is more than 3.5 times better when the hold time is only 7
days?

 

[joeu2004]

I can only say this is fairly hideous in how they
implemented it. The function has complex assumptions,
and it seems like a pretty broken implementation to have
to keep all of that straight.

I think you misunderstood. It has nothing to do with assumptions of any
Excel function or its implementation. It is the __concept__ of IRR (its
most common definition, that is) that I was speaking about.

You asked why the IRR is not a positive percentage, which you expected for
the short sale that resulted in a net gain.

I tried to explain that IRR, when defined to be the discount rate that
causes the NPV to be zero, is simply not the correct concept for you to use
for that situation.

Perhaps I made things too complicated for you when I tried to explain why,
namely: if you are expecting a positive IRR, the magnitude of the sum of
the later cash flows must be greater than and the opposite of sign (positive
or negative) of the initial cash flow. It is simply a mathematical reality
of the NPV formula.

If you don't gronk that, ignore the technical explanation. Just know that
the (NPV-based) IRR is not the right concept for you to use. (And I never
heard of any other definition of IRR.)

I also provided you with the correct formula to use for computing the
annualized rate of return for a short sale, namely:

(1 + (s-p)/s)^(365/days) - 1

where "s" is the initial net sales proceeds (less transaction and margin
costs), "p" is the later net purchase proceeds (less transaction costs), and
"days" is the hold time.

That should answer the question for you.

 

[W]

I think you misunderstood. It has nothing to do with assumptions of any
Excel function or its implementation. It is the __concept__ of IRR (its
most common definition, that is) that I was speaking about.

You asked why the IRR is not a positive percentage, which you expected for
the short sale that resulted in a net gain.

I tried to explain that IRR, when defined to be the discount rate that
causes the NPV to be zero, is simply not the correct concept for you to use
for that situation.

Perhaps I made things too complicated for you when I tried to explain why,
namely: if you are expecting a positive IRR, the magnitude of the sum of
the later cash flows must be greater than and the opposite of sign (positive
or negative) of the initial cash flow. It is simply a mathematical reality
of the NPV formula.

If you don't gronk that, ignore the technical explanation. Just know that
the (NPV-based) IRR is not the right concept for you to use. (And I never
heard of any other definition of IRR.)

I also provided you with the correct formula to use for computing the
annualized rate of return for a short sale, namely:

(1 + (s-p)/s)^(365/days) - 1

where "s" is the initial net sales proceeds (less transaction and margin
costs), "p" is the later net purchase proceeds (less transaction costs), and
"days" is the hold time.

That should answer the question for you.

Is there any third party vendor who sells an XIRR substitute that would work
for both negative and positive cash flows, short sale as well as a normal
buy and sell model?

To have to mentally switch between different calculation approaches based on
very detailed understanding of the underlying implementation of each
function is just complex.

 

[PJ Hooker]

Is there any third party vendor who sells an XIRR substitute that would work
for both negative and positive cash flows, short sale as well as a normal
buy and sell model?

To have to mentally switch between different calculation approaches basedon
very detailed understanding of the underlying implementation of each
function is just complex.

No implementation of XIRR will do what you are expecting. XIRR is the
internal rate of return for irregular cash flows and the it is
calculated using iterative methods. IRR calculations make use of fact
that it is the rate of interest at which a series of cash flows have
any of the following qualities

1. At IRR, the NPV is 0
2. At IRR, the NFV is 0
3. At IRR, the profitability index is zero

And following are the mathematical equations that define NPV, NFV, and
profitability index as explained in detail here at http://finance.thinkanddone.com/irr.html

NPV = CF0 + CF1(1+i)^-1 + CF2(1+i)^-2 + ... + CFn(1+i)^-n = 0

NFV = CF0(1+i)^n+1 + CF1(1+i)^n + CF2(1+i)^n-1 + ... + CFn(1+i) = 0

Profitability Index
B0 + B1(1+i)^-1 + B2(1+i)^-2 + ... + Bn(1+i)^-n
------------------------------------------------------------------- =
1
C0 + C1(1+i)^-1 + C2(1+i)^-2 + ... + Cn(1+i)^-n

i is the internal rate of return

You can not solve for i in any of these equations you need to solve
for it using iterative or numerical techniques such as Newton Raphson
method, Secant method, Müller's Method, etc

The difference between IRR and XIRR is the timing of the cash flows,
with IRR t ranges from 0 to N-1 or from 1 to N in case of NPV. With
XIRR, t is actual timings of the cash flow calculated with difference
of number of days between CFt and CF0 divided by 365 days

As you have cash flows with date schedule there is no other function
in Excel other than XIRR that finds rate of return

You may wish to try out TADXL add-in which has financial functions
such as XMIRR that finds modified internal rate of return for
irregular cash flows.

 

[PJ Hooker]

No implementation of XIRR will do what you are expecting. XIRR is the
internal rate of return for irregular cash flows and the it is
calculated using iterative methods. IRR calculations make use of fact
that it is the rate of interest at which a series of cash flows have
any of the following qualities

1. At IRR, the NPV is 0
2. At IRR, the NFV is 0
3. At IRR, the profitability index is zero

And following are the mathematical equations that define NPV, NFV, and
profitability index as explained in detail here athttp://finance.thinkanddone.com/irr.html

NPV = CF0 + CF1(1+i)^-1 + CF2(1+i)^-2 + ... + CFn(1+i)^-n = 0

NFV = CF0(1+i)^n+1 + CF1(1+i)^n + CF2(1+i)^n-1 + ... + CFn(1+i) = 0

Profitability Index
B0 + B1(1+i)^-1 + B2(1+i)^-2 + ... + Bn(1+i)^-n
------------------------------------------------------------------- =
1
C0 + C1(1+i)^-1 + C2(1+i)^-2 + ... + Cn(1+i)^-n

i is the internal rate of return

You can not solve for i in any of these equations you need to solve
for it using iterative or numerical techniques such as Newton Raphson
method, Secant method, Müller's Method, etc

The difference between IRR and XIRR is the timing of the cash flows,
with IRR t ranges from 0 to N-1 or from 1 to N in case of NPV. With
XIRR, t is actual timings of the cash flow calculated with difference
of number of days between CFt and CF0 divided by 365 days

As you have cash flows with date schedule there is no other function
in Excel other than XIRR that finds rate of return

You may wish to try out TADXL add-in which has financial functions
such as XMIRR that finds modified internal rate of return for
irregular cash flows.

EDIT---

At IRR, the profitability index is 1

1. At IRR, the NPV is 0
2. At IRR, the NFV is 0
3. At IRR, the profitability index is 1

 

[W]

Thanks for the referral to the TADXL add in. That looks much more robust.
Have you used it, and have you heard any general feedback on it?

--
W

Is there any third party vendor who sells an XIRR substitute that would work
for both negative and positive cash flows, short sale as well as a normal
buy and sell model?

To have to mentally switch between different calculation approaches based on
very detailed understanding of the underlying implementation of each
function is just complex.

No implementation of XIRR will do what you are expecting. XIRR is the
internal rate of return for irregular cash flows and the it is
calculated using iterative methods. IRR calculations make use of fact
that it is the rate of interest at which a series of cash flows have
any of the following qualities

1. At IRR, the NPV is 0
2. At IRR, the NFV is 0
3. At IRR, the profitability index is zero

And following are the mathematical equations that define NPV, NFV, and
profitability index as explained in detail here at
http://finance.thinkanddone.com/irr.html

NPV = CF0 + CF1(1+i)^-1 + CF2(1+i)^-2 + ... + CFn(1+i)^-n = 0

NFV = CF0(1+i)^n+1 + CF1(1+i)^n + CF2(1+i)^n-1 + ... + CFn(1+i) = 0

Profitability Index
B0 + B1(1+i)^-1 + B2(1+i)^-2 + ... + Bn(1+i)^-n
------------------------------------------------------------------- =
1
C0 + C1(1+i)^-1 + C2(1+i)^-2 + ... + Cn(1+i)^-n

i is the internal rate of return

You can not solve for i in any of these equations you need to solve
for it using iterative or numerical techniques such as Newton Raphson
method, Secant method, Müller's Method, etc

The difference between IRR and XIRR is the timing of the cash flows,
with IRR t ranges from 0 to N-1 or from 1 to N in case of NPV. With
XIRR, t is actual timings of the cash flow calculated with difference
of number of days between CFt and CF0 divided by 365 days

As you have cash flows with date schedule there is no other function
in Excel other than XIRR that finds rate of return

You may wish to try out TADXL add-in which has financial functions
such as XMIRR that finds modified internal rate of return for
irregular cash flows.

 

[PJ Hooker]

TADXL has been on the market since early March 2012 and none of the
100 customers thus far have complained.
To the contrary, those who have used TADXL have requested more custom
functions to be included in TADXL's next version

Have a view of some of these TADXL video tutorials at
http://www.youtube.com/user/TADXL/feed