P & L Calculation Help Needed

[robert.kennedy]

Hello,
I'm trying to put together a p & l spreadsheet which shows buys and
sells of futures. With futures; I can buy and go long or sell and go
short. I can't seem to figure out how to get the average cost basis or
gain and loss. Here is an example. (The first line is the buy/sell;
the second line is the net position/price)

Sell 5 @ $15.00
-5 @ $15.00 Net position and Net Price

Sell 10 @ $16.50
-15 @ $16.00 New net position and New net price

Buy 25 @ $20.00 (loss of $4.00 per contracts on 15 contracts I was
short; those 15 contracts are now closed)
+10 @ 20.00 (my new net position and new net price for being long)

I'm not sure if I need to use vba for this or not, but I would
appreciate some help as soon as possible. Please let me know if you
have any questions.

 

[Steve G]

Hello,
I'm trying to put together a p & l spreadsheet which shows buys and
sells of futures. With futures; I can buy and go long or sell and go
short. I can't seem to figure out how to get the average cost basis or
gain and loss. Here is an example. (The first line is the buy/sell;
the second line is the net position/price)

Sell 5 @ $15.00
-5 @ $15.00 Net position and Net Price

Sell 10 @ $16.50
-15 @ $16.00 New net position and New net price

Buy 25 @ $20.00 (loss of $4.00 per contracts on 15 contracts I was
short; those 15 contracts are now closed)
+10 @ 20.00 (my new net position and new net price for being long)

I'm not sure if I need to use vba for this or not, but I would
appreciate some help as soon as possible. Please let me know if you
have any questions.

I believe you have to decide your rules for a cost basis--FIFO, LIFO,
or some other basis. After that youdetermine your cost on each
particlar share. You can prepare a table of the number of shares at
each cost basis.

Then when you sell the shares, using LIFO, FIFO, or some other rule
such as average cost basis, you assign the new cost basis to the
remaining shares.

Steve G