Use Excel to Backtest a Trading Strategy using an ATR Stop-loss

Last Updated on April 14, 2021 by Mark Ursell

This post continues the series of video articles about how to use Microsoft Excel to backtest trading strategies.  In this post I show how to calculate a stop-loss using the ATR and then how to backtest the trading strategy.

For the previous video articles see, An Easy Way to use Excel to Backtest a Trading Strategy – Part 2.

The Average True Range

Developed by J. Welles Wilder the ATR is very popular with traders. On its own, the ATR can be used to measure market volatility and market range. It is also frequently used in other technical indicators such as the SuperTrend indicator and the ADX.

One of the most popular uses for the ATR was developed by Chuck LeBeau and is referred to as the Chandelier Exit. The chandelier exit sets the stop-loss distance as a multiple of the ATR. The ATR reacts to market conditions so when things are calm, the stop-loss will be relatively close and when things are volatile, the stop-loss will be further away.

Formulas Used:

H-L = High-Low
H-PC = abs(High-Previous Close)
L-PC = abs(Low-Previous Close)
True Range =max(range)
ATR =average(range)
SL =ATRFactor Max Weekly Drawdown = Low-Previous Close Trading Strategy =IF(F34>G34,IF(N35>M34,((F34-M34)/F34)Q34,(F35/F34)*Q34),Q34)