엑셀 CAGR 구하는 RATE 함수 및 기하평균 공식

엑셀에서 CAGR(연평균성장률)을 구할 수 있는 2가지 방법에 대한 설명입니다.

퀀트 투자 백테스팅에서 CAGR값 산출은 반드시 필요한 과정입니다. 엑셀에서 CAGR은 RATE 함수를 통해서 구할 수도 있고, 직접 공식을 입력해서 구할 수도 있습니다. 저는 주로 RATE함수를 이용해서 구하는 편이지만 공식도 알아두면 좋을 것 같습니다. 

 

 

엑셀-CAGR-구하는-RATE함수

 

CAGR은 산술평균이 아닌, 기하평균으로 산출해야 합니다. CAGR, 산술평균, 기하평균에 대한 설명은 이전 포스팅에서 쉽게 설명해 놓았으니 참고하시기 바랍니다. 

 

연평균성장률(CAGR) 공식, 산술평균과 기하평균 이란?

 

코스피의 연평균성장률 (2003년 ~ 2020년)

아래는 코스피 수익률에 관해서 제가 백테스팅한 결과입니다. 매년 1월 첫 거래일에 코스피 지수를 (모든 종목) 매수하고, 해당 연도 마지막 거래일에 매도한다는 전략으로 산출한 값입니다. 거래비용, 배당금, 세금, 슬리피지 모두 포함되지 않은 값이며, 오로지 지수의 변동률로만 구한 값입니다. 

 

코스피 지수에만 투자했었도 지난 17년 동안 CAGR 9.6%를 얻을 수 있었습니다. 하지만 2008년 같은 해에는 -40.73%의 손실을 입어야 했기에, 이때 주식판을 떠난 투자자들도 많았았으리라 생각이 듭니다.

 

코스피-2003년부터-2020년까지-연평균성장률

 

1. 엑셀 RATE 함수를 이용한 CAGR 구하기

엑셀 RATE함수 전체는 다음과 같습니다.

• =RATE(연도, 0, -시작 값, 마지막 값)

각 항목별로 보면 다음과 같습니다.

1. =RATE : RATE 함수의 시작을 알립니다.
2. 연도 : 몇 년간 투자했는지 적습니다. 계산하기 복잡할 때는 '마지막 해 - 첫해'로 계산합니다.
3. 0 : 납입 기한인데, 일시불로 투자를 시작했으니 0으로 합니다. 
4. -시작 값 : 시작 값에 - 부호를 붙입니다. 보통 투자 첫 자금이라고 생각하면 됩니다.  
5. 마지막 값 : 마지막 산출금액을 적습니다. 

 

위에 함수 배열대로 값을 넣어주면 =RATE(17년, 0, -10000000원, 45785068원) 코스피의 17년간 연평균성장률은 한 해 평균 9.36%라는 값이 나오게 됩니다.

 

투자 기간 이해하기

보통 퀀트데이터를 산출할 때, 연초, 연말로 나눠서 값을 따로 적지는 않습니다. 제가 표에서 연초, 연말 금액을 따로 적은 이유는 연도수에 대한 헷갈림을 설명하기 위해서입니다. 2003년부터 2020년까지는 17년의 세월이 흐른 것인데, 코스피 수익률이 적힌 건 18칸입니다. 그렇기 때문에, RATE함수에 18년을 적어야 된다고 착각할 수 있습니다. 이는 틀린 생각입니다.

 

위의 표의 전략은 1월 1일 매수 - 12월 31일 매도 전략입니다. 즉 1년이 되지 않은 시점에 모든 거래가 이루어진 것입니다.만약 연초라는 말을 지우고 11월 30일이라고 적는다고 가정해 봅시다. 그럼 29.19%의 수익률은 2003년에 발생한 수익률이기는 하나 1년간의 수익률은 아닙니다. 1달간의 수익률입니다. 만약 이런 상황에 CAGR을 구한다면, 1달간 수익을 얻고 다음 연도 11월 30일까지 현금으로 홀딩이라는 전략을 구사한 것이라고 생각해볼 수도 있습니다.

 

CAGR은 각 연도별 평균 성장률을 구하는 것이기 때문에 왼쪽에 적힌 해당 연도 수익률의 개수와는 약간 다른 개념입니다. 이 설명 역시 헷갈린다면 복잡하게 생각할 것 없이, 투자를 마친 마지막 연도  -  투자를 시작한 연도로 (2020-2003=17년) 계산해서 활용하면 됩니다.  

 

2. 기하평균 공식으로 CAGR 구하기 

RATE 함수는 일정 금액을 정기적으로 납입하고 일정한 이자율이 적용되는 연금 투자의 이자율을 계산하는 함수이기 때문에, RATE함수를 이용한 CAGR 계산은 약간 편법이라고 볼 수 있습니다. 그래서 기하평균 공식도 알고 있으면 좋을 것 같아서 다룹니다. 

 

엑셀 기하평균 공식은 다음과 같습니다. *엑셀에서 ^ 표시는 자승 부호입니다. 

• =(마지막 값/처음 값)^(1/투자기간)-1

위의 식대로 구해보면 CAGR값이 나옵니다.

• =(45785068/10000000)^(1/17)-1
• =0.09361915807
• =9.36%

 

이상으로 엑셀로 CAGR값을 구하는 RATE함수와, 기하평균 공식에 대해서 알아봤습니다. 퀀트 투자 백테스팅 시에 꼭 필요한 공식들이니 잘 숙지하셔서 투자에 도움이 되시길 바랍니다.