Saturday, July 7, 2012

How to calculate Exponential Moving Average in Excel

How to calculate Exponential Moving Average illustrated with an excel example.

Have a look at the spreadsheet below. Once it opens to download simply press Ctrl+S

Now the formulas used.

1. Exponent function value - this is essentially the weight used to weigh each new period.

= 2/(1+ no of periods)
= 2/(1+20) for a 20 period EMA

2. Actual calculation of EMA

= Previous EMA * (1-Exponent) + Current Close * Exponent

One clarification here. For calculation of the first EMA we simply use a SMA as value of EMA needs a previous value and thus we need to start somewhere. This does not affect the value of the EMA after the first 5-6 values.

For any clarifications or queries, please leave a comment.

Dow Jones Index Daily Historical data

For those looking for all time dow jones historical data one can download the same from here...

Once it has happened simply press Ctrl+S to download.

What is the difference between simple moving average and exponential moving average

Both are similar in the sense that both serve the same purpose of trying to smoothen out the volatility of the underlying data to give a sense of the direction and trend of the data.

How they differ is in the weightages given to each value. While simple moving average (Sma) gives equal weight to all values, the exponential moving average gives more weightage to recent events and thus is more sensitive to movements in the underlying data than the sma.

A simple way to put it would be that a 10 period exponential moving average would behave like a 7 or 8 period simple moving average, but will be smoother and is thus better to use. Overall, Ema's have proved to be more useful than Sma's and thus one will find that many technical indicators use EMAs. Like MACD uses only EMAs and no SMAs.

Have a look at the actual calculation of both simple and exponential moving average in another post of mine. Looking at the calculation in excel will improve your understanding of how they are calculated and move. 

What is Exponential Moving Average

Exponential Moving Average or EMA is not very different from the Simple Moving Average (SMA). Both are essentially used to smoothen a curve to get a better sense of the underlying trend.

How they are different is that the Ema is more sensitive than the Sma. Ema reacts and moves faster than the Sma in following the value of the data. How that actually happens is that in the calculation of Ema, recent events are given greater weight than earlier events and thus it tracks the data much more closely. The Sma on the other hand gives equal weight to all values, and thus is slightly more lagged.