return

[Dave]

Hi,

I have a spreadsheet with my purchases and sales. I have
used the IF statement to get either a P or a S in the
colomn. But now I want to be able to calculate the return
from P to S (ie if I buy at 100 and sold at 125 then I
made a 25% return). The data is sorted by date. But the
point is to get excel to find the P and then the S and
then give me the return but the P and S can occur at
anytime along the time line.

Any clues?

 

[Frank Kabel]

Hi Dave
not quite sure what you're trying to achieve. Can you post (as plain
text please) some example rows and you expected result?

 

[Dave]

A B
Jan Buy 1000
Feb
Mar
Apr Sell 1200
...
...
...
... Buy 3000
... Sell 3500
...
....
... Buy 10
Jan
Feb Sell 15
Mar

What I have for above table are three returns. But is
there a way of getting a formula to work out the returns.

I had the same problem with a stock market spreadsheet
where by a model would say buy and then say sell and then
say buy again and so on and I just wanted to calculate the
return from this strategy.

Thanks.

 

[Frank Kabel]

Hi Dave
still not sure what you're trying to achieve. Just as a guest try
=SUMIF(B1:B100,"Sell",C1:C100)-SUMIF(B1:B100,"Buy",C1:C100)

returns the difference of all sell and buy transactions

 

[Dave]

Frank,

Thanks for your reply. Sadly that is not what I want. Let
me have another go at explaining what I am looking for...

Lets say I buy something at time t for B (for Buy) and
then x-days have lapsed and then I sell for S (for Sell)
this at t+x. I have a whole colomn of B's and S's over a
specified time period. All I want is to work out the
return but I cant sum them because the things I buy are
different but by focusing on the price I can bypass this
problem. But then this leaves me with the problem of
having a forumal for workkng out the return for each
purchase and sale.

example

In Jan buy apples for £2000 and then in March sell them
for £2500.

In April I buy pears for £1500 and sell them for £2000.

The return on 1st transaction is 25% and on the second is
33.3%. But supposed I have a whole list of these but
labeled simply B and S. How do I work out the return?

 

[Frank Kabel]

Hi Dave
do you have a column which stores the product/type you buy/cell? If yes
maybe pivot tables could be what you're looking for (but this depends
on your data layout)

 

[Guest]

no it simply says this is what was bought and this is when
it was sold and at what price. because the dates are not
fixed i was simply looking for a way to find a B and then
find an S and then work out the return. and for this to
work down the colomn.,

 

[Frank Kabel]

Hi
but if you have several B and S transactions how do you know which S
transaction belongs to which B transaction?

 

[Guest]

The next B transaction does not occur until an S
transaction has occured. So that I buy stock and do not
buy more stock until I have sold (even if at deep
discount) what I alrady have.