return

  • Thread starter Thread starter Dave
  • Start date Start date
D

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?
 
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?
 
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.
 
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
 
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?
 
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)
 
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.,
 
Hi
but if you have several B and S transactions how do you know which S
transaction belongs to which B transaction?
 
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.
 
Back
Top