True range is an indicator often used to measure volatility of investments such as stocks. It’s most often presented in the form of average true range (ATR). Excel makes it easy to calculate the ATR of an investment’s volatility, thanks to the use of formulas. Let’s learn how to calculate ATR in Microsoft Excel.
How to Calculate ATR in Excel
Average true range is calculated as a 14-day moving average in many common scenarios. Let’s imagine fourteen days of true range data for a given investment. It’s first useful to know how true range is calculated. True range takes three measurements of an investment on a daily basis:
- The price range from highest to lowest within the day
- The price range from the day’s high to the prior day’s closing price
- The price range from the day’s low to the prior day’s closing price
The true range is the maximum – largest – of the three range values listed above. As you can see, a true range spreadsheet might look like this.
The ATR is a simple moving average consisting of 14 days’ worth of data. In the range here, you’ll simply want to calculate the average of all 14 true range values that you’ve computed.
To do that, click into an empty cell on your worksheet. Type an = to begin a formula. Then, insert the AVERAGE function:
=AVERAGE(
Finally, click and drag to select the cells containing your true range data. Here, this range is B2:B15. Close your parentheses, completing your formula as:
=AVERAGE(B2:B15)
Hit Enter on your keyboard, and Excel returns your true range value:
As you can see, Excel makes it easy to find the average true range (ATR) for a given investment. This is a great tool to measure volatility and illustrate it based on real-world performance data for investments.
How to Calculate Arrears in Excel