This post shows how an Excel backtest model can be used to calculate a trailing stop-loss for a trading strategy.
You can now purchase spreadsheets directly from my website: Trading Spreadsheets
Trailing stops are popular among many different types of traders. The key aspect of them is to try to capture as much of a market move as possible.
Turtle Traders used a volatility system to measure the distance of the trailing stop and moved their stops when the markets moved in their favour.
Counter-trend traders will also make use of trailing stops when they buy at market extremes, and trail their stops to breakeven.
Calculating the Stop-Loss
I like to calculate stop-losses and profit targets based on the ATR. The ATR is a measure of the recent trading range. The calculation of the ATR is explained in my article, Backtest a Trading Strategy using an ATR Stop-Loss. The trailing stop-loss distance is measured by multiplying the ATR by the factor. This factor can be manually adjusted and optimised by backtesting.
The trailing stop-loss for a long position is measured by subtracting the trailing stop-loss distance from the high point of the previous period. If this distance is higher than the existing stop-loss.
This method was described by Alexander Elder in his book, Come into My Trading Room. In his book, Dr Elder describes this type of trailing stop-loss as the Chandelier Exit.
The video provides a step-by-step guide to how to amend a long-only backtest model. The backtest model shown in the video is based on the Ebook course, How to Backtest a Trading Strategy Using Excel.
The SL Price is adjusted to include an IF AND statement that checks whether the stop-loss level should be moved. We also include a check to ensure that the stop-loss is never exactly the same as the entry price.
Cell Y60 =IF(T60=”long”,W60-(N60*$Y$3),IF(AND($Y$2>0,AB60=$AB$2,T59<>”long”,D59-($Y$2*N60)>Y59),IF(D59-($Y$2*N60)=W60,D59-($Y$2*N60)+0.01,D59-($Y$2*N60)),Y59))
The SL Loss is adjusted to add an if statement that checks whether the stop-loss has changed and if so to adjust the potential loss accordingly.
Cell AA60 =IF(T60=”long”,-AF59*$AA$3,((W60-Y60)/(W59-Y59))*AA59)