엑셀에서 파이썬을 이용하여 혼합식 투자 수익률을 거치식 CAGR로 환산하기 [파이썬 분석 부록 B3]

주식투자

엑셀에서 파이썬을 이용하여 혼합식 투자 수익률을 거치식 CAGR로 환산하기 [파이썬 분석 부록 B3]

오렌지사과키위 2025. 5. 30. 18:28

이전 글에서 혼합식 투자 수익률을 거치식 CAGR로 환산하기 위해 파이썬을 사용하는 방법을 살펴보았습니다. 혼합식 투자는 거치식, 적립식, 인출식을 포함한 입출금에 특별한 제약을 두지 않는 투자 방식을 말합니다. 이전 글: 임의 인출식/혼합식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 (파이썬 수치 해석) [파이썬 분석 부록 A5]

엑셀에서 지원하는 파이썬으로 코드를 작성하면, 실행한 결과를 엑셀의 시트에 넣을 수 있습니다. 앞에서는 이러한 목적으로 엑셀의 목표값 찾기 기능을 사용했지만, 파이썬을 이용하면 시트의 데이터가 변경될 때마다 자동으로 재계산해 주기 편리합니다. 참고: 엑셀로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C2] (목표값 찾기)

계좌가 3개이고 각각에 대해 따로 계산해야 한다면, 더욱 편리하게 이용할 수 있을 것입니다. 엑셀에서 파이썬을 이용하는 방법에 대해서는 다음 두 편의 글을 참고하기 바랍니다.

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

파이썬 코드의 함수화

다음은 이전 글에서 작성한 파이썬 코드를 함수로 바꾼 것입니다.

def guess_cagr(_invest_l, _low = 0.5, _high = 2.0):
  _amount_l = _invest_l[:, 0]
  _years_l = _invest_l[:, 1]

  _step = 0.0001
  _rs = np.arange(_low, _high + _step, _step)
  _ys = np.array([sum(_amount_l * (r ** _years_l)) for r in _rs])

  _ind = abs(_ys).argmin()
  return (_rs, _ys, _ind)

invest_l = np.array([(100, 5),
                     (200, 4),
                     (0, 3),
                     (-50, 2),
                     (-100, 1),
                     (-300, 0)])

rs, ys, ind = guess_cagr(invest_l)

plt.axhline(0, ls = '--', c = 'tab:gray')
plt.axvline(0, ls = '--', c = 'tab:gray')
plt.plot(rs, ys)

plt.scatter(rs[ind], ys[ind], c = 'tab:red', label = f'r = {(rs[ind] - 1) * 100:.2f}% + 1')

plt.xlabel('$r$')
plt.ylabel('$y$')
plt.legend()
plt.show()

guess_cagr() 함수를 살펴봅니다.

def guess_cagr(_invest_l, _low = 0.5, _high = 2.0):
  _amount_l = _invest_l[:, 0]
  _years_l = _invest_l[:, 1]

  _step = 0.0001
  _rs = np.arange(_low, _high + _step, _step)
  _ys = np.array([sum(_amount_l * (r ** _years_l)) for r in _rs])

  _ind = abs(_ys).argmin()
  return (_rs, _ys, _ind)

투자 기록이 든 numpy 배열인 _invest_l과 r의 최소값인 _low, 최대값인 _high를 인자로 받습니다. r은 자산비이기에 찾고자 하는 값은 0.5 (CAGR -50%)와 2.0 (CAGR 100%) 사이에 있다고 보면 일반적인 경우 큰 문제가 없습니다.

실행 결과로 수치 해석에 사용한 r값의 목록인 _rs와 결과를 담은 _ys, 그리고 0에 가장 가까운 값이 든 색인을 돌려줍니다.

엑셀에서 시트를 참조하여 사용하기 위한 수정

엑셀의 파이썬은 시트의 데이터를 DataFrame 형태로 가져옵니다. DataFrame을 사용하도록 함수를 수정하거나, DataFrame을 numpy 배열로 바꾸어서 사용해야 됩니다. 여기서는 DataFrame을 numpy로 바꿔 기존 함수를 재사용해 보겠습니다.

다음은 어떤 투자자가 본인의 혼합식 투자 기록을 엑셀 시트로 정리한 화면입니다.

2020년 1월 1일에 100만원으로 첫 투자를 시작했고, 다음 해 2월 1일에 200만원을 추가로 투자했습니다. 첫 투자를 시작한 지 4년 6개월 정도 지난 2024년 7월 1일에 50만원을 인출하고, 6개월 뒤 100만원을 추가로 인출했습니다. 계좌 평가일인 2025년 5월 28일의 계좌 잔고는 300만원입니다.

단순하게 계산하면 300만원을 투자하고 150만원을 회수했으니 150만원을 순투자한 셈입니다. 계좌 잔고는 300만원이니, 수익률은 100%입니다. 최대 투자 기간 5.4년을 감안하면 13.68%의 혼합식 CAGR이 나옵니다. 이 수익률은 과장되었을 수도 있고, 과소 평가되었을 수도 있습니다.

해당 투자 수익률에 대응하는 거치식 CAGR을 넣을 E4 셀에 다음의 파이썬 코드를 입력합니다.

def guess_cagr(_invest_l, _low = 0.5, _high = 2.0):
  _amount_l = _invest_l[:, 0]
  _years_l = _invest_l[:, 1]

  _step = 0.0001
  _rs = np.arange(_low, _high + _step, _step)
  _ys = np.array([sum(_amount_l * (r ** _years_l)) for r in _rs])

  _ind = abs(_ys).argmin()
  return (_rs, _ys, _ind)

cur_date = xl("B1")
balance = xl("B3")
days_in_year = xl("E1")
invest_df = xl("A8:C12", headers = True)
invest_df.columns = ['Sequence', 'Date', 'Amount']
invest_df['Years'] = (cur_date - invest_df.Date).dt.days / days_in_year

invest_l = invest_df[['Amount', 'Years']].to_numpy()
invest_l = np.append(invest_l, [[-balance, 0]], axis = 0)

rs, ys, ind = guess_cagr(invest_l)
rs[ind] -1

guess_cagr() 함수는 이전과 동일합니다.

cur_date = xl("B1")
balance = xl("B3")
days_in_year = xl("E1")
invest_df = xl("A8:C12", headers = True)
invest_df.columns = ['Sequence', 'Date', 'Amount']
invest_df['Years'] = (cur_date - invest_df.Date).dt.days / days_in_year

B1 셀에서 평가 날짜를, B3 셀에서 계좌 평가액을, E1 셀에서 연평균 일수를 가져와서 각각 cur_date, balance, days_in_year 변수에 담았습니다.

A8:C12 셀 범위에 있는 투자 기록은 칼럼명과 함께 가져와서 invest_df에 넣고, 칼럼명을 모두 영문으로 바꾸었습니다. 평가 일자에서 각각의 투자 일자를 빼서 .days로 날짜 차이를 일수를 구했습니다. 이를 연평균 일수로 나누면 각 투자의 투자 년수가 됩니다.

모든 툴이 그렇지만, 파이썬도 쓰다 보면 별 것 아닌 것에 한참 헤매는 경우가 있습니다. 제 경우 위 코드 마지막에 있는 .dt.day를 때문에 1시간 이상 헤맸습니다.

Pandas에서 두 날짜를 빼면 Timedelta 타입이 됩니다. 두 날짜의 시간 간격을 나타내는 타입입니다. Timedelta 변수 하나를 일수로 변환하려면 .days를 쓰면 됩니다. cur_date - invest_df.Date를 계산하면 Timedelta가 나열된 일종의 배열인 Series가 됩니다. Series 각각의 값에 대해서 날짜로 변환하려면 .days를 바로 쓸 수 없고, .dt.days를 사용해야 합니다. dt는 .days와 같이 ()이 없는 함수가 아닌 값을 가져올 때 각각에 원소에 적용하는 기능입니다.

왜 이 부분이 헷갈렸냐면, 비슷한 배열 형태라도 TimedeltaIndex와 같이 색인으로 사용하는 경우에는 .dt 없이 바로 .days를 쓰기 때문입니다. 대부분의 투자 관련 데이터에는 날짜가 있고, 날짜를 색인으로 사용합니다. 그러니 (cur_date - invest_df.index).days와 같이 사용합니다. 이 예에서는 날짜를 색인으로 지정하지 않았기에 .dt.days를 사용해야 합니다.

invest_l = invest_df[['Amount', 'Years']].to_numpy()
invest_l = np.append(invest_l, [[-balance, 0]], axis = 0)

invest_df에서 Amount와 Years 칼럼을 꺼내 invest_l이라는 numpy 배열을 만들었습니다. 계좌 평가일 계좌 잔고인 balance를 np.append()로 맨 마지막에 -balance 값으로 넣었습니다. 최종 평가일에 파악한 계좌 평가액이기 때문에 투자 기간은 0으로 넣습니다.

실행하면 다음과 같이 거치식 CAGR 9.4%에 해당된다고 나옵니다. 단순하게 계산한 13.68%와는 꽤 차이가 납니다.

데이터를 수정하면 파이썬 코드가 자동으로 실행되어 재계산됩니다.

왼쪽은 2025년 2월과 3월에 각각 50만원씩 투자한 기록을 추가하고, 계좌 평가액을 420만원으로 수정한 결과입니다. 데이터를 수정하면 엑셀이 시트를 재계산해 주듯, 파이썬 코드를 다시 실행해서 결과를 업데이트해 줍니다. 단순 계산한 CAGR은 10.08%로 거치식 CAGR 10.3%와 비슷합니다.

오른쪽은 2025년 4월과 5월에 각각 100만원과 50만원을 인출하고, 계좌 평가액을 280만원으로 고친 결과입니다. 단순 계산한 CAGR 24.01%와 거치식 CAGR 12.9%는 두 배 정도로 큰 차이가 나는 경우입니다.

정리하며

엑셀에서 혼합식 투자 수익률에 대응하는 거치식 CAGR을 계산하기 위하여 파이썬을 활용하는 방법을 살펴보았습니다. 엑셀에서 데이터를 수정하면 자동으로 재계산해 주기에 목표값 찾기 기능을 사용하는 것보다 훨씬 간편합니다.

구글 시트의 경우 Google Apps Script라는 자바 스크립트 기반의 프로그래밍 언어를 제공합니다. 자바 스크립트에 익숙한 사용자라면 이를 이용하여 같은 기능을 수행하는 코드를 구글 시트용으로 작성할 수 있을 것입니다.

참고 서적:

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

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

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

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

저작자표시 비영리 변경금지 (새창열림)

'주식투자' 카테고리의 다른 글

커버드콜 유지 비용은 얼마나 될까? (높은 기본 수수료를 부과하는 국내 상장 커버드콜 ETF들) (0)	2025.06.02
혼합식 투자의 거치식 투자 환산을 위한 수치 해석을 기하학적으로 설명해 보자 [파이썬 분석 부록 A6] (0)	2025.06.01
해외 자산을 기초자산으로 하는 국내 상장 커버드콜 ETF의 성과 비교 목록과 해석 (0)	2025.05.31
국내 자산을 기초자산으로 하는 커버드콜 ETF의 성과 비교 사례와 해석 (0)	2025.05.31
임의 인출식/혼합식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 (파이썬 수치 해석) [파이썬 분석 부록 A5] (0)	2025.05.30
엑셀에서 파이썬으로 그래프를 그려보자 (+한글 사용) [파이썬 분석 부록 B2] (0)	2025.05.29
엑셀에서 파이썬을 사용해 보자 (기초 사용법과 예제) [파이썬 분석 부록 B1] (0)	2025.05.29
엑셀로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C2] (목표값 찾기) (0)	2025.05.29

도움이 되었다면, 이 글을 친구와 공유하는 건 어떻까요?

현재글엑셀에서 파이썬을 이용하여 혼합식 투자 수익률을 거치식 CAGR로 환산하기 [파이썬 분석 부록 B3]

오렌지사과의 사진과 투자