엑셀 연평균 성장률 계산해보기

엑셀에서 연평균 성장률 ( CAGR, Compound Annual Growth Rate )은 계산방법이 살짝 어렵다.

평균이라는 용어 때문에 average를 사용하면 될까? 라고 생각하지만 아니다

average() 함수로 평균을 구할 수 있는 평균은 합계를 개수로 나눈 산술 평균 값이다.

산술 평균 값은 시험 성적이나 월평균매출을 구할 때 사용한다.

 

 

 

연평균 성장률 = GEOMEAN(비율) - 1

연평균 성장률 = (마지막결과 / 첫번째결과)^(1/Y)-1

연평균 성장률 = RATE(Y, 0, -첫번째결과, 마지막결과)

 

Y : 마지막 연도에서 시작 연도를 뺀 기간이다.

GEOMEON : 기하 평균을 구하는 함수이다.

RATE : 이자 지급 기간 동안의 평균 이율을 계산하는 함수로 연평균 성장률을 계산할 때도 사용한다.

 

 

 

연평균 성장률 계산 시 평균이라는 용어 때문에 성장률의 산술 평균을 구하는 것은 잘못된 방법이다.

산술평균을 이용한 값은 성장률의 단순 평균일 뿐이므로 매년 일정한 비율로 성장했다고 할 수 없기 때문이다.

 

 

 

 

매년 성장률을 아래와 같이 계산하고, 성장률의 평균을 계산하면 틀렸다는 것이다.

 

산술평균을 통해 연평균 성장률 8.6%이 나왔다.

하지만, 연평균 8.6%을 적용할 경우, 2019년 매출은 4,305로 나오기 때문에 4,239가 달라서 틀렸다.

 

2014년도 매출 2,850에서 연평균성장률 8.6% 적용한 수식은 다음과 같다.

연매출 예상을 계산하려면 증가률을 알아야 하는데,

1 + 8.6% 를 하면 = 108.6% 증가률이 나온다.

 

 

 

그럼 2014년 매출 2,850 * 108.6%를 곱하면 된다.

곱하면 3,095가 나온다. 2015년도 매출이랑 다르다.

 

그러면 이 과정을 수식으로 하면

= 2014년도매출 * ( 1 + 연평균증가률 8.6% ) 이다.

이렇게 계산하면 2015년도 매출이 나올것이다.

2014년도부터 5년동안 성장을 지속했다면

= 2014년도매출 * ( 1 + 연평균증가률 8.6% ) * ( 1 + 연평균증가률 8.6%) * ( 1 + 연평균증가률 8.6% ) * ( 1 + 연평균증가률 8.6% ) *

( 1 + 연평균증가률 8.6% )

이렇게 5번 곱해야 한다.

 

결국은 = 2014년도매출 * ( 1 + 연평균증가률 8.6% )^5 로 표현 할 수 있다.

 

하지만 계산 결과는 4,305로 2019년 실제 매출인 4,239와 차이가 있다.

그러므로 산술평균을 이용해서는 연평균 성장률을 구할 수가 없다.

 

 

#2. 산술평균 함수 average() 가 아닌 기하평균을 이용하여
연평균 성장률 구하는 방법

 

 

 

 

여기서 보면, 비율과 성장률은 다른의미를 말한다.

비율은 = 이번값 / 지난 값 을 말하고,

성장률은 = (이번값 - 지난값) / 지난 값 을 말한다.

 

하지만 연평균 성장률에서는 성장률이 아닌 전년도 대비 비율로 먼저 계산해야 한다.

그 이유는 기하평균은 양수만 구할수 있는데, 성장률은 음수 값이 나올 수 있기 때문이다.

원래 성장률 계산방법은 = (이번값 / 지난값) - (지난값 / 지난값)으로 풀어 설명할 수 있다.

'이번값 / 지난값' 부분은 비율 계산식과 동일하다.

'자난값 / 지난값' 부분은 1로, '= 비율 - 1'과 같다는 것을 이해할 수 있다.

그러므로 항상 양수만 구하는 비율 값을 구한 후 1을 빼는 방식의 계산식을 적용해 기하 평균을 구하는 것이 바람직하다.

 

이제 기하평균에 대해서 계산해 보자.

 

 

기하평균은 = GEOMEAN(비율) - 1 로 계산하면 된다.

 

 

그리고 기하평균을 이용해서 2019년 매출을 구할 수 있다.

 

 

 

2019년 매출 = 2014년도매출 * (1 + 연평균성장률)^5

 

 

즉, 4,239로 2019년 매출과 동의하다.

이번에는 제대로 된 결과값이 나왔다.

 

 

 

그러면 2년후 2021년 예상 매출을 계산해 보자!!

 

기하평균으로 구한 연평균 성장률과 같이 지속적으로 성장한다고 하면, 2021년도 매출은

 

2021년도 매출 = 2014년도매출 * (1 + 연평균성장률)^(2021년도 - 2014년도)

 

 

 

위의 예상매출 수식으로 계산하면 2021년도 예상매출은 4,969가 나온다.

 

이해가 어렵다.

그러면 그냥 빨간색으로 칠해놓은것 따라하면 연평균성장률과 예상매출을 계산할 수 있다.