# ATR Stop Loss Excel Free Calculator

In this article, we’ll show you how to create and use your Average True Range ATR Stop Loss Calculator, and you’ll learn how to create a simple calculator with Excel (free to download).

Average True Range (ATR) indicator calculates the average candles ranges over a specified period; its peculiarity is that it also considers gaps.

Contents

The Average True Range is the most used indicator for the calculation of historical volatility. It is present in all trading platforms, but sometimes it is necessary to calculate the stop loss on particular financial instruments.

Many use our table to calculate the stop loss on long-term investments made with their bank, which cannot be charted in a trading platform.
Also, many trading platforms provide the average true range but not an automatic stop loss calculator.
It can be advantageous to use Excel and manually enter the easily available ATR data in these cases. It is also possible to calculate and chart the Average True Range in Excel.

The most important thing is that adapting the stop loss to the market’s volatility is always a good idea, both when we are doing discretionary trading and building an automated trading system.

## Why use Average True Range Stop Loss

Everywhere you will find someone who recommends you to use stoploss when trading, but few explain how to use it.

Misuse of stoploss can be much more dangerous than not using it at all. Why should we use the stop loss? Because it should help limit losses, but if misused, it only increases and accumulates losses.

Many traders use the stop loss or the trailing stop to increase their exposure to the market, and the more they increase the size of their trade, the more they decrease the stop loss.

We have not yet discussed calculating an Average True Range stop loss because we first want to convince you of how essential it is to measure volatility before setting the stop loss.

When you decide to open your trade, you will need to determine how much money you want to risk in that single financial transaction.

Let’s take an example to understand better; you may want to risk only a part of your capital, suppose 100 \$, there are many questions you will ask yourself, for example, how much leverage to use? How many contracts to buy? But the more important question is: how much volatility is there in the market right now?

The higher the volatility, the more you will have to increase your stop loss distance and, therefore, keep your position smaller.

If you use a stoploss that is too small compared to the market volatility, you will be continually hit by the price, and you will accumulate a lot of losses. Furthermore, not all trading strategies need the same stoploss; for some strategies, you will have to use smaller Average True Range stop losses and larger ones.

Increasing and decreasing the ATR stop loss distance is quite simple; use a multiplier, and based on the strategy, you will have to find the best ATR multiplier stop loss. Many traders use the SuperTrend to calculate the stop-loss; it is no coincidence that the SuperTrend is calculated through the atr.

We will come back to talk more about the multiplier later in the next paragraphs because now we show you how the ATR is calculated.

## How to calculate the Average True Range

We calculate the Average True Range over periods of 14 candles, the N periods of a True Range are added together and divides by the number of periods. The True Range also takes gaps into account.

Welles Wilder developed the indicator for commodity analysis, and he quickly realized that the daily range was not sufficient to examine the volatility of commodities thoroughly.

In the commodities market, many gaps open up for various reasons, sometimes due to the reduced duration of some instruments’ trading sessions.

Wilder realized he had to consider the difference between the previous day’s closing price and the new opening.

To overcome this problem, wilder defined the whole range as the highest of the following values:

• the distance between the maximum and minimum
• the spread between the previous closing price and the current highest price
• The distance between the last closing price and the current lowest price

Once the real range between these three values ​​has been chosen, an average is calculated, generally set at 14 periods. Thus, the Average True Range is a moving average of valid range values ​​instead of closing prices.

As you can see, the Average True Range is straightforward and can also be done through an Excel spreadsheet.

True Range = max [(H – L), (H – C-1),(C-1 – L)]

Average True Range = Sma14(True Range).

## How to calculate ATR Stop Loss

Now let’s see how the ATR Stop Loss is calculated; you will see that it is straightforward and intuitive.

If you are opening a long trade, you will need to subtract your ATR’s value from the previous daily candle’s low price.

However, if you are opening a short trade, you will have to add the value of your Average True Range to the maximum price of the previous daily candle.

We took the daily timeframe as an example because it is great for selecting a stop loss. Statistically, the lows and highs of the previous days’ daily candles often act as support and resistance.

By adding or subtracting the ATR’s value, we will obtain supports and resistances with a safety net.

The main mistake that a novice trader can make is to set a stop loss at a precise price corresponding to a support or a resistance.

When the price bounces on a support or a resistance, it can easily break back, but in this way, it triggers our badly placed stop loss.

By adding or subtracting the Average True Range value from the graph level, we are giving the possibility to cross the level for a space equal to the stock’s average daily volatility.

So we have a stop loss based on a solid technical level consisting of the highs or lows of the previous day with a safety net based on the current volatility.

## How to calculate ATR using a MetaTrader4 indicator

It is also possible to calculate the atr through an indicator especially developed by us for MetaTrader4.

It is a handy indicator and indicates the three stoploss levels directly on the chart.

## How to adjust the ATR stop loss using graphical analysis

Not all strategies need the same stop-loss; for some, you need a tighter stop loss, for others wider.

When we need to expand or narrow the ATR safety net, we have to increase or decrease the Average True Range value.

If you can create an automated trading system, the best way to find the right ATR stop loss is to do backtests, but if you don’t know how to program, you will have to do it through graphical analysis, and in this chapter, we will address this topic, showing you some real cases.

### Case 1: Set an ATR Stop Loss in a long trade with Apple Stock – Using no ATR Multiplier

It’s September 3, 2019; your trading system signals you a long entry on Apple stock; you have to choose a stop loss.

Using our ATR Stop Loss calculation method, subtract the ATR value of \$ 1.22 from the previous day’s low of \$ 51.06.

In this way, you will have obtained the indication to set your stop loss at the price level of 49.84\$.

Let’s check if this stop loss is graphically correct.

We point out to you that the previous day’s candle under consideration is very, very short.

Setting a stop loss on a very short candle is always complicated; placing a stop below its minimum would make us risk a lot.

Therefore, we should have zoomed out and considered the range created after the long bearish candle on August 23.

Furthermore, we are close to the 50 levels, and therefore, we should undoubtedly insert the stop loss below this level.

So the level indicated by the other stop loss calculator is perfect; 49.84 \$ is a great level to enter a stop loss that will have little chance of being hit by market noise with the current volatility.

In this case, the Average True Range calculator worked well and showed us the right price level, but what if it had indicated 50.30, for example?

If the previous days’ volatility had been slightly lower, a stop loss at 50.30 would undoubtedly have been possible; remember that we are opening trade after a very short candle.

However, if it had given us 50.30, as a result, we would have certainly modified it by bringing it below the bullish candle of 23 August and, therefore, below the rectangle of the range created below 50.30.

Furthermore, being now very close to the 50.30 level, we would certainly have inserted the stop a few points below the 50.00 level because round numbers always attract the price.

This moderately substantial change in the stop loss in a very discretionary way should not surprise you.

If you rely totally on the automatic trading system, perform backtests, and you can let the algorithm decide how to set the stop loss.

When trading discretionally, you can use tools such as the atr stop loss calculator, but you must always check that the result has logic.

Do not rely on any tools if you are unable to verify that their indications are not senseless.

### Case 2: Set an ATR Stop Loss in a short trade with Forex EurUsd – Using no ATR Multiplier

It’s September 11, 2017; your trading system signals you a long entry in the forex market with EurUsd; you have to choose a stop loss.

Using our ATR Stop Loss calculation method, add the ATR value of 0.0091 from the previous day’s high of 1.2030.

In this way, you will have obtained the indication to set your stop loss at the price level of 49.84\$.

Let’s check if this stop loss is graphically correct.

Our ATR stop loss calculator once again did a great job placing the stop loss right above the two spikes.

If not, we would have had to move it, always remember that the discretionary stop loss must always have a graphic logic.

It is not the graphical analysis itself; we must always stop losses after some support and resistances because more traders can defend those levels.

## What Lenght use for Average True Range Stop Loss

Before talking about the multiplier, let’s see what length is used for the Average True Range stop loss.

Our advice is always to use a technical indicator’s default setting unless extensive backtests have been done.

So our advice is to use the indicator with the standard 14-period length, but in any case, we will show you what can change by changing this parameter.

These are two Average True Ranges. The first is 5 periods, while the second is 50 periods; as you can see, the first is much more nervous because it averages over a more extended period.

As for any moving average, the shorter the length, the faster the indicator becomes nervous and unreliable.

If we use a more extended period, the curve becomes more smooth. It is easier to have reliable data but indeed delayed in a sudden change in volatility.

So we must always found the right compromise between stability and speed.

## Charting the Average True Range StopLoss

Charting the Average True Range is very useful for every trading strategy.

You can use this indicator to set three different “visual stoploss” levels; you can use this to trailing your position.

## How to use Excel to create your Stop-Loss ATR Calculator

The last step is to calculate your entry size. Before calculating it, we need to decide our stop loss in money.

For example, we have a 20.000\$ account, and we would like to risk only 200\$ for trades, how many shares can we buy?

With this Excel Spreadsheet, you can easily calculate your stoploss based on ATR.

As you can see in the spreadsheet, there are three different StopLoss:

• 1° = Low Price – Average True Range
• 2° = Low Price – Average True Range x 2
• 3° = Low Price – Average True Range x 3

The spreadsheet also calculates the number of share-based on the maximum stoploss in dollars or the number of Lots for forex traders.

## How to identify the volatility phases in a trading system?

You are using this method; when volatility increase, your position decrease. Generally, it’s correct.

But what’s happening before a strong breakout? The volatility became very low, so your position could be more significant than average at the moment of the breakout.

You are entering a storm with a large position, and it’s dangerous. You can reduce this problem by increasing the length of the Average True Range.

Another solution can be to use different multiplier ATR for different volatility phases.

To do this calculation, we need to compare the actual ATR with the older values. For this purpose, the trading system needs to analyze two different ATR periods, for example, 20 and 60.

When the ATR 20 is over the ATR 60, the volatility is high (red color); in this scenario, the multiplier is ok.

When the ATR 20 is under the ATR 60, the volatility is low (green color), there isn’t the multiplier.

Consequently, you have a system that decreases your market exposure more when the volatility is high; that’s correct. But remember that when volatility is too low, the breakout could be near, and you should decrease your position size.