On Apr 11, 6:56*am, PJ Hooker wrote:
On Apr 11, 1:56*am, "W" wrote:









"joeu2004" wrote in message

...

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

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.

--
W

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