Trailing stops

[Guest]

I am having a hard time figuring out how to do the following. I want to
create a spreadsheet that calculates a trailing stop for a stock. The
premise is that I want to sell the security if the price drops by a certain
user entered percentage. I have the excel add-in "Stock Quotes". So
basically what I am having trouble with is I cannot get my spreadsheet to
capture the highest latest price. See below for an example of a 15% trailing
stop:

Purchase Price Current Price Highwater Mark Trailing Stop
100.00 101.50 103.00 87.55

The problem is that I cannot figure out how to keep the highwater mark
stable even if the current price drops. What I want is if the current price
is higher than the highwater mark, then a new highwater mark is established
and the trailing stop is then 85% of that highwater mark.

Can anyone help!!!!!

 

[T. Valko]

You can do this using a circular reference but there is a potential problem.

Assume:

B2 = Current Price
C2 = Highwater Mark

Select cell C2
Goto Tools>Options>Calculation>Iteration>OK

Now, enter this formula in C2:

=MAX(B2,C2)

C2 will now retain the highest value.......but.........

If there is an error made while entering a new value in cell B2 there is no
way to recover from that. You're done!

For example, you enter 1025 in B2 but you meant to enter 102.5.

To reset the formula and start over you need to clear cell B2 (or enter 0)
and then re-register the formula in C2 by selecting cell C2 and either
double clicking and hitting ENTER or hitting function key F2 then ENTER.

Biff