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.

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marc" wrote in 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.

"Marc" wrote:
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 -----

"Marc" wrote:
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(D1:D2, E1:D2)

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(D3:D5, E3:E5)

Format the XIRR cell as Percentage.


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

"Marc" wrote in 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

I have the above data. I want to do a xirr for b1 and b6 with a1 and a6
I also want to the XIRR for b2, b3, c7 and the corresponding dates.

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

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" wrote in 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.

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" wrote:

"Marc" wrote:
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 -----

"Marc" wrote:
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(D1:D2, E1:D2)

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(D3:D5, E3:E5)

Format the XIRR cell as Percentage.


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

"Marc" wrote in 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

I have the above data. I want to do a xirr for b1 and b6 with a1 and a6
I also want to the XIRR for b2, b3, c7 and the corresponding dates.

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

.

"Marc" wrote:
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 -----

"Marc" wrote in message
...
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" wrote:

"Marc" wrote:
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 -----

"Marc" wrote:
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(D1:D2, E1:D2)

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(D3:D5, E3:E5)

Format the XIRR cell as Percentage.


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

"Marc" wrote in 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

I have the above data. I want to do a xirr for b1 and b6 with a1 and a6
I also want to the XIRR for b2, b3, c7 and the corresponding dates.

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

.

PS....

"Marc" wrote:
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 -----

"Marc" wrote in message
...
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" wrote:

"Marc" wrote:
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 -----

"Marc" wrote:
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(D1:D2, E1:D2)

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(D3:D5, E3:E5)

Format the XIRR cell as Percentage.


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

"Marc" wrote in 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

I have the above data. I want to do a xirr for b1 and b6 with a1 and a6
I also want to the XIRR for b2, b3, c7 and the corresponding dates.

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

.

On Sun, 22 Nov 2009 12:32:08 -0800, "Joe User" <joeu2004 wrote:

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

On Sun, 22 Nov 2009 21:28:04 -0800, Marc
wrote:

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

On Mon, 23 Nov 2009 12:31:34 -0800, "Joe User" <joeu2004 wrote:

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.

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