Method needed to calculate sale profit/loss

[h1981]

Hi,

I will be grateful if anyone can help me with this.
I have daily stock transaction (buy and sell) data for several investors. I need to calculate loss/gain of each sale for each investor. I have attched the worksheet has been attached which contains data for two investors.
That is, i need to calculate (profit/ loss = Sale value - Cost of Sale)

Investor is identified by the ACC column. Cost of sale is calculated on a LIFO basis. (Last in First Out method), which means the ones that bought last will be first sold.

I will provide a simple demonstration of what i need (this is for one investor).

Date--------------buy price-------buy volume------sell price------sell volume----profit
01/01/10----------10.00--------------10
02/01/10----------15.00--------------20-------------------17.00-------------15----------(17-15)*15
03/01/10-------------------------------------------------------18.00-------------10------(18-15)*5+(18-15)*5

If i explain this, by 02/01/10 the total stock investor has is, ($10*10) and ($15*20). when 15stocks are sold on that day, out of the total stock, we take 15 stocks at the base price of $15, as it is the latest price. 5 stocks left in that stock (bought at $15) after the sale and on 03/01/10sale transaction, it will be gone first and then we consider the older stocks (which is at $10).
Pls pls help me

Thank You so much

[Marcol]

Would this workbook be of help?

By using SUBTOTAL() and filters on Columns A:G you can query the table.

e.g. Filter Column C ("ACC") for the required client(s) and Column A ("trans_date") for the required transaction date(s), etc.

Conditional formatting is applied to Cell L2 to highlight Profit/Loss.

Adjust the sub-totalled columns to suit your needs, this is only to demonstrate the method, and is not necessarily a solution.

Hope this helps.

P.S.
Your profile indicates you are using 2003 but the file you supplied is 2007 or later.

Do you need a solution that works in 2003?

[h1981]

Sorry couldnt attend to this for a while. I am sorry, but what i need is something different. I have attached the sheet which demonstrate what i need. it should be a profit calculation based on the stocks we have and we should be able to record that on each day transaction occurs. The highlighted columns are what i need to calculate. What i feel is, i have to start from the last transaction of each investor and work through to tthe first date. Please let me know if it doen't make any sense.

[h1981]

bump no response

[Marcol]

Hmm?

I cannot follow your logic.

1/. The first part is easy to match with your sample
Use a helper column "Helper1 (Last Buy Price)"
In G2

Please Login or Register  to view this content.

In H2 "buy"

Please Login or Register  to view this content.

Drag/Fill Both Down.
This rightly or wrongly matches your Column "buy" (now Column I in this sample workbook)

2/. Because you intend to sell the stocks on a "last in first out basis" then "Helper2 (Last Buy Date)" might be used to return only the dates when stocks were bought.
In J2

Please Login or Register  to view this content.

Drag/Fill Down.

So far so good.

3/. Your formulae/calculations in Column "sell" make no sense whatsoever, to me at least.
The first calculation you have in this column is in row 4

Please Login or Register  to view this content.

Where does the 100 and 900 come from?
Prior to this you seem to have bought and sold equal amounts on each day, so therefore you are holding no stock at this time. Is that not correct?

From that point on I really can't follow anything!

4/. I have added some thoughts to the sheet, Columns N:Q, based on the last bought price.
This is only an attempt to follow your logic and will not hold true as it stands when the next client is reached.

Sorry I just don't understand how you can sell stocks you don't have then buy them back at the same price the following day... ... See rows 13/14. (Prior to row 13 your stock balance is zero.) That seems to be a nifty way to make $4365 ...

Maybe this is why I am not an accountant/stock broker.

[Marcol]

bump no reply!

This is an entirely voluntary service, please have some patience and courtesy.