구글 시트로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C1] (골 시크 Goal Seek)

지난 몇 편의 글을 통해 울프럼 알파와 파이썬으로 적립식 투자 수익률에 해당하는 거치식 연평균 성장률(CAGR)을 추정하는 방법을 살펴보았습니다. 예를 들어 지난 5년간 적립식 투자로 원금 대비 100% 수익률을 얻었는데, 투자를 잘한 것인지 아닌지를 알고 싶을 수 있습니다. 거치식 CAGR로 환산해서 시장 수익률보다 높다면, 수익률이 좋았다고 자부할 수 있을 것입니다.

참고: 지난 4편의 글을 먼저 읽기를 권합니다. 구글 시트를 이용하는 방법으로 설명하지만, 기본 원리를 알면 이해하기 쉽고 필요한 경우 응용하기도 수월하기 때문입니다.

• 적립식 수익률을 거치식 수익률로 환산해서 비교하기 (울프럼 알파 vs 챗GPT + 제미나이 + 코파일럿)
• 거치식과 적립식 수익률을 서로 환산해 보자 (어떻게 추정하면 적절할까?) [파이썬 분석 부록 A2]
• 파이썬을 이용한 수치 해석 (적립식 수익률에 해당하는 거치식 성장률을 찾아보자) [파이썬 분석 부록 A3]
• 임의 적립식 투자 수익률에 해당하는 거치식 성장률을 찾아보자 [파이썬 분석 부록 A4]

주의: 이 글 및 이전 글은 모두 수익률만 비교합니다. 위험은 고려하지 않습니다. 엄밀한 의미에서 투자 성과 비교는 수익률뿐 아니라 위험도 함께 비교해야 공정합니다. 적립 시기와 적립금에 대한 데이터만 있는 경우, 투자 중간의 성과는 알 수 없기에, 현실적으로 위험을 비교할 수 없습니다.

주의: 이 글은 특정 상품 또는 특정 전략에 대한 추천의 의도가 없습니다. 이 글에서 제시하는 수치는 과거에 그랬다는 기록이지, 앞으로도 그럴 거라는 예상이 아닙니다. 분석 대상, 기간, 방법에 따라 전혀 다른 결과가 나올 수 있습니다. 데이터 수집, 가공, 해석 단계에서 의도하지 않은 오류가 있을 수 있습니다. 일부 설명은 편의상 현재형으로 기술하지만, 데이터 분석에 대한 설명은 모두 과거형으로 이해해야 합니다.

적립식 투자 내역 시트 정리

일반적인 투자자라면 구글 시트나 마이크로소프트 엑셀과 같은 스프레드시트를 이용하여 다음과 같이 적립식 투자 내역을 관리할 것입니다.

어떤 개인 투자자가 관리하고 있는 적립식 투자 내역

왼쪽 상단을 먼저 보겠습니다.

• 평가 일자: 계좌 평가액을 확인한 날자입니다. 2025년 5월 28일 글 작성 시점입니다.
• 투자 원금: 적립식으로 투자한 총원금입니다. =SUM(C9:C) 수식이 들어 있습니다.
• 계좌 평가액: 평가 일자의 계좌 평가액입니다. 개별 종목 평가 금액은 물론 예수금이나 배당금까지 모두 합산한 결과입니다. 한 계좌에 적립식으로 투자할 금액을 넣기만 하고 빼지만 않았다면 계좌 평가액과 동일합니다. 여러 계좌에 투자금을 나누어 사용한다면, 각 계좌 평가액의 합이 됩니다.
• 투자 수익률: 계좌 평가액 / 투자 원금 - 1입니다. =B3 / B2 - 1 수식이 들어 있습니다.
• 최대 투자 기간: 첫 투자일 기준으로 연단위로 환산한 투자 기간입니다. =MAX(E9:E) 수식이 들어 있습니다.
• CAGR: 적립식 CAGR입니다. 투자 수익률이 40%이니 =(1 + 40%)^(1 / 최대 투자 기간) - 1로 계산하면 됩니다. 수식으로는 =(1 + B4)^(1 / B5) - 1입니다.

오른쪽에 몇 가지 값이 설정된 셀이 있습니다.

• 연평균 일수: 투자 일수를 연단위로 환산하는 데 사용하는 연평균 일수로 365.25를 넣었습니다.
• 거치식 CAGR: 앞으로 구할 가상의 자산의 연평균 성장률입니다. 일단 10%로 두었습니다.
• 거치식 연평균 자산비: 거치식 CAGR + 1입니다. 거치식 CAGR을 10%로 두었으니 1.1이 됩니다. 수식은 =E4 + 1로 1.1입니다. 이 셀은 이후 계산 수식을 간단하게 표시하기 위해 거치식 CAGR 대신 사용합니다.

그 아래에 표 형식으로 된 적립식 투자 내역이 있습니다.

• 차수: 몇 번째 투자인지 기록한 일련번호입니다.
• 투자일: 투자 일자입니다.
• 투자금: 해당 투자일의 투자금입니다. 여기서는 만원 단위입니다.
• 투자 일수: 평가 일자에서 투자일을 빼서 일단위로 환산한 결과입니다. D9 셀의 경우 =B$1 - B9가 들어 있습니다.
• 투자 년수: 투자 일수를 연단위로 환산한 결과입니다. E9 셀의 경우 =D9 / E$1이 들어 있습니다.
• 예상 자산: 만일 해당 투자금이 거치식 연평균 자산비(여기서는 1.1)로 불어났다면, 예상되는 금액입니다. 같은 행에 있는 투자 금액에 거치식 연평균 자산비의 투자 년수 지수승과 곱한 것입니다. F9 셀이라면, =C9 * E$5^E9와 같이 쓰면 됩니다.

표 오른쪽 끝에 차이라는 셀(F7)이 있습니다. 예상 자산의 합에서 계좌 평가액을 뺀 금액입니다. 수식으로는 =SUM(F9:F) - B3이 들어 있습니다.

수동으로 연평균 성장률 찾기

빨간색 거치식 CAGR을 바꾸면, 그 아래 거치식 연평균 자산비가 바뀝니다. 이에 따라 표에 있는 각 행의 예상 자산도 함께 바뀝니다. 따라서 차이에 해당되는 F7 셀의 값도 바뀌게 됩니다. 다음은 거치식 CAGR을 5%와 15%로 바꾸어 본 결과입니다.

거치식 CAGR을 5%와 15%로 바꾸었을 때

관련된 셀의 값이 바뀌면서, 차이가 각각 -151과 -35로 바뀝니다. 앞의 10%도 포함하여 정리하면 (5%, -151), (10%, -96%), (15%, -35)입니다. 예상 자산의 합이 아직 700에 조금 모자라니 거치식 CAGR을 좀 더 높여도 될 듯합니다.

16%로 두면, -22, 17%로 두면 -8, 18%로 두면 6%가 나옵니다. 0이 되는 값 즉 예상 자산의 합과 계좌 평가액의 차이가 없는 거치식 CAGR은 17%와 18% 사이에 있을 것입니다. 0.1% 단위로 조정해 보면, 다음과 같이 17.6%로 설정하면 차이가 0이 됩니다.

차이가 0이 되는 거치식 CAGR

이 투자자의 적립식 투자 수익률은 CAGR이 17.6%인 자산에 투자한 것과 동일했다고 볼 수 있습니다. 이 기간 시장 수익률이 연평균 10% 정도였다면, 이 투자자의 투자 수익률은 시장보다 꽤 높은 편이라 할 수 있고, 시장 수익률이 연평균 20% 정도였다면, 시장보다 낮은 수익률을 얻었다고 볼 수 있습니다.

자동으로 연평균 성장률 찾기

수작업으로 한 거치식 CAGR 찾기는 앞에서 살펴본 수치 해석과 동일한 방식이라 보면 됩니다. 거치식 CAGR은 그 범위가 넓지 않고, 차이에 따라 증가시킬지 아니면 감소시킬지도 명확합니다 또한 단위도 0.1% 정도면 충분하기에 수작업으로 해도 큰 무리가 없습니다. 10 ~ 20번 정도 바꾸어 보면 원하는 결과를 얻을 수 있습니다.

이왕 하는 김에 자동으로 할 수 있는 방법도 소개합니다. 구글 시트의 경우 수치 해석 기법으로 최적화를 하는 기본 툴이 설치되어 있지 않습니다. 확장(extension) 프로그램으로 추가해야 합니다. 참고: 마이크로소프트 엑셀을 사용하는 방법은 다른 글에서 설명합니다.

다음과 같이 메뉴 > 확장 프로그램 > 부가기능 > 부가기능 설치하기를 선택해서 Goal Seek를 찾아 설치합니다.

구글 시트에서 Goal Seek 확장 프로그램 설치하기

이 확장 프로그램은 구글에서 개발한 것이니 안심하고 설치해도 됩니다. 설치 후 시트를 리프레시(F5 키)하면 방금 설치한 Goal Seek를 사용할 수 있습니다.

다음과 같이 메뉴 > 확장 프로그램 > Goal Seek > Open을 선택합니다.

구글 시트에서 Goal Seek 실행하기

몇 가지 입력 창이 나옵니다. 첫 번째 Set Cell은 차이가 들어 있는 F7 셀을 클릭하고 오른쪽 밭 전(田)자를 선택하면 입력이 됩니다. 두 번째 To Value는 차이를 0으로 만들 것이기에 0을 입력하면 됩니다. 세 번째 By Changing Cell은 수작업으로 한 것과 같이 거치식 CAGR이 든 E4 셀을 지정하면 됩니다.

다음은 세 값을 모두 입력한 결과입니다. 변화를 관찰하기 위해 거치식 CAGR은 다시 10.0%를 입력했습니다.

Goal Seek에 파라메터 입력하기

Goal Seek 하단 Solve 버튼을 클릭하면 Goal Seek가 거치식 CAGR을 이리저리 변경해 가며 차이가 0에 가까운 값을 자동으로 찾아줍니다.

최종 결과는 다음과 같습니다.

Goal Seek가 찾은 거치식 CAGR

Goal Seek가 찾은 거치식 CAGR은 약 17.5872%입니다. 수작업으로 찾은 17.6%와 거의 동일합니다.

정리하며

구글 시트를 이용하여 수작업 또는 자동으로 적립식 수익률을 거치식 CAGR로 환산하는 방법을 살펴보았습니다. 추정한 거치식 CAGR을 시장 수익률 또는 벤치마크와 비교하면, 본인의 적립식 투자 수익률이 시장 대비 어느 정도였는지 대략적으로 짐작할 수 있습니다.

해가 여럿 있는 경우, 수치 해석 결과로 엉뚱한 값이 나올 수 있습니다. 적립식 수익률을 거치식 CAGR로 환산하는 이 문제에서는 그럴 가능성이 극히 낮지만, 우연이 겹치면 발생할 수도 있습니다. 이런 경우에는 차이를 계산하는 F7 셀의 수식에 조건을 넣어 계산에 사용한 연평균 자산비가 어느 범위를 벗어나면 큰 값을 돌려주도록 다음과 같이 수식을 설정하면 됩니다.

=IF(E5 < 1, 10000, SUM(F9:F) - B3)

참고 서적:

• 왜 위험한 주식에 투자하라는 걸까? - 장기 투자와 분산 투자에 대한 통계학적 시각
• 구글 시트로 시작하는 투자 포트폴리오 분석 - 오렌지사과의 불친절한 워크북

이어지는 글: 엑셀로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C2] (목표값 찾기)

관련 연재: 순서대로 읽으시길 권합니다.

• 적립식 수익률을 거치식 수익률로 환산해서 비교하기 (울프럼 알파 vs 챗GPT + 제미나이 + 코파일럿)
• 거치식과 적립식 수익률을 서로 환산해 보자 (어떻게 추정하면 적절할까?) [파이썬 분석 부록 A2]
• 파이썬을 이용한 수치 해석 (적립식 수익률에 해당하는 거치식 성장률을 찾아보자) [파이썬 분석 부록 A3]
• 임의 적립식 투자 수익률에 해당하는 거치식 성장률을 찾아보자 [파이썬 분석 부록 A4]
• 구글 시트로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C1] (골 시크 Goal Seek) (본 글)
• 엑셀로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C2] (목표값 찾기)

연재 목록: 자산 배분 분석 방법 책 소개, 연재글 및 사례 모음 [목록]

함께 읽으면 좋은 글 (최신 글)

1. 임의 적립식 투자 수익률에 해당하는 거치식 성장률을 찾아보자 [파이썬 분석 부록 A4]
2. 파이썬을 이용한 수치 해석 (적립식 수익률에 해당하는 거치식 성장률을 찾아보자) [파이썬 분석 부록 A3]
3. 거치식과 적립식 수익률을 서로 환산해 보자 (어떻게 추정하면 적절할까?) [파이썬 분석 부록 A2]
4. 적립식 수익률을 거치식 수익률로 환산해서 비교하기 (울프럼 알파 vs 챗GPT + 제미나이 + 코파일럿)
5. [중급 24] 베이즈 정리와 추론 - 나주사씨의 친구들은 주사위를 어떻게 추정했을까? (고정된 수익률 분포를 완벽하게 아는 경우)

함께 읽으면 좋은 글 (인기 글)

1. 구글 제미나이(Gemini)를 이용한 그림 생성하기 (vs. 마이크로소프트 이미지 크리에이터의 이미지 생성 AI)
2. 챗GPT(ChatGPT)로 사진을 지브리 만화 스타일로 변환하기
3. 스튜디오 지브리 만화 스타일로 그림 그리기 (구글 제미나이)
4. 커버드콜과 노벨상 (커버드콜에 투자하면 안 되는 간단하고 명확한 이유 + 노벨상을 받는 손쉬운 방법)
5. 당신이 JEPI/JEPQ를 사면 안되는 이유 (해외 상장 인컴 ETF의 배당소득세와 양도소득세)