How to Calculate Bollinger Bands in Excel
Bollinger Bands belong to Volatility category of Indicators. It consists of three bands – upper band, lower band and middle band. As per Bell Curve, 68% of the observations lie in the 1STD (Standard Deviation) from Mean, 95% observations lie in the 2STD from Mean and 99.7% observations lie in 3STD from Mean Values
The bands automatically widen when volatility increases and contract when volatility decreases. Theoretically when price touches upper band / lower band it reverses to average. That means upper bollinger band acts as resistance and lower band acts as support.
Middle Band = 20-day simple moving average (SMA) Upper Band = 20-day SMA + (20-day standard deviation of price x 2) Lower Band = 20-day SMA - (20-day standard deviation of price x 2) Download Bollinger Band Excel Template
In the Excel sheet attached, Column ‘E’ contains the close price, Column ‘F’ contains Standard Deviation, Column ‘G’ contains 20 SMA which is the middle band, Column ‘H’ contains Upper Bollinger Band which is (20SMA+2STD) and lower Bollinger Band contains (20SMA-2STD).
