Stop Loss Backtesting Formula

[ComCool3000]

Hi, I’m trying to figure out a formula that I’m finding a bit tricky. I’m backtesting a trading strategy and currently focusing on optimising a stop loss percentage. I have 8.5 years worth of data and I want to be able to tweak a Stop Loss percentage number to see what would have given optimum results. For the trades that were successful, I want the formula to give me either the Exit price (if the trade was completed) or the Stop Loss price (if the lowest price during the trade fell below the Stop Loss and triggered it.


A = Exit price (if stop is not triggered)
B = Stop Loss (represented as price, not percentage)

C = Lowest price for the duration fo the trade

D = Result : Either A or B depending on whether the Stop loss was triggered by C falling below B


I need a formula that says:

D = A
unless
C falls below B,
in which case
D = B


Example 1:
A (Exit Price) = 100
B (Stop Loss) = 90
C (Lowest) = 98
In this case, D = 100
(lowest price did not go under stop loss, therefore D = A (Exit price)



Example 2:
A (Exit Price) = 100
B (Stop Loss) = 90
C (Lowest) = 85
In this case, D = 90
(lowest price did go under stop loss and triggered it, therefore D = B (Stop loss)



Any ideas greatly appreciated!

 

[DRSteele]

Welcome to the forum.

Is this what you're after?

MrExcel posts18.xlsx
	B	C	D	E	F
2		Trade1	Trade2	Trade3	Trade4
3	Exit	100	100	100	100
4	StopLoss	90	90	90	90
5	Lowest	98	85	90	110
6	result	100	90	100	100
Sheet22

Cell Formulas
Range	Formula
C6:F6	C6	=IF(C5<C4,C4,C3)