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


"joeu2004" wrote in message
...
"W" wrote:
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"
Date: Sun, 8 Apr 2012 17:05:52 -0700

"W" wrote:
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.)


"W" wrote:
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....


"W" wrote:
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"
Date: Sun, 8 Apr 2012 17:29:23 -0700

PS.... I wrote:
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"
Date: Sun, 8 Apr 2012 18:32:40 -0700

PPS.... I wrote:
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?