Trailing Stop

Hello I operate a fairly sophisticated excel spreadsheet with over 150 stock positions in it. I am trying to figure out how to build a formula for 25% trailing stops for all stocks.

For each security I have a column for the cost we paid for each and then a column with live feeds coming of current price.

Where I get confused is if a security is worth more than our purchase price I want to 25% trailing stop to increase with it but not retrace if the security falls. Here is an example which should help explain it.


Purchase company x for $10 a share.
First trailing stop is automatically generates a $7.5 stop price because we want a 25% trailing stop.

Now if security goes down 7.5 stays firm.

However, if security rises in value to say $15 the next day

I want trailing stop to automatically change to 11.25 15*.25=3.75 15-3.75=11.25

Then the third day say the security goes back to 11.50 I need my new stop price to stay firm at 11.25 and not retrace with the stock price.

So I get notified that if it hits my new stop price I sell.



Is it possible to automate and build a formula for all of that? Thanks for any help anyone has.

Do you have each day's price in a separate column or row?

Listed like this

Security purchase price Last/current market price



Those are three of about 40 different columns we have going.

Seems to me you need to at least add one more column, for Previous highest market price. Your criteria indicate that if the price goes down, your stop remains firm but if it incresaes, so does your stop. In order to make the logic work, you need to somehow know if the price has gone up or down.

So, if Col A is purchase price, Col B is current price, Col C is previous high, then the formula (for row 5) is: =MAX(B5,C5)*0.75

The Previous High formula is simply: =MAX(B$2:B5), assuming the first entry (which would equal the purchase price) is on Row 2.

Yes I think so. So if I put in a previous high price and change as securities make new highs these formulas should work?

Thanks for all of your help!!!