In the earlier post we have seen how to calculate SMA using excel. But the problem with SMA is that it allocates equal weightage to all the observations. This increases a lag in the indicator which responds slowly to the price movement. To reduce this lag, traders have come up with another indicator called Exponential Moving Average.

Exponential Moving Average (EMA) allocates highest weightage to the latest closing price and least weightage to the historical closing prices. 

Formula:

Multiplier: (2 / (Time periods + 1) ) 
EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day). 
Here Time period is the number of days you want to look back.

Download EMA Excel Sheet Calculation 

In the sheet attached, we have considered EMA for 10 days, so the look back period / Time Period will be 10 days.

Column ‘E’ contains the “close price” and Column ‘F’ contains the EMA itself. Since we have taken EMA 10, first 10 days won’t have any EMA. If you look at the formula of EMA above, EMA of current day will depend on the EMA of yesterday. But, since we don’t have EMA for the first time, we just take simple moving average on the 10th day. From 11th day onwards we start calculating EMA.