Calculating Returns

[Marc]

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000

How do I calculate returns for Goog and Intc individually, if I sold on the
21st Nov, and bought on three different dates?

I have a big list of such transactions. I want to calculate returns for
individual stocks.

 

[Don Guillett]

Your example is not clear. You do not show a before/after
I am a retired Reg Mgr for ING and held a series 7.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Joe User]

How do I calculate returns for Goog and Intc individually, if I
sold on the 21st Nov, and bought on three different dates?

Is there some reason why my response to your XIRR query does not answer the
question for you?

I am trying to calculate my gain on those stocks. How do I do that?

There are main ways of expressing gain, all valid. The IRR takes
time-value-of-money into account. But that is not the way that the IRS
calculates gain, for example.

If the IRR is not the answer you are looking for, it might help to know why
you want to calculate gain; that is, for what purpose.


----- response to previous message -----

I am trying to calculate my gain on those stocks. How do I do that?

If you want to use XIRR, it would be nice if you could write the formula
=XIRR((B2,B3,C7),(A2,A3,A7)), a form that the IRR function supports.

But the Excel 2003 XIRR does not. XIRR requires contiguous (adjacent) cells
in each range. (I don't know about Excel 2007.)

So....

I want to do a xirr for b1 and b6 with a1 and a6

Set up the following:

D1: =B1
D2: =C6

E1: =A1
E2: =A6

=XIRR(D12, E12)

FYI, for this simple investment, you can get about the same result by the
following:

=(1+RATE(A6-A1, 0, B1, C6))^365 - 1

Format the XIRR and RATE cells as Percentage.

I also want to the XIRR for b2, b3, c7 and the corresponding dates

Set up the following:

D3: =B2
D4: =B3
D5: =C7

E3: =A2
E4: =A3
E5: =A7

=XIRR(D35, E3:E5)

Format the XIRR cell as Percentage.


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

 

[Fred Smith]

XIRR is designed to do exactly what you want. Either look it up in help or
read the answers that have been provided to your previous postings.

Regards,
Fred.

 

[Marc]

Joe, Greatly appreciate your response here. Your original post for my
original query: You have to manually do what you said for those cells. I knew
how to do that.

My file is large. It has about 1000 entries with different stock tickers,
with different buy and sell dates. Was wondering if I could automate it
somehow.

The purpose is to know the gain I have in a year (or for the period I held
it for).
Yes, I realize IRS does not take time value into account. This is not for
IRS purposes.

Again, greatly appreciate your response.

 

[Joe User]

My file is large. It has about 1000 entries with different stock tickers,
with different buy and sell dates. Was wondering if I could automate it
somehow.

Aha! Now you asking the right question ;-).

Frankly, I would prefer to reorganize your data so that XIRR can be used
directly. Hindsight is 20-20. We could provide a macro to do that. Then
you can maintain the new organization going forward.

Alternatively, the following macro automates the manual steps that I believe
are necessary in order to compute the XIRR.

It is something of a kludge to work around the fact that
WorksheetFunctions.XIRR does not work in Excel 2003. If you have Excel
2007, see if WorksheetFunctions.XIRR is supported. If it is, the following
macro can be simplified to some degree.

Note: The macro assumes that the data in the 1000 entries is exactly as you
presented them in the examples in your posting. If the relavent data --
transaction date, security and amount -- are not exactly as you presented
them, the macro will need to be changed.

How to set up the macro:

1. Press alt+F11 to open the VBA window.

2. Click Insert > Module, which open the VBA editing pane.

3. Copy-and-paste the text of the macro below into the editing pane.

4. Edit the constants xRange, yRange, and myData as needed. xRange and
yRange must be two completely unused columns in the worksheet that contains
the 1000 entries. myData must be the name assigned to the range of 1000
entries below.

How to use the macro:

1. Select the entire range of data that includes all 1000 entries. Name the
range "data". If you prefer another name, you need to change myData in the
macro.

2. Find an area where you have at least 2 unused adjacent cells. In the
left cells, list the name of each security in the 1000 entries.

3. For each security name, select the cell, then execute the macro by
pressing alt+F8, selecting the macro name myXIRR, and clicking Run.

If this does not satisfy your needs, please do not start yet-another thread.
Simply post a response in this thread. That will permit others to see the
context of the question as well as what ideas failed to meet your needs.

Macro....

Sub myXIRR()
'*** modify the following constants ***
Const myDataName As String = "data"
Const tmpDateCol As String = "x"
Const tmpValCol As String = "y"

Dim myName As String, myData As Range
Dim n As Long, r As Long
Dim x1 As String, y1 As String, xyRange As String
Dim x1Range As String, y1Range As String

x1 = tmpDateCol & 1 'cell name "x1"
y1 = tmpValCol & 1 'cell name "y1"
x1Range = x1 & ":" & tmpDateCol 'partial range "x1:x"
y1Range = y1 & ":" & tmpValCol 'partial range "y1:y"
xyRange = tmpDateCol & ":" & tmpValCol 'range "x:y"
Set myData = Range(myDataName)
myName = UCase(Selection)

Range(xyRange).Clear
n = 0
For r = 1 To myData.Rows.Count
If UCase(myData.Cells(r, 2)) = myName Then
n = n + 1
Range(x1).Cells(n, 1) = myData.Cells(r, 1)
Range(y1).Cells(n, 1) = myData.Cells(r, 3)
ElseIf UCase(Range("data").Cells(r, 3)) = myName Then
n = n + 1
Range(x1).Cells(n, 1) = myData.Cells(r, 1)
Range(y1).Cells(n, 1) = myData.Cells(r, 2)
End If
Next r

If n > 0 Then
'execute formula =XIRR(y1:yN,x1:yN)
'then replace with value
Selection.Cells(1, 2).Formula = _
"=xirr(" & y1Range & n & "," & x1Range & n & ")"
Selection.Cells(1, 2) = Selection.Cells(1, 2)
Selection.Cells(1, 2).NumberFormat = "0.00%"
Range(xyRange).Clear
End If
End Sub


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

 

[Joe User]

PS....

My file is large. It has about 1000 entries with different stock tickers,
with different buy and sell dates. Was wondering if I could automate
it somehow.

If all of your investments have the same pattern as your examples, namely
one or two closely-timed buys and one sell, I would be tempted to eschew
XIRR, which seems to require a macro in your case, and use the following
paradigm (an array formula):

=(1 + RATE(MAX(IF($B$1:$B$7=A9,$A$1:$A$7)) -
MIN(IF($C$1:$C$7=A9,$A$1:$A$7)),
0, SUMIF($C$1:$C$7,A9,$B$1:$B$7), SUMIF($B$1:$B$7,A9,$C$1:$C$7)))^365 - 1

If RATE returns #NUM errors, you would need a "guess" argument, which is
hard to determine for one case, much less generalize for all investments.
So in that case, I would use the following equivalent paradigm (also an
array formula):

=(-SUMIF($B$1:$B$7,A9,$C$1:$C$7) / SUMIF($C$1:$C$7,A9,$B$1:$B$7))
^(365 / (MAX(IF($B$1:$B$7=A9,$A$1:$A$7)) -
MIN(IF($C$1:$C$7=A9,$A$1:$A$7)))) - 1

where A9 contains the security name, and B9 contains the formula above. The
B9 can be copied down into B10 etc. for each security name in A10 etc.

Note that an array formula is entered by pressing ctrl+shift+Enter, not
simply Enter. If done correctly, you should see curly braces around the
entire formula, i.e. {=formula}.

Also note that the range B1:B7 can be simplified if all the buys are in
B1:B3 and all sells are in B4:B7, for example. Likewise for C1:C7.

The formulas effectively compute (sellPrice/buyPrice)^(365/daysHeld) - 1,
where daysHeld is lastSellDate-firstBuyDate.

In the case of the "Goog" investment (single buy, single sell), the result
of the formulas above is about the same as XIRR. In the case of the "intc"
investment (two buys closely-timed, single sell), the result of the formulas
above is only slightly different from XIRR (66.43% instead of 67.85%). The
difference, if any, will depend on how closely-timed the multiple buys and
multiple sells are relative to the eariest buy and last sell.


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

 

[Ron Rosenfeld]

It is something of a kludge to work around the fact that
WorksheetFunctions.XIRR does not work in Excel 2003. If you have Excel
2007, see if WorksheetFunctions.XIRR is supported. If it is, the following
macro can be simplified to some degree.

Joe,

I'm pretty certain that if, in Excel 2003, you set an explicit reference
(Tools/References) to atpvbaen.xls, xirr can be used (directly).

In Excel 2007+, xirr IS a member of the worksheetfunction class.
--ron

 

[Ron Rosenfeld]

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000

How do I calculate returns for Goog and Intc individually, if I sold on the
21st Nov, and bought on three different dates?

I have a big list of such transactions. I want to calculate returns for
individual stocks.

Does your example above represent three different columns? Or is each line a
single cell?

If the data is in three separate columns, is it a typo that the last two lines
are in the order of Date | Company | CashFlow and the first three in the order
of Date | CashFlow | Company, or is that the way it really is?

Assuming that all of the company names are in the same column, and that all of
the cash flow amounts are in the same column, you could use a User Defined
Function to calculate this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=spXIRR(Values,Dates,Company_Names,DesiredCompany,[guess})

in some cell.

Values, Dates and Company_Names must all be single column (or single row)
ranges of the same size.

Values = the range (column) in which ALL of the values are found.
Dates = the range (column) in which ALL of the dates are found.
Company_Names = the range (column) in which ALL of the associated company names
are found

DesiredCompany is obvious.

Guess means the same here as for the XIRR function (see HELP).

In addition, the UDF will be different depending on the version of Excel you
have. In 2007, MS made XIRR part of the worksheetfunction group. Before that,
it was part of the analysis toolpak. So how it gets called will differ -- see
the comments within the UDF for instructions.

============================================
Option Explicit
Option Compare Text
Option Base 0
Function spXIRR(v As Range, d As Range, c As Range, _
DesiredCompany As String, Optional g As Double = 0.1) As Double
Dim vv(), dd()
Dim i As Long, j As Long
Dim r As Range

ReDim vv(0)
ReDim dd(0)
j = 0
For i = 1 To v.Count
If c(i, 1).Value = DesiredCompany Then
ReDim Preserve vv(j)
ReDim Preserve dd(j)
vv(j) = v(i).Value
dd(j) = d(i).Value
j = j + 1
End If
Next i

'if Excel 2007
spXIRR = WorksheetFunction.Xirr(vv, dd, g)

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.
'Then use this line instead:

'spXIRR = XIRR(vv,dd,g)

End Function
=====================================

--ron

 

[Ron Rosenfeld]

Deleted an extraneous line and added some error checking to ensure arrays are
one dimensional and of the same size. Otherwise, return #NA! error.

===============================
Option Explicit
Option Compare Text
Option Base 0
Function spXIRR(v As Range, d As Range, c As Range, _
DesiredCompany As String, Optional g As Double = 0.1) As Variant
Dim vv(), dd()
Dim i As Long, j As Long

ReDim vv(0)
ReDim dd(0)

'Do some error checking
'1D arrays:
If v.Rows.Count > 1 And v.Columns.Count > 1 Then GoTo NAError
If d.Rows.Count > 1 And d.Columns.Count > 1 Then GoTo NAError
If c.Rows.Count > 1 And c.Columns.Count > 1 Then GoTo NAError

'same size arrays
Select Case v.Count
Case Is <> d.Count, Is <> c.Count
GoTo NAError
End Select

j = 0
For i = 1 To v.Count
If c(i, 1).Value = DesiredCompany Then
ReDim Preserve vv(j)
ReDim Preserve dd(j)
vv(j) = v(i).Value
dd(j) = d(i).Value
j = j + 1
End If
Next i

'if Excel 2007
spXIRR = WorksheetFunction.Xirr(vv, dd, g)

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.
'Then use this line instead:

'spXIRR = XIRR(vv,dd,g)

Exit Function

NAError:
spXIRR = CVErr(xlErrNA)
End Function
====================================
--ron

 

[Marc]

Ron, thanks for your post. Thanks to others for trying to help me as well. My
file has a lot more columns - I took the unnecessary ones out. I guess Ron's
solution works well!

My next question: i guess i cannot do something like this in a google
spreadsheet!

 

[Joe User]

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.

Those instructions did not work for me.

I had to click Browse in Tools > References, wend my way down the path
\Program Files\Microsoft Office\OFFICE11\Library\Analysis, and select
atpvbaen.xla.

(I had found atpvbaen.xla by doing a file search of the local drive.)

Once selected, yes, atpvbaen.xls appears in the Tools > References list.

But it only seems to be in that list for the workbook that I was using when
I selected atpvbaen.xla. If I simply run Excel and press alt+F11 to open
the VBA window, I do not find atpvbaen.xls under Tools > References.

Am I doing something wrong?

I am using Office Excel 2003 Sm Busn Edition (11.5612.5606) and VB 2.3
version 9969 VBA Retail 6.4.8869.


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

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000

How do I calculate returns for Goog and Intc individually, if I sold on
the
21st Nov, and bought on three different dates?

I have a big list of such transactions. I want to calculate returns for
individual stocks.

Does your example above represent three different columns? Or is each
line a
single cell?

If the data is in three separate columns, is it a typo that the last two
lines
are in the order of Date | Company | CashFlow and the first three in the
order
of Date | CashFlow | Company, or is that the way it really is?

Assuming that all of the company names are in the same column, and that
all of
the cash flow amounts are in the same column, you could use a User Defined
Function to calculate this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=spXIRR(Values,Dates,Company_Names,DesiredCompany,[guess})

in some cell.

Values, Dates and Company_Names must all be single column (or single row)
ranges of the same size.

Values = the range (column) in which ALL of the values are found.
Dates = the range (column) in which ALL of the dates are found.
Company_Names = the range (column) in which ALL of the associated company
names
are found

DesiredCompany is obvious.

Guess means the same here as for the XIRR function (see HELP).

In addition, the UDF will be different depending on the version of Excel
you
have. In 2007, MS made XIRR part of the worksheetfunction group. Before
that,
it was part of the analysis toolpak. So how it gets called will differ --
see
the comments within the UDF for instructions.

============================================
Option Explicit
Option Compare Text
Option Base 0
Function spXIRR(v As Range, d As Range, c As Range, _
DesiredCompany As String, Optional g As Double = 0.1) As Double
Dim vv(), dd()
Dim i As Long, j As Long
Dim r As Range

ReDim vv(0)
ReDim dd(0)
j = 0
For i = 1 To v.Count
If c(i, 1).Value = DesiredCompany Then
ReDim Preserve vv(j)
ReDim Preserve dd(j)
vv(j) = v(i).Value
dd(j) = d(i).Value
j = j + 1
End If
Next i

'if Excel 2007
spXIRR = WorksheetFunction.Xirr(vv, dd, g)

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.
'Then use this line instead:

'spXIRR = XIRR(vv,dd,g)

End Function
=====================================

--ron

 

[Ron Rosenfeld]

Ron, thanks for your post. Thanks to others for trying to help me as well. My
file has a lot more columns - I took the unnecessary ones out. I guess Ron's
solution works well!

I'm glad to hear that my solution is working for you. Thanks for the feedback.

My next question: i guess i cannot do something like this in a google
spreadsheet!

Probably be more appropriate to address that question to a NG that deals with
google spreadsheet. I can't answer that.
--ron

 

[Ron Rosenfeld]

Those instructions did not work for me.

Is it only that part of the instructions that did not work? Were you able to
use XIRR as a VBA function?

I had to click Browse in Tools > References, wend my way down the path
\Program Files\Microsoft Office\OFFICE11\Library\Analysis, and select
atpvbaen.xla.

(I had found atpvbaen.xla by doing a file search of the local drive.)

Interesting. It's listed in my "Reference list" as written, and was also
listed that way in previous versions of Excel. Perhaps it has something to do
with my having added Analysis ToolPak - VBA as an Add-in in the Excel
application.

Once selected, yes, atpvbaen.xls appears in the Tools > References list.

But it only seems to be in that list for the workbook that I was using when
I selected atpvbaen.xla. If I simply run Excel and press alt+F11 to open
the VBA window, I do not find atpvbaen.xls under Tools > References.

Am I doing something wrong?

I don't think so. I don't know how to change the set of references that is
normally added when a new instance of Excel is started. And, assuming you are
using the code for a particular WB, it should not be that much of a limitation.
However, there are programmatic methods for adding explicit references
available on the web. Here's one:
http://www.ozgrid.com/forum/showthread.php?t=56538

I've not gotten particularly involved in that aspect of Excel VBA, however, so
really can't comment on it's effectiveness or usefulness.

I am using Office Excel 2003 Sm Busn Edition (11.5612.5606) and VB 2.3
version 9969 VBA Retail 6.4.8869.


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

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000

How do I calculate returns for Goog and Intc individually, if I sold on
the
21st Nov, and bought on three different dates?

I have a big list of such transactions. I want to calculate returns for
individual stocks.

Does your example above represent three different columns? Or is each
line a
single cell?

If the data is in three separate columns, is it a typo that the last two
lines
are in the order of Date | Company | CashFlow and the first three in the
order
of Date | CashFlow | Company, or is that the way it really is?

Assuming that all of the company names are in the same column, and that
all of
the cash flow amounts are in the same column, you could use a User Defined
Function to calculate this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=spXIRR(Values,Dates,Company_Names,DesiredCompany,[guess})

in some cell.

Values, Dates and Company_Names must all be single column (or single row)
ranges of the same size.

Values = the range (column) in which ALL of the values are found.
Dates = the range (column) in which ALL of the dates are found.
Company_Names = the range (column) in which ALL of the associated company
names
are found

DesiredCompany is obvious.

Guess means the same here as for the XIRR function (see HELP).

In addition, the UDF will be different depending on the version of Excel
you
have. In 2007, MS made XIRR part of the worksheetfunction group. Before
that,
it was part of the analysis toolpak. So how it gets called will differ --
see
the comments within the UDF for instructions.

============================================
Option Explicit
Option Compare Text
Option Base 0
Function spXIRR(v As Range, d As Range, c As Range, _
DesiredCompany As String, Optional g As Double = 0.1) As Double
Dim vv(), dd()
Dim i As Long, j As Long
Dim r As Range

ReDim vv(0)
ReDim dd(0)
j = 0
For i = 1 To v.Count
If c(i, 1).Value = DesiredCompany Then
ReDim Preserve vv(j)
ReDim Preserve dd(j)
vv(j) = v(i).Value
dd(j) = d(i).Value
j = j + 1
End If
Next i

'if Excel 2007
spXIRR = WorksheetFunction.Xirr(vv, dd, g)

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.
'Then use this line instead:

'spXIRR = XIRR(vv,dd,g)

End Function
=====================================

--ron

--ron

 

[Joe User]

Is it only that part of the instructions that did not work?
Were you able to use XIRR as a VBA function?

Yes and yes.

But oddly, the XIRR function does not work (returns #VALUE) until I save and
reopen the workbook. I've tried all sorts of things to work around that,
e.g. Run > Reset, to no avail.

(That is only the first time after adding atpvbaen.xls to the References
list using Browse.)

Also, if I deselect atpvbaen.xls in the References list and save the
workbook, when I reopen the workbook, atpvbaen.xls is no longer in the
References list. I have to find it again using Browse :-(.

But aha!....

Perhaps it has something to do with my having added
Analysis ToolPak - VBA as an Add-in in the Excel
application.

Yup! That made all the difference. Then ATPVBAEN.XLA appears in the
VBAProject list; and that is retained from one instance of Excel to another.

Still need to explicitly select atpvbaen.xls from the References list in
order to call XIRR from VB code. But the #VALUE error is no longer
apparent.

However, there are programmatic methods for adding
explicit references available on the web. Here's one:
http://www.ozgrid.com/forum/showthread.php?t=56538

Thanks for the pointer. I'll experiment with that later.

Well, I was going to start another thread to discuss this off-topic issue.
But I think you just saved me the trouble. Thanks.


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

Those instructions did not work for me.

Is it only that part of the instructions that did not work? Were you able
to
use XIRR as a VBA function?

I had to click Browse in Tools > References, wend my way down the path
\Program Files\Microsoft Office\OFFICE11\Library\Analysis, and select
atpvbaen.xla.

(I had found atpvbaen.xla by doing a file search of the local drive.)

Interesting. It's listed in my "Reference list" as written, and was also
listed that way in previous versions of Excel. Perhaps it has something
to do
with my having added Analysis ToolPak - VBA as an Add-in in the Excel
application.

Once selected, yes, atpvbaen.xls appears in the Tools > References list.

But it only seems to be in that list for the workbook that I was using
when
I selected atpvbaen.xla. If I simply run Excel and press alt+F11 to open
the VBA window, I do not find atpvbaen.xls under Tools > References.

Am I doing something wrong?

I don't think so. I don't know how to change the set of references that
is
normally added when a new instance of Excel is started. And, assuming you
are
using the code for a particular WB, it should not be that much of a
limitation.
However, there are programmatic methods for adding explicit references
available on the web. Here's one:
http://www.ozgrid.com/forum/showthread.php?t=56538

I've not gotten particularly involved in that aspect of Excel VBA,
however, so
really can't comment on it's effectiveness or usefulness.

I am using Office Excel 2003 Sm Busn Edition (11.5612.5606) and VB 2.3
version 9969 VBA Retail 6.4.8869.


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

On Sun, 22 Nov 2009 08:48:01 -0800, Marc
<[email protected]>
wrote:

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000

How do I calculate returns for Goog and Intc individually, if I sold on
the
21st Nov, and bought on three different dates?

I have a big list of such transactions. I want to calculate returns for
individual stocks.

Does your example above represent three different columns? Or is each
line a
single cell?

If the data is in three separate columns, is it a typo that the last two
lines
are in the order of Date | Company | CashFlow and the first three in the
order
of Date | CashFlow | Company, or is that the way it really is?

Assuming that all of the company names are in the same column, and that
all of
the cash flow amounts are in the same column, you could use a User
Defined
Function to calculate this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=spXIRR(Values,Dates,Company_Names,DesiredCompany,[guess})

in some cell.

Values, Dates and Company_Names must all be single column (or single
row)
ranges of the same size.

Values = the range (column) in which ALL of the values are found.
Dates = the range (column) in which ALL of the dates are found.
Company_Names = the range (column) in which ALL of the associated
company
names
are found

DesiredCompany is obvious.

Guess means the same here as for the XIRR function (see HELP).

In addition, the UDF will be different depending on the version of Excel
you
have. In 2007, MS made XIRR part of the worksheetfunction group.
Before
that,
it was part of the analysis toolpak. So how it gets called will
differ --
see
the comments within the UDF for instructions.

============================================
Option Explicit
Option Compare Text
Option Base 0
Function spXIRR(v As Range, d As Range, c As Range, _
DesiredCompany As String, Optional g As Double = 0.1) As Double
Dim vv(), dd()
Dim i As Long, j As Long
Dim r As Range

ReDim vv(0)
ReDim dd(0)
j = 0
For i = 1 To v.Count
If c(i, 1).Value = DesiredCompany Then
ReDim Preserve vv(j)
ReDim Preserve dd(j)
vv(j) = v(i).Value
dd(j) = d(i).Value
j = j + 1
End If
Next i

'if Excel 2007
spXIRR = WorksheetFunction.Xirr(vv, dd, g)

'if prior to Excel 2007
'you must set an EXPLICIT reference to atpvbaen.xls
' from the main menu above, select Tools/References
' then select atpvbaen.xls.
'Then use this line instead:

'spXIRR = XIRR(vv,dd,g)

End Function
=====================================

--ron

--ron

 

[Ron Rosenfeld]

But aha!....



Yup! That made all the difference. Then ATPVBAEN.XLA appears in the
VBAProject list; and that is retained from one instance of Excel to another.

Still need to explicitly select atpvbaen.xls from the References list in
order to call XIRR from VB code. But the #VALUE error is no longer
apparent.




Thanks for the pointer. I'll experiment with that later.

Well, I was going to start another thread to discuss this off-topic issue.
But I think you just saved me the trouble. Thanks.

Glad to help.

Not having 2003, I sometimes don't recall all the nuances. One thing I do seem
to recall, which is pertinent (and unfortunate) in this situation, is that I
don't think you can use late-binding with atpvbaen. And, of course, if you are
going to write something that will work in both 2007+ and earlier versions, the
code is different.
--ron