Shahriar Abrar Rafid
If you are a stock market investor or keep up to date about the commodities market, then you’ve obviously heard the word ATR which is the average true range. It’s one kind of market indicator which points out the price volatility of a product. In this article, we’ll talk about this and show you how to calculate the average true range in Excel. So, stay with us throughout.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
What Is Average True Range (ATR)?
In 1978, J. Welles Wilder introduced this technique as an indicator of volatility. The disparity between a stock’s highest and lowest price on any given day is known as its range, and it is frequently employed as a volatility indicator. However, everybody suspends trading frequently if prices change significantly on any given day. We occasionally observe it in the trade of commodities, and it may result in a difference between the opening and closing prices of two successive days. This information wouldn’t always be included in the daily range. Here comes the necessity of ATR. It displays the typical price fluctuation of an asset over a specified time. The indicator can be used by investors to choose the ideal trading period. Even though we use it to forecast trends, it cannot tell which way the price will move. However, the ATR is currently utilized for all future contracts.
Generic Formula to Calculate Average True Range
We require a stock’s daily high, daily low, and daily close price in order to calculate the average true range. At first, we have to know the daily range of stock. It’s nothing difficult at all. Simply, it’s the difference between the high and low prices of a single day.
But we’ve to consider another thing in our mind if yesterday’s closing price doesn’t fit in the daily range. Then, the formula of true range converts into the following.
That means it chooses the maximum among these three differences. To put it simply in words, the true average is the maximum of the following three.
- The present high minus the present low
- The absolute value of present high minus previous close
- The absolute value of present low minus previous close
The calculation of the average true range for the first period is like the following.
Welles proposed using a smoothed average of 14 days. So, in the above formula, n equals 14. Firstly, we’ve to average the true range for the first 14 days, and from that, we’ll get our first average true range. After that, the next ATRs will be calculated using an exponential moving average. In this case, we’ll utilize the following formula.
Here, ATRt means the present average true range.
ATRt-1 means the previous average true range.
n represents 14 because we’re using a 14-day moving average.
TRt serves as the present true range.
So, finally, the formula looks like the one below.
The standard value of n is 14, as the average volatility over the previous 14 days is exposed by ATR, and it is thought that a 14-day period provides the most accurate outcomes.
5 Steps to Calculate Average True Range in Excel
Let’s assume we have a report on the Stock Price of “ABC” Company in our hand. This dataset includes the High, Low, and Close prices of this certain stock from 24 March 2021 to 26 April 2021. We’ve used Freeze Panes to accommodate the full picture of the dataset.
Now, we’ll show how to calculate the average true range from this data in Excel.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Let us know if you face any problems while doing the same work in any other version of Excel.
Step 01: Determine Present High Minus Present Low
At the very beginning, we’ll calculate the daily ranges. To do this, follow the steps carefully.
📌 Steps:
- Firstly, select cell F5 and enter the following formula into that cell.
=C5-D5
Here, C5 and D5 represent the High and Low prices of the corresponding day.
- Secondly, press the ENTER key.
- Thirdly, bring the cursor to the bottom-right corner of cell F5 and it will look like a plus (+) sign. It’s the Fill Handle tool. Then, double-click on it to use the tool.
Wondrously, it fills the following cells till cell F27 with correct results. Using this tool saves us a lot of time.
Read More: How to Calculate Time Range in Excel (4 Handy Methods)
Step 02: Calculate Present High Minus Previous Close
Then, we’ll calculate the second part of the formula for the true range stated above. Let’s see it in action.
📌 Steps:
- Initially, select cell G6 and write down the following formula.
=ABS(C6-E5)
Here, C6 and E5 represent the present high price and the previous close price respectively. The ABS function returns the absolute value of their difference. That means it always gives a positive value.
- After that, press ENTER.
Read More: How to Calculate Range in Excel (5 Handy Methods)
Step 03: Compute Present Low Minus Previous Close
In this section, we’ll find out the third part of the formula for the true range stated above. Let’s see the process in detail.
📌 Steps:
- Primarily, go to cell H6 and paste the formula below.
=ABS(D6-E5)
- Secondarily, hit ENTER.
Here, cells G5 and H5 are blank because there is no previous day before these days.
Read More: How to Average Negative and Positive Numbers in Excel
Step 04: Enumerate True Range
Now, from the results we get in the previous three steps, we’ll calculate the true range. It’s simple & easy, just follow along.
📌 Steps:
- At first, select cell I5 and put the following formula into the cell.
=MAX(F5:H5)
In this formula, F5:H5 represents the cells with values of high-low, high-previous close, and low-previous close.
Here, the MAX function returns the maximum value among data in cells F5, G5, and H5.
- As usual, tap the ENTER key.
Similarly, we can check for cell I6 if the formula returns the correct answer or not. There are 5.37, 1.48, and 3.89 in cells F6, G6, and H6 correspondingly. Easily, we can see that 5.37 is the greatest among them. And the function also returns the same value in cell I6.
Read More: How to Calculate Range for Grouped Data in Excel (3 Effective Methods)
Step 05: Calculate Average True Range
Finally, the phase has come. We’ll get the desired average true range after completing this section.
📌 Steps:
- First of all, select cell J18 and paste the formula below.
=AVERAGE(I5:I18)
In this case, the AVERAGE function returns the arithmetic mean of the arguments.
- As always, hit ENTER.
Why have we kept the cells in the J5:J17 blank? Any idea? Just get back to the generic formula we discussed before. There we’ve said that we’ll calculate the ATR for the first period using the arithmetic mean of consecutive 14 days. So, here, we’ve used 14 days of data in cells in the I5:I18 range.
- Then, we’ve to determine ATR for the following days. To do this, go to cell J19 and enter the following formula.
=(J18*13+I19)/14
We also explained this formula in the previous section of this article.
- Accordingly, hit the ENTER key.
Read More: How to Calculate Average of Top 5 Values in Excel (5 Methods)
How to Calculate ATR Stop Loss
We just learned to figure out how to calculate the average true range in Excel. ATR can assist you in determining where to set your stop loss. A correctly positioned stop loss takes care of losing too much by acting as insurance. Your stop loss will likely be broader if you’re dealing with a stock with a high ATR because the stock will likely experience larger daily price swings.
We can utilize multiples of the ATR reading (2x/3x), and at this point, you can choose the market you’re trading in accordance with your risk appetite. There isn’t a fixed ideal number. You must choose it based on the state of the market and your prior knowledge.
Now, we have taken a portion of our previous dataset with the corresponding calculated ATR values.
As a thumb rule, we took the second multiple of the ATR value. Then, we’ll calculate the stop loss for each date. So, without further delay, let’s dive in!
📌 Steps:
- Firstly, create a new column Stop Loss.
- Here, go to cell G5 and enter the formula below.
=E5-2*F5
- Then, press ENTER.
For clarification, we’re explaining it now. Let’s say you purchased stock on 13 April 2021 at the close price of this stock which is $258.49. Then, you will set a stop loss at $249.26 which means if the price of this stock falls on this value of stop loss, you won’t take any further risk and sell this stock.
Similarly, if someone buys it on 21 April at a price of $260.58, then he will set his stop loss at $252.07 if he takes the second multiple of the ATR reading.
Read More: How to Calculate Date Range in Excel
Practice Section
For doing practice by yourself we have provided a Practice section on the last sheet. Please do it by yourself.
Conclusion
This article provides easy and brief solutions on how to calculate the average true range in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.
Related Articles
- How to Calculate Interquartile Range in Excel (2 Suitable Ways)
- Calculate Percentage Range in Excel (Step-by-Step Guide)
- How to Average Filtered Data in Excel (2 Easy Methods)
- Calculate Weekly Average in Excel (4 Easy Ways)
- How to Do Subtotal Average in Excel (5 Suitable Ways)
- Calculate Average Numbers in Excel (9 Handy Methods)
- How to Calculate Average Share Price in Excel (2 Easy Ways)
