"Joe User" <joeu2004 wrote in message:
I see that Ron posted a solution at about the same time that I did.
Combining the best of both....

On second thought, Ron's version has all the same error results that mine
and Excel XIRR have. So it may be the better implementation, since it is
tighter. See my follow-up comments to Ron's posting.


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

"Joe User" <joeu2004 wrote in message
...
I see that Ron posted a solution at about the same time that I did.
Combining the best of both....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range":
ReDim vv(1 To v.Count)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
Case "Variant()": vv = v
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
Case "Variant()": dd = d
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


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

"Joe User" <joeu2004 wrote in message
...
"Brad" wrote:
I'm willing to have my UDF, but not sure how that would be done.

The following UDF seems to have the same results for comparable Excel
XIRR usage.

For Excel 2003, you must select the add-in "Analysis ToolPak - VBA" in
Excel (Tools Add-ins), and you must select the reference "atpvbaen.xls"
in VBA (Tools References).

The UDF should work with your examples, when written correctly:

=myXIRR((m3:m4,n5), d3:d5)
=myXIRR((m3:m40,n41), d3:d41)

However, for thorough testing of any solution, I suggest that you set
B1:B7 to the values -10000, 2000, 3000, 4000, 5000, 6000, 7000, and set
C1:C7 to the dates 1/1/2011 through 1/1/2017. Then....

1. Compare to the following, all of which should return the same valid
result (about 29.79%):

=XIRR(B1:B7, C1:C7)
=myXIRR(B1:B7, C1:C7)
=myXIRR((B1,B2:B6,B7), (C1:C2,C3,C4,C5:C7))
=myXIRR({-10000,2000,3000,4000,5000,6000,7000}, C1:C7)

2. Compare the following error conditions:

a. =XIRR({-1E9,1}, C1:C2)
=myXIRR({-1E9,1}, C1:C2)

b. =XIRR({-10000}, C1)
=myXIRR({-10000}, C1)

c. =XIRR(-10000, C1)
=myXIRR(-10000, C1)

d. =XIRR("hi", C1)
=myXIRR("hi", C1)


To enter the UDF, copy the following text. In Excel, press alt+F11. In
VBA, click Insert Module, then paste into the VBA editor pane. Also
see the special steps for Excel 2003 above.

The UDF....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range": nv = v.Count
Case "Variant()": nv = UBound(v)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(d)
Case "Range": nd = d.Count
Case "Variant()": nd = UBound(d)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
ReDim vv(1 To nv)
ReDim dd(1 To nd)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


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

"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