Tuesday, April 16, 2013

Calculate Exponential Moving Average in excel

How to calculate Dow Jones Exponential Moving Average - illustrated with data in excel spreadsheet.

What is exponential moving average(ema) and how is it different from simple moving average(sma)? Simple moving average takes an average of the no of days. So a 20 day sma would calculate an average of all the figures for preceding 20 days. In EMA, we give more weight to recent observations. This is done via using a factor called exponent.

The value for the exponent = 2/(No of days +1)
So for EMA of 20 days the exponent = .047619

Now we multiply today's closing figure with this exponent, and yesterday's EMA with (1-exponent) to get new ema. Obviously you have to start somewhere, so we start with a simple average for the first 20 data points and then move onto the EMA formula from there. Confusing? Have a look at the excel sheet it will become a little more clearer as to what it is that I am talking about and how exactly it is calculated.

Label: ema excel