How to calculate a P&L in Excel

I have an Excel sheet with O/H/L/C data. I am buying at a close and want to sell only when the close of the next day is higher then the previous close. Otherwise I keep the trade open until there is a higher close coming up. This means I can have a trade open for at least one day, but also for example for five days.

How can I calculate the P&L of every trade?

Thanks.

Hans

 

Anyone who can help?

Hans

 

attachment

 

Thanks, but it's not what I mean. I have attached an example.

Hans

 

not sure whether this is what you need. Try this

Insert three columns between G and H in your sheet (i.e. before the P&L Column)

Add these formulae and copy it down

On cell H2: =IF(G3<>"",0,IF(F3<>"",H3+1,H3))
On cell I2: =E2-E3
On cell J2: =H2*I2
On cell k2: =J2+K3

 

Thanks for your reaction. I have tried it , but is not correct.

Anyone else an idea?

Hans

 

One simple solution attached, using the old reliable Vlookup function.

Since you can have multiple trades exited at the close of a single day, yet want to track each trade's P&L separately, I'd recommend having as many Exit and P&L columns as the largest possible number of simultaneous positions you'll ever expect to hold (here 3).

Also, having your Exits (columns G-I) entered manually, rather than calculated, generally isn't the optimal or fastest way to handle it, for larger data sets... but if it works for you, sure, why not. I preserved as much of your original spreadsheet as possible. (Exiting Trade 2, but not Trade 1, on Jan. 8th implies that you're using other rules besides a higher close.)

 

The columns F and G were only to explain what I mean. All columns should be calculated in Excel. Column F is no problem. Main problem is to calculate the exit and the P&L.

Any idea?

Hans

 

Hans, example1.xls already calculates P&L automatically.

To calculate exits (yes, a good idea), you'd still need to specify what the exit rules are, consistent with your actual exits. See my earlier comment regarding trades 1 and 2.