Need some help(Excel risk management)

[heavenskrow]

I created a very simple Excel Spreadsheet that contains, ticker, entry, current price, stop, target, target%, stop%, risk/reward ratio, $loss,$gain,initial investment, as well as current gain/loss.

These formulas that I have made are all assuming I would be playing on the long side.
If I were to have a short position, the current formula gets all messed up.

I was wondering if anyone can give me some simple tips to calculate for both short/long position or if there is already a similar spreadsheet made.
Thanks!

 

[xandman]

Post the formula and explain the inputs. Better yet, post the spreadsheet.

 

[Baron]

You might want to take a look at ExcelTradingModels. They have both free and paid resources available that might help you.

 

[Visaria]

I created a very simple Excel Spreadsheet that contains, ticker, entry, current price, stop, target, target%, stop%, risk/reward ratio, $loss,$gain,initial investment, as well as current gain/loss.

These formulas that I have made are all assuming I would be playing on the long side.
If I were to have a short position, the current formula gets all messed up.

I was wondering if anyone can give me some simple tips to calculate for both short/long position or if there is already a similar spreadsheet made.
Thanks!

I presume you have a column which details how many contracts/ no. of shares you have and that feeds the other columns like profit column? If so, for short positions just put '-' before the number.

 

[heavenskrow]

Here is a sample of the spreadsheet I am using.
https://docs.google.com/spreadsheets/d/1u-ou6-1yGTARMgAtaZargWxqihSX70vDHHCRiAxicP4/edit?usp=sharing

Visaria the negative # did help somewhat in terms of the $ risk, $ initial investment, $gain/loss, and reward$. However the target % , stop%, and trailing stop% are still lop sided.

 

[Visaria]

Yeah, you need to change the forumulae in those columns by way of an IF function, so it inputs a different formula for short positions. Very simple.

For example in cell G4, you would put "=IF(q4<0, -(f4/c4)-1,(f4/c4-1)). All this is saying is if the value in Q4 is negative i.e short, then use this formula, otherwise use the normal formula.