지수 이동 평균(EMA)은 미래 데이터를 예측하거나 주가 변동을 부드럽게 시각화할 때 널리 활용되는 기법입니다. 지수 이동 평균이 무엇인지, 그리고 Excel에서 어떻게 계산할 수 있는지 궁금하셨다면 잘 찾아오셨습니다. 이 글에서는 Excel에서 지수 이동 평균을 계산할 수 있는 3가지 실용적인 방법을 단계별로 소개해 드립니다.
지수 이동 평균(EMA)이란?
지수 이동 평균(EMA)은 '지수 가중 이동 평균'이라고도 불리는 이동 평균의 한 종류로, 단순 이동 평균보다 최근 데이터에 더 많은 가중치를 부여합니다. 그렇기 때문에 Excel에서 지수 이동 평균을 적용했을 때 나타나는 평활(smoothing) 효과는 단순 이동 평균보다 큽니다. 최신 데이터의 영향력은 기간이 길어질수록 점차 줄어듭니다.
EMA를 계산하는 공식은 다음과 같습니다.
EMAt = α × Yt-1 + (1-α) × EMAt-1
- EMAt : 현재 기간의 지수 이동 평균
- α : 평활 계수(Smoothing multiplier). 0에서 1 사이의 값을 가질 수 있으며, α 값이 클수록 최근 데이터에 더 많은 가중치가 부여됩니다. 일반적으로 α의 적정 범위는 0.1~0.3이며, 다음 공식으로도 구할 수 있습니다. α = 2/(n+1) (여기서 n은 기간 수)
예시를 위해 2020년 1월부터 12월까지 12개월 치의 매출 데이터가 담긴 아래 데이터셋이 있다고 가정해 보겠습니다. 우리의 목표는 지수 이동 평균을 활용해 2021년 1월의 매출을 예측하는 것입니다.
Excel에서 지수 이동 평균을 계산하는 3가지 방법
방법 1. 데이터 분석 도구팩으로 지수 이동 평균 계산하기
데이터 분석 도구팩(Analysis ToolPak)을 활용하면 복잡한 수식 없이도 지수 이동 평균을 적용해 미래 데이터를 예측할 수 있습니다. 먼저 Excel에 해당 도구팩을 추가해야 합니다.
➤ 파일 탭으로 이동하여 옵션을 선택합니다.
Excel 옵션 창이 열리면,
➤ 추가 기능 탭으로 이동한 뒤 하단의 이동(Go) 버튼을 클릭합니다.
추가 기능 대화상자가 나타납니다.
➤ 분석 도구팩(Analysis ToolPak)에 체크하고 확인을 클릭합니다.
도구팩 설치가 완료되면,
➤ 데이터 탭으로 이동하여 데이터 분석을 클릭합니다.
데이터 분석 창이 열립니다.
➤ 목록에서 지수 평활법(Exponential Smoothing)을 선택하고 확인을 클릭합니다.
지수 평활법 설정 창에서,
➤ 입력 범위 상자에 매출 데이터가 있는 셀 범위를 지정합니다. 이때 예측 대상 기간의 빈 셀까지 포함해야 합니다. 그리고 감쇠 계수(Damping factor) 상자에 α 값을 입력합니다.
이 예제에서는 α 값을 0.3으로 설정했습니다.
그다음,
➤ 계산 결과를 표시할 출력 범위를 지정합니다.
실제 데이터와 EMA를 그래프로 비교해 보고 싶다면,
➤ 차트 출력(Chart Output) 항목에 체크합니다.
마지막으로,
➤ 확인을 클릭합니다.
결과적으로 선택한 출력 범위에 각 기간의 EMA 값이 계산되고, 2021년 1월 매출에 대한 예측값까지 얻을 수 있습니다.
잘 살펴보면 2월의 EMA가 1월의 실제 매출값과 동일한 것을 알 수 있습니다. Excel의 분석 도구팩은 첫 번째 EMA를 직전 기간의 데이터와 같다고 가정하기 때문인데, 이러한 방식을 예측 분야에서 나이브 방법(Naive Method)이라고 부릅니다.
또한 Excel은 실제 데이터와 계산된 EMA 예측값을 비교하는 차트도 함께 제공합니다.
방법 2. 수식을 직접 입력해 수동으로 계산하기
분석 도구팩을 사용하지 않고도 EMA 계산 공식을 직접 입력해 지수 이동 평균을 구할 수 있습니다. 전체 기간의 EMA를 계산하려면 먼저 첫 번째 기간의 EMA 값을 알아야 하는데, 이는 나이브 방법, 단순 이동 평균, 3기간 이동 평균 등 다양한 방식으로 구할 수 있습니다. 여기서는 3기간 이동 평균(3MA) 방식을 사용해 보겠습니다.
3기간 이동 평균을 구하려면,
➤ 4월 행의 셀 E8에 아래 수식을 입력합니다.
=(D5+D6+D7)/3
이 수식은 직전 3개월 매출의 평균을 반환합니다. 그다음,
➤ 셀 E8을 아래로 끌어 채우기 하면 전체 기간의 3MA를 구할 수 있습니다.
이제 EMA 계산에 필요한 α 값을 구해야 합니다. 이번에는 다음 공식으로 α를 결정하겠습니다.
=2/(12+1)
데이터셋에 총 12개의 기간이 있으므로 n=12로 설정했습니다.
그다음,
➤ 네 번째 기간의 EMA를 구하기 위해 아래 수식을 입력합니다.
=(1-$C$3)*D7+$C$3*E8
이 수식은 첫 번째 산출 기간의 EMA를 계산하며, 해당 기간의 3MA를 이전 기간의 EMA 대신 사용했습니다.
첫 번째 기간의 EMA를 구했으므로, 이제 나머지 모든 기간의 EMA를 손쉽게 계산할 수 있습니다.
➤ 셀 F9에 다음 수식을 입력합니다.
=(1-$C$3)*D8+$C$3*F8
여기서 $C$3은 α 값이 저장된 셀, D8은 이전 기간의 매출, F8은 이전 기간의 EMA를 의미합니다.
마지막으로,
➤ 셀 F9를 데이터셋 끝까지 드래그하여 채웁니다.
그러면 예측 대상 기간인 2021년 1월을 포함한 모든 기간의 EMA를 얻을 수 있습니다.
방법 3. VBA로 사용자 정의 함수 만들어 계산하기
지수 이동 평균은 주가 움직임을 부드럽게 만드는 데에도 활용됩니다. 이번 방법에서는 Microsoft VBA(Visual Basic Application)로 사용자 정의 함수를 만들어, 여러 기간에 걸친 주가 변동을 지수 이동 평균으로 평활하는 과정을 살펴보겠습니다.
주가를 평활할 때는 EMA 계산 공식에서 이전 기간의 데이터 대신 현재 기간의 가격을 사용합니다. 따라서 공식은 다음과 같이 바뀝니다.
EMAt = α × P + (1-α) × EMAt-1
여기서 P는 현재 기간의 가격입니다.
이번에는 연중 여러 달에 걸친 특정 주식의 평균 가격 데이터가 있다고 가정하겠습니다. VBA로 사용자 정의 수식을 만들어 월별 주가의 지수 이동 평균을 계산해 보겠습니다.
➤ 먼저 시트 이름 위에서 마우스 오른쪽 버튼을 클릭하고 코드 보기(View Code)를 선택합니다.
VBA 코드 창이 열립니다.
➤ 해당 창에 아래 코드를 입력합니다.
Private Sub Workbook_Open()
AddUDF
End Sub이 코드는 통합 문서가 열릴 때 매크로를 실행해 해당 시트에 사용자 정의 코드를 등록하는 역할을 합니다.
그다음,
➤ VBA 창 왼쪽 패널에서 시트 이름을 마우스 오른쪽 버튼으로 클릭하고 삽입 > 모듈로 이동합니다.
VBA 모듈 창이 열리면,
➤ 모듈 창에 아래 코드를 입력합니다.
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이 코드는 EMA(EMAYesterday, price, n)라는 이름의 사용자 정의 함수를 생성합니다.
이제,
➤ VBA 창을 닫습니다.
이 사용자 정의 함수로 EMA를 계산하려면 첫 번째 기간의 EMA 값을 알아야 합니다. 여기서는 나이브 방법을 사용해 첫 번째 기간의 EMA를 해당 기간의 주가와 동일하게 간주하겠습니다.
그다음,
➤ 셀 D6에 아래 수식을 입력합니다.
=EMA(D5,C6,12)
여기서 D5는 이전 기간의 EMA, C6는 현재 기간의 주가, 12는 기간 수입니다.
➤ Enter 키를 누릅니다.
그러면 해당 기간의 EMA가 계산됩니다.
이제,
➤ 셀 D6을 데이터셋 끝까지 드래그하여 채웁니다.
모든 기간의 EMA가 한 번에 계산됩니다.
마무리
지금까지 Excel에서 지수 이동 평균을 계산하는 세 가지 방법, 즉 데이터 분석 도구팩 활용, 수식 직접 입력, VBA 사용자 정의 함수 생성 방법을 알아보았습니다. 상황에 맞는 방법을 선택해 미래 데이터 예측이나 주가 평활 분석에 활용해 보시기 바랍니다. 내용 중 궁금한 점이 있다면 언제든 댓글로 문의해 주세요.
함께 읽으면 좋은 글
- Excel에서 평균, 최솟값, 최댓값 계산하는 4가지 쉬운 방법
- Excel에서 VLOOKUP으로 평균 계산하는 6가지 빠른 방법
- AVERAGE 함수를 활용한 누적 평균 계산 방법