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.