Your problem is similar to calculating return on a typical investment. If
you've invested $10,000 over various periods of time, and it's currently
worth $15,000, what's your ROI? While XIRR will do the calculations, they
can be done only if you assume the investment is sold today. In other words,
the final entry is today's date and the value of the investment.

Your problem is the same. Even if you haven't sold all of the shares, the
only way to do the calculation is to assume you did. So your final entry has
to be an assumed sell for the remaining balance (or an assumed buy to cover
a short sale).

Hope his helps,
Fred.

"Dallman Ross" <dman@localhost. wrote in message
...
Gentle people,

I have come up against a problem doing some Excel
time/cash-flow calculations and could really use some help
from the "brain trust" here.

It's a bit complicated, but let me try to break it down.
First I'll summarize the goal and the problem:

The goal is to take data on stock purchases and sales and
find the average time held per trade.

I have what seems to be a good partial solution I
concocted. The problem I'm having is reconciling unmatched
buys and sells, by which I mean share imbalances that occur
during periods of stock accumulation or divesting
(overselling, or shorting).

Here is an easy example of what I am trying to do. Suppose
I trade a stock as follows in a 3-month period. (Note that
a positive number in the Shares and $Traded columns implies
a buy. A negative number implies a sale.) [Best viewed
with a non-proportional font such as Courier.]

Symbol: Date: Shares: Price: $Traded: Commission:
MSFT 1/1/08 100 29.90 2990 -10
MSFT 1/15/08 -100 29.40 -2940 -10
MSFT 2/1/08 100 28.90 2890 -10
MSFT 2/15/08 -100 29.65 -2965 -10
MSFT 3/1/08 100 27.90 2790 -10
MSFT 3/15/08 -100 28.15 -2815 -10

Here's what I did: separately for buys and sales, I took a
weighted average of dollars traded and dates, and then
divided by dollars traded to leave me with a weighted
"average buy-date" and "average sell date." Then I
subtracted one from the other to see the average time held.
I used SUMPRODUCT to do this. I have named ranges for the
columns. We can use the header names for those in the
explanation here.

So using the example data, I have for the "average purchase
date":

[ (gross dollars from buys x dates) - (commissions x dates) /
(gross dollars from buys - commissions) ]

=(SUMPRODUCT(--(colTraded0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded0),colTraded)- _
SUMPRODUCT(--(colTraded0),colComm))

(The formula is, however, of course on one line without the
"_" continuation marks.)

And I have for the "average sale date" the exact same
thing, except that the now becomes a <.

The dollar-weighted "average purchase date" via the
formula, formatted as a date, is 1/30/08. And looking at
the dates of purchases in the table above, that seems
correct: I bought on the first of the month for three
months, and the price paid was not very different each
time. Since I started the table with 1/1/08, well, on
average I do seem to have bought about 30 days after the
period being considered began.

The "average sale date" comes out to 2/13/08. That also
seems intuitively correct based on the data shown in my
table.

So I have an "average days held" of 2/13/08 - 1/30/08, or
14.265 days when I work it out in Excel. Great! That's
just what I wanted to know: how long am I holding each
trade on average?

Now we finally come to my big problem.

What if I never made the final sale? Now I've bought 300
shares but only sold 200. (In case it helps, I do have a
column showing total accumulation of shares. It can also
go negative.)

Well, if I remove the final sale from my example table and
apply my formula, Excel give an "average time held" of -
0.078 days! What can I do to offset the share discrepancy?
In other words, HELP! :-)

=dman=