LiteFolio 팀입니다. 주식 포트폴리오를 엑셀로 관리하고 싶은데 어떤 열을 만들어야 할지, 평균 매수 단가는 어떻게 계산해야 할지 모르겠다는 고민을 자주 듣습니다. 결론부터 말하면, 거래 기록 시트와 보유 현황 시트를 나누고 필요한 열을 최소한으로 좁히면 투자 기록용 엑셀은 직접 만들 수 있습니다. 이 글에서는 시트 설계부터 수식, 그리고 직접 만든 엑셀 운용이 힘들어졌을 때의 선택지까지 구체적으로 설명합니다.
기록의 수고를 줄이고 싶은 분들을 위해 수동 입력형 기록 앱 「LiteFolio」를 이용할 수 있습니다
LiteFolio 자세히 보기 →엑셀 관리는 “시트를 나누는 것”에서 시작한다
주식 포트폴리오를 엑셀로 관리할 때 가장 먼저 정해야 할 것은 “거래 기록 시트”와 “보유 현황 시트”를 나누는 것입니다. 이유는 단순합니다. 한 시트에 매수·매도·보유 현황을 전부 몰아넣으면 행이 늘어날 때마다 집계식이 무너지기 쉽기 때문입니다.
예를 들어 A종목을 3번에 나눠서 사고 한 번 팔았다고 합시다. 이 이력을 한 시트에 시계열로 그대로 적어 나가면, “지금 얼마나 가지고 있는지” 알기 위해 매번 직접 해당 행을 눈으로 찾아 더하고 빼야 합니다. 이래서는 종목 수가 늘어날수록 감당할 수 없게 됩니다.
그래서 거래 기록 시트에는 “언제 무엇을 했는가”만 시계열로 추가하고, 보유 현황 시트에는 “지금 어떻게 되어 있는가”를 수식으로 자동 계산하는 식으로 역할을 나눕니다. 이 2개 시트 구성이 엑셀 투자 기록의 기본형입니다.
거래 기록 시트에 필요한 열 구성
거래 기록 시트에 필요한 열은 날짜·종목·매매구분·수량·단가·수수료 6가지입니다. 이유는 이 6가지 항목만 있으면 평균 매수 단가도 손익도 나중에 계산할 수 있기 때문입니다. 반대로 이보다 늘리면 입력 수고가 늘어나 지속하기 어려워집니다.
구체적인 열 예시는 다음과 같습니다.
매매구분은 “매수”, “매도” 문자열로 넣는 방법과, 매수를 +1, 매도를 -1로 하여 수량에 부호를 주는 방법이 있습니다. 후자로 해 두면 뒤에서 다룰 집계식이 단순해지므로, 익숙하지 않다면 D열을 “구분”, E열의 수량에 부호를 주는 방식을 추천합니다.
배당·입출금·분할도 같은 거래 기록 시트에 “거래 종류” 열을 하나 추가해 섞는 방법과, 별도 시트로 나누는 방법이 있습니다. 건수가 적을 때는 종류 열만으로도 충분하지만, 나중에 배당만 따로 집계하는 것이 번거로워지는 점은 뒤에서 다룹니다.
여러 증권사 계좌를 쓰는 경우는 “계좌” 열을 추가한다
여러 증권사 계좌에서 같은 종목을 보유하고 있다면, 거래 기록 시트에 “계좌” 열을 추가해 두면 나중에 계좌별·전체별로 모두 집계할 수 있습니다. 열을 추가하지 않고 계좌별로 시트를 나누면, 전체 포트폴리오를 보기 위해 매번 시트를 오가야 합니다.
여러 증권사 계좌를 한 번에 관리하는 방법 보기 →보유 현황 시트에서 평균 매수 단가와 손익을 구하는 수식
보유 현황 시트의 목적은 종목별로 “현재 보유 수량”, “평균 매수 단가”, “평가 손익”을 자동으로 표시하는 것입니다. 이것이 가능해지면 거래 기록 시트에 한 행을 추가하기만 해도 보유 현황이 갱신되는 구조가 완성됩니다. 먼저 기본 개념을 단순한 수식으로 짚어 보겠습니다.
평균 매수 단가의 기본형
평균 매수 단가는 “매수에 들어간 총액 ÷ 매수한 총수량”으로 구합니다. 3번에 나눠서 산 경우의 개념은 다음과 같은 가중평균 형태가 기본입니다.
이를 시트 위의 수식으로 만들면 종목별 합계 금액을 합계 수량으로 나누는 형태가 됩니다. 종목코드로 필터링하면서 합산할 때는 SUMIF()를 조합하는 것이 기본입니다.
여기서 주의할 점은 매도분까지 포함해서 나누지 않는 것입니다. 매도는 수량이 줄어들 뿐, 평균 매수 단가 자체는 (일부 매도인 경우) 변하지 않습니다. 매매구분별로 범위를 나눠 집계하거나, “구분=매수” 조건을 반드시 SUMIF에 넣어야 합니다.
더 자세한 계산 패턴(일부 매도 시의 처리나 여러 번의 매수가 섞이는 경우의 개념)은 평균 매수 단가 계산 방법에서 따로 다룹니다.
실현 손익의 기본형
매도 시 실현 손익은 “매도 금액 −（평균 매수 단가 × 매도 수량）− 수수료”로 구합니다. 매도한 행에 대해 그 시점의 평균 매수 단가를 참조하는 수식을 짜 두면, 매도할 때마다 손익이 자동으로 나옵니다.
배당 기록은 “종목·날짜·금액·세금”을 나눠서 남긴다
배당 기록은 종목·수령일·수령 금액·세액을 최소한의 열로 별도로 남기는 것이 기본입니다. 이유는 배당이 매수·매도와 수식 구조가 다르기 때문에, 같은 집계 로직에 섞으면 평균 매수 단가 수식이 무너지기 쉽기 때문입니다.
배당 기록 시트(또는 거래 기록 시트 안의 배당 전용 행 묶음)에는 다음 열이 있으면 연간 수령 배당을 집계하기 쉬워집니다.
연간 수령 배당 합계는 이 배당 기록 시트에 대해 SUMIFS()로 연도를 조건으로 걸면 집계할 수 있습니다. 배당 기록·관리에 대한 개념을 더 자세히 알고 싶다면 배당금 기록·관리 방법도 참고해 주세요.
직접 만든 엑셀이 지속되지 않는 4가지 한계
여기까지의 설계로 투자 기록 엑셀은 충분히 기능하지만, 운용을 계속하다 보면 스마트폰 입력·외출 중 열람·수식 손상·배당이나 분할 관리라는 4가지 한계에 반드시 부딪힙니다. 이는 엑셀을 잘못 만들어서가 아니라, 스프레드시트라는 구조상 피하기 어려운 문제입니다.
구체적으로는, 증권사 앱을 보면서 스마트폰으로 스프레드시트에 숫자를 입력하는 것은 열이 어긋나기 쉽고 시간도 걸립니다. 외출 중에 보유 현황을 바로 확인하고 싶어도, 클라우드 버전이라도 열기까지 탭 수가 많습니다. 행 삽입이나 범위 선택 실수로 SUMIF의 참조 범위가 어긋나 집계가 맞지 않게 됩니다. 주식 분할이 일어나면 과거 거래 기록 전체의 수량·단가를 수동으로 다시 조정해야 합니다.
이런 한계는 기록을 지속하려는 의지와는 무관하게 발생합니다. 수식이 깨진 줄도 모른 채 오래된 데이터를 계속 보게 될 위험도 있어, 정확도를 추구할수록 엑셀 관리의 유지 비용은 늘어납니다.
수식 유지보수에서 벗어나 수동 입력 기록에만 집중하고 싶은 분은 LiteFolio도 검토해 보세요
LiteFolio에 대해 자세히 보기 →엑셀 다음 선택지는 “수동 입력 앱”
엑셀의 한계에 부딪혔을 때의 선택지는 증권사와 연동하는 자동 취득형 앱으로 갈아타거나, 엑셀과 같은 “직접 입력한다”는 주도권을 유지하면서 집계만 자동화하는 수동 입력형 앱으로 옮기는 2가지입니다. 전자는 편하지만, 계좌 연동에 거부감이 있는 사람이나 여러 계좌·절세 계좌 등을 자신만의 분류로 관리하고 싶은 사람에게는 맞지 않습니다.
수동 입력형 앱이라면 엑셀에서 짰던 “날짜·종목·매매구분·수량·단가·수수료”라는 기록 항목을 그대로 이어갈 수 있습니다. 차이는 평균 매수 단가나 실현 손익의 계산식을 직접 짤 필요가 없고, 입력하기만 하면 자동으로 반영된다는 점입니다. 계좌 연동 없이 쓸 수 있는 앱의 개념은 증권사 연동 없는 자산관리 앱 고르는 법에서도 설명합니다.
엑셀로 하는 투자 기록은 거래 기록 시트와 보유 현황 시트를 나누고, 6개 열과 가중평균 수식으로 짜는 것이 기본형입니다. 먼저 직접 여기까지 만들어 보면 자신에게 정말 필요한 기록 항목이 보이기 시작합니다. 그 위에서 입력 수고나 수식 유지보수가 부담스러워지면, 기록 항목은 그대로 두고 집계만 자동화하는 수동 입력 앱으로 옮기는 것을 검토하는 순서가 자연스럽습니다.
LiteFolio
매수·매도·배당·입출금·수수료·주식 분할을 수동으로 기록하는, 증권사 계좌에 연결하지 않는 투자 기록 앱입니다. 엑셀과 같은 “직접 입력한다”는 자유로움을 유지하면서 평균 매수 단가·실현 손익·자산 배분 등의 집계를 자동화합니다. 데이터는 먼저 기기에 저장되는 로컬 우선 방식입니다. iOS와 Android에서 이용할 수 있습니다.
LiteFolio에 대해 자세히 보기LiteFolio는 기록·시각화 도구이며 투자 자문을 제공하지 않습니다.
자주 묻는 질문
엑셀과 구글 스프레드시트, 어느 쪽으로 관리해야 하나요?
시트 설계나 수식 개념은 어느 쪽이든 같습니다. 외출 중에 스마트폰으로 확인·추가하는 일이 많은 분은 구글 스프레드시트, PC 작업이 중심이고 수식을 정교하게 짜고 싶은 분은 엑셀이 적합합니다. 둘 다 수식 유지보수와 스마트폰 입력의 불편함이라는 한계는 공통적으로 발생합니다.
주식 분할이 있으면 엑셀은 어떻게 수정해야 하나요?
분할 비율에 맞춰 해당 종목의 과거 거래 기록 전체 수량에 분할 후 비율을 곱하고, 단가는 같은 비율로 나눕니다. 행 수가 많을수록 수작업 부담이 커지기 때문에, 분할이 잦은 종목을 보유하고 있다면 이 조정이 관리 비용의 큰 부분을 차지합니다.
여러 증권사 계좌를 한데 모아 엑셀로 관리할 수 있나요?
가능합니다. 거래 기록 시트에 “계좌” 열을 추가하고, 보유 현황 시트의 집계 조건에 계좌를 더하면 계좌별과 전체를 같은 시트에서 볼 수 있습니다. 자세한 내용은 여러 증권사 계좌를 한 번에 관리하는 방법에서 설명합니다.
LiteFolio는 무료로 사용할 수 있나요?
LiteFolio는 iOS와 Android에서 이용할 수 있습니다. 최신 요금제는 App Store 또는 Google Play에서 확인해 주세요. 클라우드 동기화 등 일부 기능은 유료 플랜(Pro)으로 제공될 예정이지만, 기본 기록 기능의 요금 체계는 현재로서는 확정되지 않았습니다. 최신 정보는 LiteFolio 페이지에서 확인해 주세요.