"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.