I need an MS Excel formula that backtests a set of prices and computes
loss/gain using specific price stops.

I have downloaded historical stock prices into an Excel spreadsheet. I
need a formula that computes the potential gain or loss for a stock
using specific price stops based on an initial entry point. For
instance, if I buy IBM @ 100, using a price stop of +/- 5%, I want the
formula to compute whether the position realized a gain or loss over a
specific time period using end of day price data. Ideally the formula
will identify and compute the gain/loss based on the first price stop
during a given time period. For example, with an entry price of 100,
if IBM hits 105 at the beginning of a time period, and falls to 95
later in the time period, the formula should recognize the first price
stop – at a profit of 5%. Ideally, I would also like the formula to
compute a series of pre-defined stop prices, let’s say sell half the
position at a stop of +/- 5%, and the other half when the price hits
+/- 10%. Thank you.


--
ExcelKat
------------------------------------------------------------------------
ExcelKat's Profile: http://www.excelforum.com/member.php...o&userid=35169
View this thread: http://www.excelforum.com/showthread...hreadid=549384

=IF(INDEX(A1:A365,SMALL(IF(ABS(A1:A365-StartPricesheet!A1)=5,ROW(A1:A365)),1),1)-StartPricesheet!A10,"gain","Loss")

entered with Ctrl+shift+enter

should get you started.

--
Regards,
Tom Ogilvy


"ExcelKat" wrote:


I need an MS Excel formula that backtests a set of prices and computes
loss/gain using specific price stops.

I have downloaded historical stock prices into an Excel spreadsheet. I
need a formula that computes the potential gain or loss for a stock
using specific price stops based on an initial entry point. For
instance, if I buy IBM @ 100, using a price stop of +/- 5%, I want the
formula to compute whether the position realized a gain or loss over a
specific time period using end of day price data. Ideally the formula
will identify and compute the gain/loss based on the first price stop
during a given time period. For example, with an entry price of 100,
if IBM hits 105 at the beginning of a time period, and falls to 95
later in the time period, the formula should recognize the first price
stop €“ at a profit of 5%. Ideally, I would also like the formula to
compute a series of pre-defined stop prices, lets say sell half the
position at a stop of +/- 5%, and the other half when the price hits
+/- 10%. Thank you.


--
ExcelKat
------------------------------------------------------------------------
ExcelKat's Profile: http://www.excelforum.com/member.php...o&userid=35169
View this thread: http://www.excelforum.com/showthread...hreadid=549384