I wrote:
But I believe someone showed how to call the XIRR add-in
(in Excel 2003) directly in VBA.

I meant to add: but in Excel 2007, we might be able to write simply
WorksheetFunction.Xirr(...,...).

Also, I believe I stumbled upon differences between the the atpvbaen.xla
implementation of XIRR and the ATP XIRR that Excel 2003 uses. I would not
be surprised if there were also differences between the VBA and Excel
implementations in Excel 2007. As I recall (vaguely and perhaps
incorrectly), the differences were in error handling, which the ATP XIRR
does not do well anyway. So the differences might be of little or no
concern.

For some reason, I am having difficulty finding the discussion of all this
not too long ago. That's why I'm being vague. And I hope my comments are
not a misdirection. (Sorry*2.)


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Fred Smith" wrote:
A UDF would copy the cells to an unused portion (or unused sheet), then
do the calculation. Why not just do this yourself in your spreadsheet?

The manual approach is reasonable to do if it must be done just a few
times. But it is a challenge to make that work for any arbitrary set of
value and date ranges.

As you say, the simple UDF would copy and execute
Evaluate("XIRR(...,...)"). But I believe someone showed how to call the
XIRR add-in (in Excel 2003) directly in VBA. The key is using a
Reference, which I do not remember off-hand.

Oh, I see I have it already (atpvbaen.xla). Well, the key is remembering
how to set that up and the syntax for the call. All that escapes me at
the moment. As icing on the cake, it would be nice if the UDF worked with
a reference union directly; alternatively, we can define an ad hoc calling
sequence for specifying both the value list and date list to be
variable-length.


----- original message -----

"Fred Smith" wrote in message
...
A UDF would copy the cells to an unused portion (or unused sheet), then do
the calculation. Why not just do this yourself in your spreadsheet?

Regards,
Fred

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less