ETF 포트폴리오 수익률, 엑셀·구글시트로 직접 관리하는 법

이 글은 엑셀 사용법을 다룹니다. 특정 종목이나 투자 방식을 권하지 않으며, 투자 판단과 그 결과는 본인 책임입니다.

증권사 앱에도 수익률이 나옵니다. 그런데 계좌가 두 개 이상이거나, 분배금을 포함한 실제 수익률이 궁금하거나, 자산군별 비중을 직접 보고 싶으면 앱만으로는 부족합니다.

거래내역 시트 하나만 성실히 쌓아두면 나머지는 수식이 알아서 계산합니다. 이 글에서는 그 구조를 만드는 방법을 다룹니다.

1단계. 시트를 세 장으로 나누기

가장 흔한 실패는 한 시트에 전부 몰아넣는 것입니다. 그러면 매수할 때마다 표 구조를 손대게 되고, 결국 관리를 포기하게 됩니다.

거래내역 — 직접 입력하는 유일한 시트
종목별 집계 — 수식만 들어가는 중간 시트
대시보드 — 결과를 보는 시트

거래내역 시트의 열 구성은 이렇게 잡습니다.

날짜 | 종목코드 | 종목명 | 구분 | 수량 | 단가 | 수수료 | 금액

구분에는 매수·매도·분배금을 적습니다. 수량은 매수를 양수, 매도를 음수로 기록하세요. 이렇게 해두면 나중에 SUMIF 하나로 보유수량이 나옵니다. 조건을 나눌 필요가 없어집니다.

입력을 마쳤으면 Ctrl + T로 표로 만드세요. 거래가 늘어도 수식 범위가 자동으로 따라옵니다.

2단계. 현재가 가져오기

여기가 엑셀과 구글시트의 차이가 가장 큰 부분입니다.

구글시트 — GOOGLEFINANCE

=GOOGLEFINANCE("KRX:069500")

국내 상장 종목은 KRX: 뒤에 여섯 자리 코드를 붙입니다. 해외 상장은 =GOOGLEFINANCE("NYSEARCA:VOO")처럼 거래소 코드를 씁니다.

실시간이 아닙니다. 최대 20분 지연되며, 종목에 따라 데이터가 없을 수도 있습니다. 참고용으로만 쓰세요.

엑셀 Microsoft 365 — 주식 데이터 형식

셀에 종목명이나 티커를 입력하고 데이터 탭 → 주식을 누르면 셀이 연결된 데이터로 바뀝니다. 이후 =A2.Price 형태로 값을 꺼냅니다.

다만 국내 ETF는 인식되지 않는 경우가 많습니다. 해외 종목 위주라면 쓸 만하지만, 국내 ETF가 많다면 구글시트 쪽이 현실적입니다.

엑셀 2021 이하 — 수동 입력

위 기능이 없습니다. 종목별 집계 시트에 현재가 열을 만들어 직접 입력하고, 갱신한 날짜를 옆에 적어두세요.

불편해 보이지만 종목이 열 개 안팎이라면 월 1회 갱신에 2분이면 됩니다. 매일 볼 필요가 없는 데이터이기도 합니다.

3단계. 보유수량과 평균단가

종목별 집계 시트의 A열에 종목코드를 나열하고, 옆에 수식을 넣습니다.

보유수량 — 매수를 양수, 매도를 음수로 적었으므로 그냥 더하면 됩니다.

=SUMIFS(거래내역!$E:$E, 거래내역!$B:$B, $A2, 거래내역!$D:$D, "<>분배금")

총 매수금액 — 수수료를 포함한 실제 투입액입니다.

=SUMIFS(거래내역!$H:$H, 거래내역!$B:$B, $A2, 거래내역!$D:$D, "매수")

평균단가 — 보유수량이 0일 때 오류가 나므로 감싸줍니다.

=IFERROR(C2/B2, 0)

주의할 점이 있습니다. 일부를 매도한 뒤에는 이 평균단가가 실제와 달라집니다. 매도 시점의 취득원가를 빼주지 않았기 때문입니다. 매도가 잦다면 매도 건마다 원가를 따로 기록하는 열이 필요합니다. 매수만 계속 쌓는 적립식이라면 이대로도 정확합니다.

4단계. 평가금액과 손익

평가금액 =B2*E2
평가손익 =F2-C2
수익률 =IFERROR(G2/C2, 0)

수익률 셀은 표시 형식을 백분율로 지정하세요. 0.0873이 아니라 8.73%로 보여야 읽힙니다.

5단계. 분배금 반영하기

여기를 빼먹으면 실제보다 수익률이 낮게 나옵니다. ETF 분배금은 자동으로 반영되지 않습니다.

분배금이 들어올 때마다 거래내역에 한 줄 추가하세요. 구분은 분배금, 수량은 비우고 금액만 적습니다. 그리고 집계 시트에,

=SUMIFS(거래내역!$H:$H, 거래내역!$B:$B, $A2, 거래내역!$D:$D, "분배금")

이걸 포함한 총수익률은 이렇게 계산합니다.

=IFERROR((F2 + H2 - C2)/C2, 0)

평가금액에 누적 분배금을 더하고 투입액을 뺀 뒤 투입액으로 나누는 구조입니다.

6단계. 자산 비중 보기

종목별 집계 시트를 표로 만든 뒤 피벗테이블을 삽입합니다.

행 : 자산군 (국내주식 / 해외주식 / 채권 등 직접 분류한 열)
값 : 평가금액 합계

값 필드를 한 번 더 끌어다 놓고 값 필드 설정 → 값 표시 형식 → 총합계 비율을 선택하면 비중이 퍼센트로 나옵니다. 리밸런싱 판단에 쓰는 숫자입니다.

자주 막히는 지점 세 가지

① 해외 ETF의 환율 처리

달러로 산 종목을 원화로 환산할 때, 매수 시점 환율과 현재 환율을 구분해야 합니다. 매수 시점 환율을 거래내역에 열로 기록해두지 않으면 나중에 복원할 수 없습니다.

현재 환율은 구글시트에서 가져올 수 있습니다.

=GOOGLEFINANCE("CURRENCY:USDKRW")

② 종목코드가 텍스트라서 SUMIFS가 못 찾음

069500처럼 0으로 시작하는 코드는 숫자로 저장되면 앞자리가 날아갑니다. 반대로 한쪽은 숫자, 한쪽은 텍스트로 저장돼서 SUMIFS가 0을 반환하기도 합니다.

종목코드 열은 처음부터 셀 서식을 텍스트로 지정하고 입력하세요. 양쪽 시트 모두 해야 합니다.

③ GOOGLEFINANCE 값이 자꾸 바뀌어서 기록이 안 남음

이 함수는 열 때마다 다시 계산되므로 과거 시점 값이 남지 않습니다. 월말 잔고를 기록하려면 그때의 값을 값만 붙여넣기로 별도 시트에 쌓아두세요.

매월 한 줄씩 쌓이면 시간에 따른 자산 추이 그래프를 그릴 수 있습니다.

자주 묻는 질문

Q. 증권사 앱 수익률과 숫자가 다릅니다.

계산 기준이 다르기 때문입니다. 증권사는 보통 매도 시 취득원가를 차감한 실현손익 기준을 쓰고, 분배금은 별도로 표시합니다. 어느 쪽이 틀린 게 아니라 정의가 다른 것이므로, 본인 시트의 기준을 명확히 정해두는 게 중요합니다.

Q. 연평균 수익률(CAGR)도 계산할 수 있나요?

가능합니다. 다만 적립식처럼 투입 시점이 여러 번이면 단순 CAGR은 왜곡됩니다. 이 경우 XIRR 함수가 적합합니다. 거래 날짜와 현금흐름(투입은 음수, 현재 평가액은 양수)을 넣으면 연환산 수익률이 나옵니다.

Q. 세금까지 반영할 수 있나요?

계산은 가능하지만 국내상장 해외ETF와 해외상장 ETF의 과세 방식이 다르고, 금융소득종합과세 여부에 따라서도 달라집니다. 시트에서는 세전 기준으로 관리하고, 세금은 별도로 확인하시는 편이 정확합니다. 저는 세무 전문가가 아니므로 구체적인 판단은 국세청 자료나 세무 상담을 참고하세요.

Q. 자동으로 거래내역을 불러올 수 없나요?

증권사에서 거래내역을 엑셀로 내려받은 뒤 파워쿼리로 붙이는 방식이 현실적입니다. 다만 증권사마다 열 구성이 달라서 처음 한 번은 열 이름을 맞추는 작업이 필요합니다.

정리

시트는 입력 / 계산 / 출력 세 장으로 분리
매수는 +, 매도는 − 로 기록하면 SUMIF 하나로 끝
분배금을 빼먹으면 수익률이 실제보다 낮게 나옴
GOOGLEFINANCE는 지연 데이터 — 기록은 값만 붙여넣기로

관련 글

거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지
여러 시트 한 장으로 합치기, 복사 붙여넣기 말고 3가지 방법

본 글은 스프레드시트 활용법을 소개하는 내용이며 투자 권유가 아닙니다. 수식으로 계산된 값은 입력한 데이터와 시세 제공 서비스의 정확도에 따라 달라질 수 있으니, 실제 거래나 세무 신고 시에는 증권사 및 관련 기관의 공식 자료를 확인하시기 바랍니다.