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?

.