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

.