엑셀로 전략 수익률, MDD 구하는 방법

엑셀을 이용해 최대 손실률(Max Drawdown)을 구하는 방법에 관한 포스팅입니다. 

 

엑셀로-수익률과-MDD-구하기

 

엑셀의 MAX 함수를 이용해서 MDD를 구해보도록 하겠습니다. MDD를 왜 구해야 하는지, MDD란 무엇인지? 잘 모르는 분들은 아래 포스팅을 꼭 확인하시기 바랍니다. 

 

투자 전략의 MDD를 낮출 수 있는 방법

 

보통 퀀트 전략의 백테스팅을 위해서는 해당 자산군의 날짜, 시가, 종가 자료가 필요합니다. 스스로 구축하기도 하고, 인베스팅 닷컴 같은 사이트를 이용해서 다운로드하기도 합니다. 일단 그런 자료가 있다는 가정하에 설명하겠습니다. 

 

백테스팅하고 싶은 자산군의 날짜, 시가, 종가가 있다면 퀀트 전략에 따라 수익률을 구할 수 있습니다. MDD를 구하기 위해서는 수익률을 비롯해 여러 가지 결과 값이 미리 있어야 하기 때문에, 엑셀로 MDD를 구하는 과정을 차례대로 설명해 보겠습니다. 

 

1. 수익률 구하기

엑셀로-MDD-구하기-표(1)

위의 표 1은 1980년부터 1980년 12월까지 코스피 지수를 매수 후 홀딩했을 때 매월 수익률을 구한 값입니다. 화면에는 가려져 있지만, B열~G열까지는 해당 기간의 종가, 시가 및 여러 데이터가 있습니다. 매수 후 홀딩 전략이니까 일단 시가와 종가 데이터만 있으면 이 전략의 수익률을 구할 수 있습니다.

 

수익률을 구하는 방법은 간단합니다. 우리는 12월까지 매수 후 보유할 예정이니까 H3 셀의 값을 구하고 싶으면, (2월 시초가 / 1월 시초가 -1) 해서 백분율로 나타내 주면 됩니다. 이렇게 계산해 주면 1월에는 4.48% 상승이 있었음을 알 수 있습니다. 이렇게 모든 달의 수익률을 구해줍니다. 

 

만약 매달 시가에 매수해서 종가에 매도하는 전략을 구현하고 싶다면 (해당 월의 종가 / 해당 월의 시가 - 1) 해주면 됩니다. 전략에 따라서 인수가 달라지니 잘 구분해서 구하면 됩니다.

 

2. 자산의 현재 값 구하기 

수익률을 구했다면 그 옆 열에 자산의 현재 값을 구합니다. I2 셀에서 보듯이 시작가는 1입니다. 자산 1이 1월에 4.48% 늘어야 합니다. 그래서 (이전 자산값 * 현재 월 수익률) 함수를 써줍니다. 그 결과값은 I3 셀에 나와있듯이 1.0448이 됩니다. 한 달 동안 자산이 0.0448 늘어난 것을 확인할 수 있습니다. 이렇게 월별 늘어나거나 줄어든 자산값을 모두 구해줍니다. 수익률도 마찬가지지만, 전략이 일별 전략이라면 모든 일의 값을 구해줘야 될 것입니다. 

 

3. MAX값 구하기

자산 증가 값을 구했다면, 그 옆 열에 MAX값을 구해야 합니다. MAX값은 투자를 시작하고 난 후, 자산이 가장 높았을 때 값을 구하는 것입니다. MAX 함수는 비교하는 두 개의 값 중 큰 값을 기록하는 함수입니다.

 

함수식은 =MAX(이전 월 MAX값, 현재 월 자산 값) 해주면 간단히 구할 수 있습니다.

 

전략을 시작한 첫 값인 1부터 비교해서 차례대로 내려왔기 때문에, MAX값은 전략 시행하는 도중 가장 큰 자산값을 나타내 줍니다. 표 1에서 보듯이 1.1677 값이 8개월째 가장 높은 값으로 기록되고 있음을 확인할 수 있습니다.  

 

4. MDD 구하기

MAX값까지 구했다며 이제 MDD를 구할 차례입니다. 

엑셀로-MDD-구하기-표(2)

MDD는 표 2에서 보는 것과 같이 (자산 값/ MAX값 - 1)로 구해줍니다. 이 함수의 뜻은, 투자를 실행한 후 자산 값이 가장 높을 때와 가장 낮았을 때의 차이를 백분율로 나타내 주는 것입니다. 

 

이전 포스팅에서 MDD란 MAX Drawdown의 약자이며, 수치가 가장 높았을 때부터 가장 낮을 때까지 낙폭을 나타내 주는 비율라고 설명한 바 있습니다. 위의 전략에서 보면 1980년 1월에 코스피 매수 후 12월까지 보유하였다면, -16.16%의 MDD를 경험했다는 것을 확인할 수 있습니다.  

 

표 2에 나와있는 K1 셀의 -73.11%는 월별로 구한 40년간의 MDD 중 가장 큰 값을 나타낸 것입니다. 즉 40년 동안 코스피 홀딩 전략을 썼다면 자산이 가장 높은 지점에서 -73% 까지 내려가는 극한의 MDD를 경험했을 것입니다. 코스피 전략은 어떤 것이 가장 좋을까요? 이전 포스팅에서 코스피 전략에 대해 백테스팅한 결과가 있으니 어떤 전략이 가장 좋을지 확인하시기 바랍니다. 

 

코스피 투자전략 & 수익률 백테스팅 (1980년-2021년)

 

이제 엑셀로 수익률, 자산 값, MDD 모두를 구할 수 있게 되었습니다. 산수 정도만 할 줄 알면 누구나 할 수 있으니, 여러 가지 전략을 백테스팅하셔서 투자에 도움이 되시기 바랍니다. 성공 투자를 기원합니다.