P & L Calculation

[RobbieBobbie23]

Hello,
I need help creating a profit/loss spreadsheet as I trade futures.
This is the data I have and I need excel to calculate the 2 columns on the right.
DATE BUY/SELL CONTRACTS PRICE NET POSITION NET PRICE
1/1/07 SELL -5 15.00 -5 15.00
1/2/07 SELL -10 16.50 -15 16.00


As you can see I'm now short 15 contracts at a price of 16.00. I need the spreadsheet to show my net position as well as average cost. If I decide to buy 25 contracts I would be closing out my original short of 15 contracts and need to show my profit/loss on those 15 contracts as well as the new price on the 10 contacts I am now long. It will not show a profit/loss

DATE BUY/SELL CONTRACTS PRICE NET POSITION NET PRICE
1/3/07 BUY 25 20.00 10.00 20.00

Since the original position of -15 is now closed I would need to show the profit/loss for the sell/buy. Then I would show my new long position of 10 contacts at 20.00. Let me know if you need any additional information. I would like to get this set as soon as possible.
Thanks,

[RobbieBobbie23]

Can I Please Get Some Help?

[Conor]

I doubt that many of us on the forum are futures traders. How do you calculate gain/loss on the transactions? Is it all or nothing on each contract? Do the contracts ever expire or can they only be bought or sold? What would your situation be if you had bought only 10 rather than 25?

And don't use an angry face icon when you are asking for help. No one here HAS to answer you.

[RobbieBobbie23]

Thanks for your response.

A gain/loss would only be calculated once the position is closed. If I buy 10 contracts at 100 and then buy 5 contracts at 110 later on, I would be long 15contracts at average price of 103.333.
(10 * 100 + 5 * 110 / 15).

If I decide to sell 5 of these contracts at 95, I would still be long 10 contracts at 103.333. However, I now have a realized loss that I need to calculate on the 5 contracts I originally bought with at an average price of 103.333. This would be calculated as
(103.333 - 95.00 * 5 contracts = Loss of 41.665)

So basically, there will only be a gain/loss recorded when you are long say 10 contracts and sell 5 contracts OR you are short 10 contracts and buy 5 contracts.

The average cost would change if you are long 10 contracts and buy 5 contracts OR you are short 10 contracts and sell 5 contracts.

Please let me know if I am not making any sense.

[Conor]

So, then in your original scenario, the buy of 25 really closed out and eliminated the first 15 sells at $20 each giving you a loss of $5 on the first 5 and a loss of $3.50 on 10 more. Is that correct?

So the entire position, is always a single net of all the open buys and sells? I.E., if you have 2 buys of 10 for $10 each, 3 sells of 5 for $5 each, and 2 more buys 10 for $10 each, you still have only a single net position of contracts at an average price of all those not yet closed out? Is that correct? So, in this case, you would have 25 contracts at an avg of $10 each and a net loss on 15 contracts of $5 each. Correct?

[RobbieBobbie23]

In the original scenario (The first line is the buy/sell; the second line is the net position/price)

Sell 5 @ $15.00
-5 @ $15.00

Sell 10 @ $16.50
-15 @ $16.00

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 position)