XIRR

[W]

The XIRR function seems to want an array of values where the first element
is always negative, and the rest are positive. So I model a stock purchase
and sale as the first number being negative, and the sale being positive.
But what about if you have a short sale of stock, where you receive funds
before you buy and pay funds? That makes the first cell of the value array
positive. XIRR freaks out and gives a #NUM! result. Why can't XIRR deal
with a positive followed by negatives? It is weird to have to distort every
financial transaction to make it look like something XIRR can work with.

Is there any third party software the implements an XIRR that is more
powerful and can take more realistic inputs?

 

[PJ Hooker]

That makes the first cell of the value array
positive. XIRR freaks out and gives a #NUM! result. Why can't XIRR deal
with a positive followed by negatives?

If XIRR does return a #NUM error, in such a case you may wish to
provide a value for GUESS that is in close proximity to the actual
rate of return

#NUM error signifies that Excel has exhausted all 20 iterations and
failed to find the IRR or it may be that there is an intermediate
calculation where the differential of the XNPV function is ZERO

Is there any third party software the implements an XIRR that is more
powerful and can take more realistic inputs?

Although this may not solve your concern with stock data calculations
for XIRR yet you may want to have a look at TADXL add-in for finance.
This add-in provides new financial functions for Excel and extends the
existing financial functions. Excel's financial functions or most of
them assume interest is compounded discretely on a periodic basis.
Whereas in finance and banking interest is usually compounded
continuously.

Here TADXL extends the 5 TVM functions of FV, PV, NPER, PMT & RATE to
permit selection of either discrete or continuous compounding. NPV
function of Excel calculates net present value of an annuity with end
of period payments and you have to mutate it to find net present value
of annuity with start of period payments.

Then there are a good number of financial functions that aren't there
in Excel, for example, you can't calculate payback period
(undiscounted or discounted), profitability index, benefits to costs
ratio, net future value.

Here too, TADXL has new functions that make it possible to perform
capital budgeting techniques I listed in the last para. And then
building on them it provides the corresponding functions for irregular
cash flows where as Excel only has two such functions namely XIRR and
XNPV