Exponentially Weighted Moving Average (EWMA) in Excel

by Best Excel Tutorial Statistics

The Exponentially Weighted Moving Average (EWMA) is a type of moving average that gives more weight to recent data points, making it more responsive to new information. It’s widely used in financial analysis, statistics, and engineering for smoothing data or identifying trends.

Unlike simple moving averages, EWMA applies a decay factor to give exponentially decreasing weight to older data points. Here’s how you can calculate EWMA in Excel:

Step 1: Prepare Your Data

Arrange your time series data in a column. Assume your data starts from cell A2.

Choose your smoothing factor (λ). Commonly, λ is set between 0.2 and 0.3 for short-term averages, but it can vary depending on your analysis needs. Place this value in a separate cell (e.g., B1) for easy reference.

Step 2: Calculate the First EWMA Value

For the first EWMA value, you can simply use the first data point or calculate a simple average of the first few data points. Place this initial EWMA value in the same row as your first data point for consistency. If you’re using the first data point as the initial EWMA, then in cell B2, you would just reference your first data point: =A2.

Step 3: Apply the EWMA Formula

Starting from the second data point (cell A3), use the EWMA formula to calculate the rest of the values. In cell B3, input the formula:

See also  How to calculate covariance in Excel?

=B2*λ + A3*(1-λ)

Where B2 is the previous EWMA value, λ is your smoothing factor, and A3 is the current data point. Replace λ with the cell reference where you’ve placed the λ value (e.g., $B$1 if you placed λ in cell B1).

Step 4: Drag the Formula Down

Drag the formula down from B3 to the end of your data series in column A. This action fills in the EWMA values for your entire dataset.

Step 5: Graph Your EWMA (Optional)

Visualizing your EWMA alongside your original data can help in analyzing trends:

Highlight both your original data and the EWMA values.

Go to the Insert tab, choose a line chart to visualize the trends clearly. This step will create a graph that plots both your original data and its EWMA, allowing you to compare them directly.

Related posts:

calculating percent changeHow to Calculate Percentage Change Skewness data tableHow to Calculate Skewness in Excel weighted average formulaHow to calculate Weighted Average?