XIRR range question

[Brad]

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.

 

[Joe User]

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,D341)). 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]

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
D35 are three dates

M3:M40 and N41 are 39 values
D341 are 39 dates

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

 

[Fred Smith]

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

 

[Joe User]

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

Well, obviously. My bad! I was rushed and misread your syntax, which was
clear on second thought. Sorry about that.

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

I or someone can help you with that -- eventually. In the meantime, you
might do a Google Groups search. I believe a UDF was posted not too long ago.


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

 

[Joe User]

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

 

[Joe User]

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

 

[Ron Rosenfeld]

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.

You need a UDF.

Harlan Grove contributed this some years ago. It was prior to Excel 2007 and
therefore requires setting an explicit reference to ATPVBAEN.XLS.

If you have Excel 2007 or later, XIRR is a worksheet function and you will need
to rewrite it a bit, by changing XIRR to worksheetfunction.xirr

Also note the requirement to NAME the non-contiguous range.

==============================================================
Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to ATPVBAEN.XLA
'if v and/or d represent non-contiguous ranges, they should be NAME'd
'-------------------------------------------------------
Dim vv As Variant, dd As Variant, x As Variant, i As Long

If TypeOf v Is Range Then
ReDim vv(1 To v.Cells.Count)
i = 0
For Each x In v
i = i + 1
vv(i) = x.Value
Next x
Else
vv = v
End If

If TypeOf D Is Range Then
ReDim dd(1 To D.Cells.Count)
i = 0
For Each x In D
i = i + 1
dd(i) = x.Value
Next x
Else
dd = D
End If

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))
End Function
====================================================
--ron

 

[Joe User]

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

 

[Joe User]

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]

If TypeOf v Is Range Then

I was not aware of the "typeof ... is" construct. And I cannot seem to
coerce VBA Help to tell me about it. (Can someone tell me how? I am using
VBA 6.5.1024.)

What are all of the names (like Range) that "typeof v is" can be compared
to?

For example....

When v is the form {1,2} in Excel, TypeName(v) returns "Variant()". How
would I write the "typeof v is" expression to recognize that type?

When v is of the form -1.2 in Excel, TypeName(v) returns "Double". How
would I I write the "typeof v is" expression to recognize that type?
(Typeof v Is Double does not work!)

When v is of the form "hi" in Excel, TypeName(v) returns "String". How
would I write the "typeof v is" expression to recognize that type? (Typeof
v is String does not work!)

Is "Typeof v Is Range" and "Not Typeof v Is Range" the only valid
expressions using Typeof?

(Hmm, maybe also "[Not] Typeof v is" any user-defined type, based on a
syntax error that I get.)


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

 

[Joe User]

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]

Harlan Grove contributed this some years ago.

(Sorry, I have been attributing this version to Ron in my other postings.)

It is tighter than my verion. However, I would make the following changes:

Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant

I would use Optional g As Double = 0.1 to be consistent with Excel (2003).
Then....

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))

..... Can simply be:

myxirr = Xirr(vv, dd, g)

Also note the requirement to NAME the non-contiguous range. [....]
'if v and/or d represent non-contiguous ranges, they should be NAME'd

I 'spose Harlan meant that as a style suggestion. It certainly is not a
"requirement" in Excel 2003. Whether they are named ranges in Excel or
explicit reference unions of the form (B1,C1:C3,D4) in the function
expression in Excel, it is transparent to the UDF.

If you have Excel 2007 or later, XIRR is a worksheet function
and you will need to rewrite it a bit, by changing XIRR to
worksheetfunction.xirr

Good point! I neglected to make that clear in my instruction.

For completeness, I'll repeat the Excel 2003 instructions that I included
with my version, since I believe they are not obvious.

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


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

 

[Joe User]

For posterity....

Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)

That second Select statement should be:

Select Case TypeName(d)

It was right in my original version.


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

 

[Joe User]

Errata....

Also note the requirement to NAME the non-contiguous range. [....]
'if v and/or d represent non-contiguous ranges, they should be NAME'd

I 'spose Harlan meant that as a style suggestion. It certainly is not a
"requirement" in Excel 2003. Whether they are named ranges in Excel or
explicit reference unions of the form (B1,C1:C3,D4) in the function
expression in Excel, it is transparent to the UDF.

My mistake. I don't know if a named range makes any difference, but the
explicit reference union does not work with Harlan's version.

It does work with my version.


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

Harlan Grove contributed this some years ago.

(Sorry, I have been attributing this version to Ron in my other postings.)

It is tighter than my verion. However, I would make the following
changes:

Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant

I would use Optional g As Double = 0.1 to be consistent with Excel (2003).
Then....

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))

.... Can simply be:

myxirr = Xirr(vv, dd, g)

Also note the requirement to NAME the non-contiguous range. [....]
'if v and/or d represent non-contiguous ranges, they should be NAME'd

I 'spose Harlan meant that as a style suggestion. It certainly is not a
"requirement" in Excel 2003. Whether they are named ranges in Excel or
explicit reference unions of the form (B1,C1:C3,D4) in the function
expression in Excel, it is transparent to the UDF.

If you have Excel 2007 or later, XIRR is a worksheet function
and you will need to rewrite it a bit, by changing XIRR to
worksheetfunction.xirr

Good point! I neglected to make that clear in my instruction.

For completeness, I'll repeat the Excel 2003 instructions that I included
with my version, since I believe they are not obvious.

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


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

You need a UDF.

Harlan Grove contributed this some years ago. It was prior to Excel 2007
and
therefore requires setting an explicit reference to ATPVBAEN.XLS.

If you have Excel 2007 or later, XIRR is a worksheet function and you
will need
to rewrite it a bit, by changing XIRR to worksheetfunction.xirr

Also note the requirement to NAME the non-contiguous range.

==============================================================
Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to ATPVBAEN.XLA
'if v and/or d represent non-contiguous ranges, they should be NAME'd
'-------------------------------------------------------
Dim vv As Variant, dd As Variant, x As Variant, i As Long

If TypeOf v Is Range Then
ReDim vv(1 To v.Cells.Count)
i = 0
For Each x In v
i = i + 1
vv(i) = x.Value
Next x
Else
vv = v
End If

If TypeOf D Is Range Then
ReDim dd(1 To D.Cells.Count)
i = 0
For Each x In D
i = i + 1
dd(i) = x.Value
Next x
Else
dd = D
End If

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))
End Function
====================================================
--ron

 

[Joe User]

Arrgghh!....

Errata.... [....]
My mistake. I don't know if a named range makes any
difference, but the explicit reference union does not
work with Harlan's version.

I was right the first time: Harlan's verion works just fine with explicit
reference unions, too.

My incorrect "errata" was due to a typo while I was messing around with both
verions. Klunk!


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

Errata....

Also note the requirement to NAME the non-contiguous range. [....]
'if v and/or d represent non-contiguous ranges, they should be NAME'd

I 'spose Harlan meant that as a style suggestion. It certainly is not a
"requirement" in Excel 2003. Whether they are named ranges in Excel or
explicit reference unions of the form (B1,C1:C3,D4) in the function
expression in Excel, it is transparent to the UDF.

My mistake. I don't know if a named range makes any difference, but the
explicit reference union does not work with Harlan's version.

It does work with my version.


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

Harlan Grove contributed this some years ago.

(Sorry, I have been attributing this version to Ron in my other
postings.)

It is tighter than my verion. However, I would make the following
changes:

Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant

I would use Optional g As Double = 0.1 to be consistent with Excel
(2003). Then....

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))

.... Can simply be:

myxirr = Xirr(vv, dd, g)

Also note the requirement to NAME the non-contiguous range. [....]
'if v and/or d represent non-contiguous ranges, they should be NAME'd

I 'spose Harlan meant that as a style suggestion. It certainly is not a
"requirement" in Excel 2003. Whether they are named ranges in Excel or
explicit reference unions of the form (B1,C1:C3,D4) in the function
expression in Excel, it is transparent to the UDF.

If you have Excel 2007 or later, XIRR is a worksheet function
and you will need to rewrite it a bit, by changing XIRR to
worksheetfunction.xirr

Good point! I neglected to make that clear in my instruction.

For completeness, I'll repeat the Excel 2003 instructions that I included
with my version, since I believe they are not obvious.

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


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

On Wed, 14 Apr 2010 12:53:02 -0700, Brad
<[email protected]>
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.

You need a UDF.

Harlan Grove contributed this some years ago. It was prior to Excel
2007 and
therefore requires setting an explicit reference to ATPVBAEN.XLS.

If you have Excel 2007 or later, XIRR is a worksheet function and you
will need
to rewrite it a bit, by changing XIRR to worksheetfunction.xirr

Also note the requirement to NAME the non-contiguous range.

==============================================================
Function myxirr( _
v As Variant, _
D As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to ATPVBAEN.XLA
'if v and/or d represent non-contiguous ranges, they should be NAME'd
'-------------------------------------------------------
Dim vv As Variant, dd As Variant, x As Variant, i As Long

If TypeOf v Is Range Then
ReDim vv(1 To v.Cells.Count)
i = 0
For Each x In v
i = i + 1
vv(i) = x.Value
Next x
Else
vv = v
End If

If TypeOf D Is Range Then
ReDim dd(1 To D.Cells.Count)
i = 0
For Each x In D
i = i + 1
dd(i) = x.Value
Next x
Else
dd = D
End If

myxirr = IIf(g <> 0, Xirr(vv, dd, g), Xirr(vv, dd))
End Function
====================================================
--ron

 

[Ron Rosenfeld]

I was not aware of the "typeof ... is" construct. And I cannot seem to
coerce VBA Help to tell me about it. (Can someone tell me how? I am using
VBA 6.5.1024.)

From VBA help for the If...The...Else Statement:

An expression of the form TypeOf objectname Is objecttype. The objectname is
any object reference and objecttype is any valid object type. The expression is
True if objectname is of the object type specified by objecttype; otherwise it
is False.

And from the glossary:

object type
A type of object exposed by an application through Automation, for example,
Application, File, Range, and Sheet. Use the Object Browser or refer to the
application's documentation for a complete listing of available objects.

Is "Typeof v Is Range" and "Not Typeof v Is Range" the only valid
expressions using Typeof?

It should work for any type of object exposed through automation.

I don't believe that many of the variable types meet that definition (e.g.
string, variant), but other objects do.

Consider this code for example:

============================
Sub foo()
'set reference to Microsoft VBScript Regular Expressions 5.5
Dim re As RegExp
Dim o As Object
Debug.Print "re", VarType(re)
Set re = New RegExp

If TypeOf re Is RegExp Then
Debug.Print VarType(re), "regexp"
End If
End Sub
============================
Immediate Window:

re 9
9 regexp

===============================

By the way, I do get the "NOT" construct to work provided the object is valid.

For example, with the reference to Microsoft VBScript REgular Expressions NOT
set:

========================================
Sub foo()
Dim re As Object
Dim o As Object
Debug.Print "re", VarType(re)
Set re = CreateObject("vbscript.regexp")

If Not TypeOf re Is Range Then
Debug.Print VarType(re), "not range"
End If
End Sub
==========================



--ron

 

[Ron Rosenfeld]

I was right the first time: Harlan's verion works just fine with explicit
reference unions, too.

It's been years since I used his unmodified version, and I've never checked
that out. And I don't recall what version of Excel I was using when he first
provided that. But that's good to know.
--ron

 

[Joe User]

[....] Harlan's verion works just fine with explicit
reference unions, too.

[....] I don't recall what version of Excel I was using
when he first provided that. But that's good to know.

Right. Harlan posted that implementation at least as long ago as Dec 2003.
Harlan did not refer to any earlier posting with that implementation. No
mention of the Excel or VBA version in the thread. See
groups.google.com/group/microsoft.public.excel.misc/msg/bb003cebc1f121c5.


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

 

[Joe User]

From VBA help for the If...Then...Else Statement:

Thanks for the pointer. BTW, the following seems to work fine:

v = Range("a1")
MsgBox TypeOf v Is Range

So TypeOf is an operator. I expected it to have its own help page, just as
the AddressOf operator does. Failing to find that, I also looked at
Operator Summary, Is Operator, and Comparison Operators, to no avail.
Didn't think to look at If...Then...Else help.

I don't believe that many of the variable types meet that
definition (e.g. string, variant), but other objects do.

Right. In fact, the If...Then...Else help states explicitly (VBA 6.5.1024):

"Note TypeOf cannot be used with hard data types such as
Long, Integer, and so forth other than Object."

Note that the TypeName function does work for as "obejct type" as well as
"hard date types". So we might think that TypeName is more general.

On the other hand, Typeof...Is works for user-defined data types, whereas
TypeName does not.

As always with MS, y'win some and y'lose some :-(.

By the way, I do get the "NOT" construct to work provided
the object is valid.

The following constructs seem to work just fine:

v = Range("a1")
MsgBox Not TypeOf v Is Range
If Not TypeOf v Is Range Then MsgBox "is false" _
Else MsgBox "is true"

However, note that v must actually contain a value. It is not sufficient to
merely declare the object type of v. For instance:

Dim v As Range
MsgBox TypeName(v)
MsgBox TypeOf v Is Range
MsgBox Not TypeOf v Is Range
If Not TypeOf v Is Range Then MsgBox "is false" _
Else MsgBox "is true"

TypeName returns "Nothing", but VBA reports a runtime error on the first
TypeOf reference.


PS: Sorry for the thread digression. I shoulda posted a separate thread.
In fact, I was gonna do just that when I logged on this morning, but Ron was
"too quick" to answer my question here .


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