Trailing stops

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lyelve - 06 Feb 2007 19:15 GMT

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!!!!!

Signature

lyelve

Reply to this Message

T. Valko - 07 Feb 2007 03:03 GMT

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

>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
[quoted text clipped - 17 lines]
>
> Can anyone help!!!!!

Reply to this Message