Excel "price-stop" formula

E

ExcelKat

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.
 
T

Tom Ogilvy

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

entered with Ctrl+shift+enter

should get you started.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top