If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
XIRR range question
For posterity....
I wrote: 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" 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 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. "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,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" 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 |
#12
|
|||
|
|||
XIRR range question
On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" joeu2004 wrote:
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 |
#13
|
|||
|
|||
XIRR range question
"Ron Rosenfeld" wrote:
On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" joeu2004 wrote: [....] 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 ----- "Ron Rosenfeld" wrote in message ... On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" joeu2004 wrote: 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 |
#14
|
|||
|
|||
XIRR range question
On Thu, 15 Apr 2010 08:25:52 -0700, "Joe User" joeu2004 wrote:
However, note that v must actually contain a value. It is not sufficient to merely declare the object type of v. For instance: I agree that it is NOT sufficient to merely declare the object type. However, I don't believe it is strictly true that the object must actually contain a value. There are objects that do not even support the Value property. --ron |
#15
|
|||
|
|||
XIRR range question
"Ron Rosenfeld" wrote:
I agree that it is NOT sufficient to merely declare the object type. However, I don't believe it is strictly true that the object must actually contain a value. There are objects that do not even support the Value property. Well, we can quibble over what I meant by the word "value". It certainly is not limited to mean that there is a Value property per se in the object. ----- original message ----- "Ron Rosenfeld" wrote in message ... On Thu, 15 Apr 2010 08:25:52 -0700, "Joe User" joeu2004 wrote: However, note that v must actually contain a value. It is not sufficient to merely declare the object type of v. For instance: I agree that it is NOT sufficient to merely declare the object type. However, I don't believe it is strictly true that the object must actually contain a value. There are objects that do not even support the Value property. --ron |
#16
|
|||
|
|||
XIRR range question
On Thu, 15 Apr 2010 14:55:40 -0700, "Joe User" joeu2004 wrote:
Well, we can quibble over what I meant by the word "value". It certainly is not limited to mean that there is a Value property per se in the object. OK, I was using in that sense, since that is how I determine the Value of a VB object. When you wrote that, my first attempt at verifying was to look for the Value of a regular expression object. (e.g. re.value) If you want to use it to mean something else, that's OK with me. --ron |
#17
|
|||
|
|||
XIRR range question
"Ron Rosenfeld" wrote:
If you want to use it to mean something else, that's OK with me. Thank you. I hope the next time that I mention the "value" of a Double variable v, you do not chide me because v.Value does not work :-) :-). ----- original message ----- "Ron Rosenfeld" wrote in message ... On Thu, 15 Apr 2010 14:55:40 -0700, "Joe User" joeu2004 wrote: Well, we can quibble over what I meant by the word "value". It certainly is not limited to mean that there is a Value property per se in the object. OK, I was using in that sense, since that is how I determine the Value of a VB object. When you wrote that, my first attempt at verifying was to look for the Value of a regular expression object. (e.g. re.value) If you want to use it to mean something else, that's OK with me. --ron |
#18
|
|||
|
|||
XIRR range question
On Thu, 15 Apr 2010 19:05:54 -0700, "Joe User" joeu2004 wrote:
Thank you. I hope the next time that I mention the "value" of a Double variable v, you do not chide me because v.Value does not work :-) :-). Of course not. But (more definitions now :-))) I don't think I would consider a Double to be an Object, in the sense we've been discussing. As you wrote: "Note TypeOf cannot be used with hard data types such as Long, Integer, and so forth other than Object." --ron |
|
Thread Tools | |
Display Modes | |
|
|