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
(E-Mail Removed)
"Marc" <(E-Mail Removed)> wrote in message
news:5C37BC7D-A041-4F7C-AD46-(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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(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 -----

"Marc" <(E-Mail Removed)> wrote in message
news:6738C80D-4F90-44EE-91D7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:5C37BC7D-A041-4F7C-AD46-(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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(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 -----
>
> "Marc" <(E-Mail Removed)> wrote in message
> news:6738C80D-4F90-44EE-91D7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:2C49D771-9ABF-4475-B28D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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(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 -----
>>
>> "Marc" <(E-Mail Removed)> wrote in message
>> news:6738C80D-4F90-44EE-91D7-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:2C49D771-9ABF-4475-B28D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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(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 -----
>>
>> "Marc" <(E-Mail Removed)> wrote in message
>> news:6738C80D-4F90-44EE-91D7-(E-Mail Removed)...
>> > 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 08:48:01 -0800, Marc <(E-Mail Removed)>
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

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