How to Calculate a Trailing Stop-Loss Using Excel

Last Updated on March 8, 2021 by Mark Ursell

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 Stop-Losses

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.

Video

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. https://www.tradinformed.com/wp-admin/post.php?post=2728&action=edit

Formulas Used

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$2N60)>Y59),IF(D59-($Y$2N60)=W60,D59-($Y$2N60)+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)