Exponential Moving Average is widely used for forecasting future data and smoothening stock price movement. If you want to know what an exponential moving average is and how you can calculate this in Excel, this is the right place for you. In this article, Iâ€™ll show you 3 suitable methods to calculate Exponential Moving Average in Excel.

**Download Practice Workbook**

## Exponential Moving Average (EMA)

The Exponential Moving Average (**EMA**), also known as the exponentially weighted moving average, is a kind of moving average that gives more weight to recent data than the simple moving average. Therefore smoothening in the case of the exponential moving average in excel is higher than that of the simple moving average. The importance of the most recent data decreases as the time period lengthens. The formula of calculating **EMA** is,

**EMA _{t }= Î± xY_{t-1 }+ (1-Î±) x EMA_{t-1}**

Where, **EMA _{t }**= Exponential moving average of current period

**Î±** = Smoothing multiplier. Î± can be any number between 0 to 1. The more the value of alpha, the more weight is given to the past data. The ideal range of Î± lies between 0.1 to 0.3. Î± can also be calculated by the following formula, Î± = 2/(n+1) ; here, n is the number of periods.

Suppose, we have the following dataset where we have sales data of 12 month in 2020. Now we want to forecast the sales data for January 2021 using Exponential Moving average.

## 3 Methods to Calculate Exponential Moving Average in Excel

### 1. Data Analysis Toolpak to Calculate Exponential Moving Average

We can apply Exponential moving average to forecast future data by using the **Data Analysis ToolPak**. For that, first, you have to add this **ToolPak** in your Excel.

âž¤ Go to the **File **tab and select **Options**.

It will open the **Excel Options** window. Now,

âž¤ Go to **Add-ins **tab and click on **Go**

** **

It will open the **Add-ins **box.

âž¤ Check on **Analysis ToolPak **and click on **OK**.

Now,

âž¤ Go to the **Data **tab and click on **Data Analysis**.

It will open the **Data Analysis **window.

âž¤ Select **Exponential Smoothing **and click on **OK**.

Now, in the **Exponential Smoothing **window,

âž¤ Select the cells with sales data in the **Input Range **box ( including the empty cell of the forecast period). Insert the value of **Î±** in the box **Damping factor.**

For this example, we have taken **Î±** as 0.3.

After that,

âž¤ Select the **Output Range** (where you want your **EMA** calculation).

If you want to see the graphical representation of the **EMA** and actual data,

âž¤ Check the box **Chart Output**.

At last,

âž¤ Click on **OK**.

As a result, Excel will give the **EMA** of your data in your selected sales and you will get the forecast of sales for January 2021.

If you observe, you will see that the **EMA** for February is the same as the sales for January. Excel **Analysis ToolPak **simply assumes that the first **EMA** equals the previous periodâ€™s data which is also known as the **Naive Method** for forecasting.

Excel will also show a chart comparing the Actual Data and Calculated EMA forecast.

### 2. Calculate Exponential Moving Average in Excel Manually

Instead of using the **Analysis ToolPak **we can calculate the Exponential Moving Average by manually inserting the formula for **EMA** calculation. To calculate **EMA** for all the periods we need to know the **EMA** for the first period. We can calculate this using various methods such as naive, simple moving average, 3 period moving average, etc. In this example, we will use the 3 period moving average method (**3MA**).

To calculate the 3 period moving average,

âž¤ Type the following formula in the row of April month (cell **E8**),

`=(D5+D6+D7)/3`

The formula will return the average of the previous three yearâ€™s sales. After that,

âž¤ Drag cell **E8** to get the** 3MA** for all the periods.

Now, we will need **Î± **to calculate the **EMA**. In this time we will determine **Î± **using the following formula,

`=2/(12+1)`

We have taken n= 12 as we have 12 periods in our dataset.

After that,

âž¤ Insert the following formula to determine **EMA** for the fourth period,

`=(1-$C$3)*D7+$C$3*E8`

The formula will give the **EMA** for the first period where we have used this periodâ€™s **3MA** as previous periods **EMA**.

Now, we have got the **EMA** for the first year, so we can easily calculate the **EMA** for all of the periods.

âž¤ Type the following formula in cell **F9**,

`=(1-$C$3)*D8+$C$3*F8`

Here, **$C$3 **contains the value of **Î±**, **D8 **is the previous periodâ€™s sale and **F8** is the previous periodâ€™s **EMA**.

At last,

âž¤ Drag cell **E9 **to the end of your dataset.

As a result, you will get the **EMA** for all of the periods including the forecast period (January 2021)

### 3. Using VBA to EMA

Exponential Moving Average is also used to smoothen stock price movement. In this method, we will see how we can use Exponential Moving Average to smoothen the stock price movement over multiple periods by making a custom function using Microsoft **Visual Basic Application (VBA)**.

In the time of smoothening stock price movement, the current period price is used in the place of the previous periodâ€™s data in **EMA** calculation formula. So the formula becomes,

**EMA _{t }= Î± x P + (1-Î±) x EMA_{t-1}**

Here, P is the current period price.

The formula can also be written in this way,

**EMA _{t} = Î± x P+ (1 â€“ Î±) x EMA_{t-1}**

Suppose, at this time in our dataset we have the average price of a stock over different months of a year. Now, we will build a custom formula using **VBA** to calculate the exponential moving average of the stock price in different months.

âž¤ First, right click on the sheet name and select **View Code**.

It will open the **VBA Code **window.

âž¤ Type the following code in this window,

```
Private Sub Workbook_Open()
AddUDF
End Sub
```

The code will create a Macro which will add a custom code in this sheet of the Excel Workbook.

After that,

âž¤ Right click on the sheet name from the left panel of the **VBA **window and go to **Insert > Module**.

It will open the **VBA Module **window. Now,

âž¤ Insert the following code in the **Module **window,

```
Sub AddUDF()
Application.MacroOptions macro:="EMA", _
Description:="Returns the Exponential Moving Average." & Chr(10) & Chr(10) & _
"Select last period's EMA or last period's price if current period is the first." & Chr(10) & Chr(10) & _
"Followed by current price and n." & Chr(10) & Chr(10) & Chr(10) & _
"The decay factor of the exponential moving average is calculated as alpha=2/(n+1)", _
Category:="Technical Indicators"
End Sub
Public Function EMA(EMAYesterday, price, n)
alpha = 2 / (n + 1)
EMA = alpha * price + (1 - alpha) * EMAYesterday
End Function
```

The code will create a custom function named **EMA (EMAYesterday, price, n)**

Now,

âž¤ Close the **VBA **window.

Now, to calculate **EMA** using this custom function we need to know the **EMA** of the first period.

In this example, we will use the naive method. So, the **EMA** of the first period will be equal to the stock price of this period.

After that,

âž¤ Insert the formula in cell **D6**,

`=EMA(D5,C6,12)`

Here, **D5 **is the previous periodâ€™s **EMA**, **C6 **is the current periodâ€™s price and **12 **is the number of periods.

âž¤ Press **ENTER**.

As a result, you will get the **EMA** of that period.

Now,

âž¤ Drag cell **D6** to the end of your dataset.

As a result, you will get the **EMA** for all of the periods.

## Conclusion

I hope now you know how to calculate the exponential moving average in Excel. If you have any kind of confusion, please feel free to leave a comment.