엑셀(Excel)을 활용한 내 주식계좌 누적수익률 계산법 V2.0 사용설명서

엑셀로 주식계좌 누적수익률을 계산하는 방법을 업데이트했던 게 지난 2021년 4월의 일이었고, 그 이후로 새로운 버전을 올리지 않았습니다. 별다른 뜻이 있었던 것은 아니고... 딱히 버전을 올릴만한 꺼리가 없었기 때문입니다.

 

그러는 동안에 부족하나마 엑셀로 나만의 수익률을 계산하고자 하는 분들이 소소하게 블로그를 방문해 주셨었고 이렇게 오랜기간 무언가(?)를 하지 않는 것은 예의가 아니라는 생각이 들면서 귀차니즘을 뿌리치고 글을 적습니다.

 

사실 그동안 제 개인적으로는 월별로 올리는 투자일지를 기록하면서 소소하게 기능 업데이트를 하기는 했었습니다. 매달 반복되는 작업들이 귀찮아져서 일자를 입력하고, 수익률이 계산되면 그래프 정도는 자동으로 그려지는 나름 반자동스러운 엑셀로 변신시켜서 사용하고 있었습니다.

 

이런 기능을 탑재하고 많은 분들께 공유를 할까도 생각했지만 너무 없어보인다고 (혼자) 생각해서 그러지 않았는데... 이런 쓸모없어 보이는 기능도 수익률 계산식을 고민하며 기록해나가고 계실 분들이 분명 있다고 생각하여 업로드를 결정했습니다.

 

이름하여 반자동 에디션!! 이라고 명명하면 될까요? ㅋㅋㅋㅋㅋ

 

사실 구글스프레드시트의 그 화려함과 첨단스러운 자동화가 가능한 것도 아니고, 제가 사용하는 기능들이 대박대박이랄 것도 없습니다. 하지만 저처럼 아날로그를 사랑하고 손수 기록하시는 것을 즐기시는 분들은 솔깃하실만한 내용일 겁니다. (제가 그랬으니까요. ㅋㅋㅋㅋ)

 

지금도 대부분 제가 구현한 기능들은 구글검색창에서 관련키워드를 넣고 찾아보시면 우수한 강좌들과 함께 쉽게 구현할 수 있도록 친절히 안내되어 있습니다. 저도 그렇게 기능을 하나씩 둘씩 첨가했습니다. 사용하시면서 내가 무언가가 아쉬워 추가하고 싶으신 분들은 구글을 적극적으로 활용하시길 조언합니다. 구글에는 진짜 없는 것 빼고 다 있습니다. 상상하시는 그 모든 것이 가능할 겁니다. 아마도..

 

그럼.. 본격적으로 진짜 오랜만의 기능 업데이트를 설명드리겠습니다. 천천히 따라오시면 크게 어렵지 않으실겁니다.

 

 

 

 

 

누적수익률 계산법 V2.0 설명서 목차

 

 

 

 

엑셀파일 공유(누적수익률 계산법_v2.0)

(희주의 놀이터) 주식계좌 누적수익률 계산법_v2.0(2022_08_14).xlsx

4.03MB

 

 

 

<주요 업데이트 사항>

1. 각종 그래프, 표 반자동화 feat. 이름정의, 동적차트

  - 수익률(그래프, 표), 포트비중, 투자지표

2. 연도별 수익률 모음 자동화

3. 종목별 상관관계

 

크게 정리하니 위의 2가지로 나뉘네요. (거창하게 몇 개 있을 줄 알았더니 별거 없네요.....) 그래도 1번의 반자동화는 정말 정말 편리합니다. 맨 처음 수식을 잘만 정의해두면 두고두고 써먹을 수 있습니다.

 

 

 

1. 이름정의를 사용한 엑셀 반자동화

엑셀의 강력한 기능이라고 할까요? 특정 위치, 수식, 범위 등을 이름정의라는 기술(?)로 참조할 수 있습니다.

말이 필요 없습니다. 직접 엑셀 파일을 보면서 확인하시면 더 쉽습니다.

 

종합 자산현황 그래프 그리기

 

자산현황 그래프를 매달 업데이트하려면 항상 데이터관리에 들어가서 Y값의 범위를 최근 일자로 갱신해줘야 합니다.

물론 이게 뭐 어때서?? 라고 하실 수도 있는데 하나두개만 수정하면 할만합니다. 하지만 자산현황만 해도 눌러야 할 곳이 세군대(누적입출금, 평가손익, 수익률)나 되고, 누적수익률 그래프도 5개의 지표가 있으니 요것도 5번.. 그리고 제가 매달 기록하는 일지에는 포트폴리오를 총 6가지로 나눠놔서 바꿔야 할 부분이 너무 많습니다.

 

이게 은근히 소모적이고 시간을 많이 잡아먹더라구요. 몇 달은 이렇게 잘하다가 안 되겠다 싶어서 엑셀은 분명 이런 기능이 있을 거야라고 검색하니 이름정의를 활용하면 수식을 새로 입력하는 대로 그래프로 바로 적용할 수 있는 방법이 있었습니다.

 

처음에는 좀 헷갈리실 수 있는데 천천히 따라오시면 자유자재로 응용해서 사용하실 수도 있을겁니다.

 

 

 

가. OFFSET 함수 이해하기

https://m.blog.naver.com/villamoa003/220845623270

엑셀 OFFSET 함수 이해와 활용 (마지막 값 변환하기)

 

OFFSET 이라는 함수가 핵심입니다. 자세한 사항은 위의 블로그에서 잘 정리되어 있으니 참고하시면 좋을 것 같고, 간단하게 이것만 기억하면 됩니다.(위의 블로그 글을 아래로 쭉 내려보시면 다양한 응용버전이 나와있습니다. 정말 좋은 블로그입니다. 꼭 방문해보세요!!)

 

엑셀의 함수들은 사용 규칙이 다 정해져 있습니다. OFFSET은 총 5가지의 옵션을 입력할 수 있습니다.

 

OFFSET(① 시작위치,② 행이동,③ 열이동,④ 행선택셀수,⑤ 열선택셀수)

 

① 시작위치: 참조를 시작할 위치입니다. 위 그래프의 누적출입금의 경우는 E3 셀이 되겠죠?

② 행이동: 기준점에서 몇번째 행으로 갈지 정하는 것입니다. 우리는 E3부터 포함해야하니 0을 입력하면 됩니다.

③ 열이동: 기준점에서 몇번째 열로 이동할지 정하는 것이고, 역시 0이면 되겠습니다.

④ 행선택셀수: 기준점부터 몇행까지를 선택할지 정하는 부분입니다. 밑에서 더 추가설명을 해야겠지만 COUNTA 함수를 사용할 겁니다.

⑤ 열선택셀수: 기준점부터 몇열까지 선택할지 정합니다. 우리는 보통 1개 열만을 쭉 선택할 예정이라서 1을 자주 쓰실 듯합니다.

 

일단 설명은 해놨고 저도 이것만 봐서는 이해하기가 어려웠습니다. 바로 엑셀에서 사용해 보시는 게 가장 빠릅니다.

 

OFFSET(종합!＄E＄3,0,0,COUNTA(종합!＄E:＄E)-1,1)

 

해석하면: 위치를 참조할 건데 종합시트의 E3열(누적입출금)에서 시작하고, 행이나 열은 이동하지 않고(0,0) 제자리를 기준으로 종합 E의 모든열에서 비어있지 않은 셀의 숫자만큼에서 1개를 뺀만큼의 행 + 1개열을 선택하겠다.. 입니다.

 

 

 

나. COUNTA 함수 한스푼 더하기

말로 하니 되게 어려운데 구체적인 그림과 설명에 앞서 COUNTA 라는 함수가 추가로 등장하니 요것도 알아보고 가야합니다.

 

https://www.oppadu.com/%EC%97%91%EC%85%80-counta-%ED%95%A8%EC%88%98/

엑셀 COUNTA 함수 사용법 및 응용공식 :: 통계함수 - 오빠두엑셀

 

COUNTA 는 지정해준 범위에서 '비어있지 않은 셀'의 숫자를 세는 함수입니다. 우리는 누적수익률이나 평가손익, 수익률 등을 처음부터 끝까지 지정하고 싶고, 내가 내용을 늘릴 때마다 알아서 갯수 계산이 되어야 합니다.

 

그래서 위에서 사용한 OFFSET 함수의 행선택셀수에서 사용한 COUNTA의 의미는 종합시트의 E열 전부에서 비어있지 않은 셀=데이터가 입력된 셀이 몇 개인지 세고 그 숫자만큼 행을 선택하겠다는 것입니다.

 

그런데 맨뒤에 -1을 붙여줬죠? 그 이유는 2행에 있는 텍스트로 된 곳도 비어있지 않기 때문에 갯수에 포함이 되니 임의로 제거해주려는 의미입니다.

 

 

 

다. 다시 반자동화를 위한 이름정의

내가 입력해야 할 OFFSET 함수도 완성이 되었으니 이름정의로 참조할 범위를 지정만 해주면 끝입니다.

 

사용할 수식: OFFSET(종합!＄E＄3,0,0,COUNTA(종합!＄E:＄E)-1,1)

 

이름정의 사용방법

 

수식 탭에 가면 이름관리자가 있습니다. 클릭하면 신규 등록이 가능하고 이름은 원하는 대로 정하면 됩니다. 저같은 경우는 나중에 자료가 많아져서 포트폴리오 계좌별로 구분해서 사용하고 있습니다.

 

제가 실제로 사용하는 투자일지의 이름관리자 목록(엄청 많이 써야합니다..)

 

사용하시는 여러분도 아마 계좌를 다양하게 사용하고 계실 테니 응용하셔서 이름정의는 붙이기만 하면 됩니다.

 

이제 "누적수익률"이라고 하는 이름참조가 생겼습니다. 어느 수식에서든 이제 이 참조범위를 가져와서 사용할 수 있습니다.

 

누적수익률이 선택된 영역확인

 

이름정의된 누적수익률을 눌러보면 E열이 E3열부터 E613열까지 잘 선택되어 있음을 확인할 수 있습니다. 같은 방법으로 필요한 수식을 모두 입력합니다. (예시 파일에 이름정의를 다 입력해 둘 테니 공부하실 때 눌러보시면서 참고하시기 바랍니다. 직접 이리저리 해보셔야 온전히 자기 것이 됩니다.)

 

 

 

라. 그래프에 최종 적용

이름 정의까지 하면 끝나는 것은 아니고 이제 실제 그래프에 적용을 해야 합니다. 최초에 한번만 적용해 두면 이제부터 건드릴 일은 없습니다.

 

https://m.blog.naver.com/podosh_ssam/220679612731

[#56 기본 엑셀강의] 차트작성 - 동적차트(1)

 

동적차트라는 좀 더 세련된 이름으로 불리고 있는 바로 요겁니다. 데이터가 추가될 때마다 연계되어 유동적으로 변한다고 해서 붙여진 이름이네요. 찰떡이름인 것 같습니다. (엑셀은 보면 볼수록 대단합니다. 진심.. 내가 쓸 줄 몰라서 그런 거지 다 준비되어 있음..)

 

이름정의 그래프에 적용하기

 

그래프를 오른클릭하면 데이터관리가 나오고, 먼저 가로축은 일자 모음이니까 '=종합!일자'를 입력하고 Y값에는 '=종합!누적입출금'을 입력하고 확인을 하면 끝입니다. 나머지는 용량이 부족해서 움직이는 파일을 못 만들었는데 평가손익과 수익률도 마찬가지로 입력해줍니다.

 

이름정의를 각 그래프 Y값에 입력함

 

위처럼 입력하면 아래처럼 그래프가 알아서 잘 그려집니다.

 

이름정의가 적용된 자산현황 그래프

 

그럼 잘 적용되는지 데이터를 입력해 봐야겠죠? 종합시트는 앞의 국내, 미국ETF 시트에 값을 잘 입력만 하면 그냥 긁으면 수익률이 표시되게 되어있습니다. 쭈욱 드래그로 긁어내리기만 하면 됩니다.

 

알아서 변하는 그래프

 

그냥 아래로 쭈욱 셀을 복사했을 뿐인데 그만큼 늘어나는 그래프입니다. 뭔가 묘하게 찌릿?? 하지 않으신가요? ㅋㅋㅋㅋ

 

 

 

마. 조금은 다른 투자지표 자동화

투자지표에서 기간수익률을 계산하는 경우는 100으로 환산한 수익률 열에서 '맨 마지막 일자의 수익률/맨 처음 일자의 수익률-1'로 계산하고 있습니다. 지금까지 활용했던 OFFSET처럼 하면 전체기간동안의 수익률이 다 선택되기 때문에 조금 변형을 시켜줘야 합니다. 원리는 똑같고 간단합니다.

 

OFFSET(종합!＄N＄3,COUNTA(종합!＄N:＄N)-2,0)/＄N＄3-1

 

N열은 100을 시작점으로 계산한 희주의 수익률입니다. OFFSET의 사용조건에 따라서 우리는 N3열을 시작점으로 데이터가 있는 갯수만큼 행을 이동하고 그 셀을 선택만 하면 됩니다.

 

-1이 아니라 -2인 이유는 N3을 시작점으로 잡았기 때문에 데이터가 있는 갯수만큼 이동하면 맨 마지막 값이 아니라 그보다 하나 더 간 빈셀을 선택하게 됩니다. (심심하신 분들은 한번 해보시길.. ㅎㅎㅎ)

 

기간수익률은 이름정의를 굳이 사용할 필요는 없습니다.(해도 무방하긴 합니다만..) 그냥 셀에 위의 함수를 그대로 적으면 끝입니다. 그래프처럼 자료가 추가될 때마다 자동으로 업데이트가 됩니다. 다른 지표들(연평균수익률, 최대낙폭, 무위험초과수익률, 무위험초과수익률 변동성, 샤프지수, 총투자일수)의 수식을 변경시켜두었습니다.

 

꽤 많은 내용이라 엑셀 수식을 살펴보시면 될 것 같네요.

 

 

 

2. 수익률 모음 (반)자동화

완전히 자동화는 못하고 매월 투자일자 정도만 입력해주면 됩니다. 사실 큰 틀에서는 OFFSET 함수를 이용하는 거라서 따로 카테고리를 뺄만한 것은 아니지만 워낙 많은 것이 변화되어서 구분하는 차원에서 따로 제목을 빼놨습니다.

 

OFFSET 함수에 대해서 또 이야기하면 입만 아프니까 캡쳐본 하나를 예시로 넘어가 봅니다.

 

수익률 모음 표에 적용된 OFFSET 함수

 

특별하게 있는 건 아니고 매월 투자일만 BJ열에 입력을 해주기만 하면 됩니다. 2023년이 아직 오지는 않았지만 수식을 조금 변경해서 계속 사용하시면 되겠습니다.

 

수익률 반자동화(예시)

 

매월 해주실 일은 투자일(셀) 부분에 실제 투자일을 입력하기만 하면 끝입니다. 7월은 투자일이 21일이라 입력을 해주었고, 평가손익까지 입력이 잘 되고 있습니다.

 

누적수익률 그래프도 데이터와 연동해서 자동 기록되도록 업데이트했습니다.

 

각각의 Y값을 이름정의에 입력한 값으로 지정함

 

 

 

3. 종목별 상관관계

이 기능은 너무 간단해서 미리 업데이트를 했어도 좋았을 텐데 많이 늦어진 이유는 저도 그동안 안 쓰고 있었습니다. 크게 중요성을 못 느꼈다고 할까요? 그래도 많은 고수분들이 필수적으로 상관관계를 따져가면서 포트폴리오 분산투자를 하고 있다고 알고 있어서 저도 제 계좌가 어떻게 상관관계가 있는지 궁금해졌습니다.

 

상관관계를 구하는 공식적인 부분은 표준편차도 구하고 이것저것 할 것이 많습니다만.. 엑셀에서는 간단히 CORREL 함수만 사용하면 끝입니다.

 

비교하고자 하는 종목 혹은 계좌의 일간 수익률만 계산해두면 범위를 지정하면 끝입니다. 저는 기본적으로 계좌별로 일일수익률을 계산해두었으므로 함수만 적용하면 바로 상관관계를 알 수 있습니다.

 

https://blog.naver.com/heecheoldo/222845948497

보유 주식 간 상관계수를 따져보다 (상관계수 구하는 법)

 

다히치님의 상관관계 구하는 포스팅에서 색깔 부분은 차용했습니다. 저는 구글스프레드시트가 아닌 엑셀을 사용하니까 조건부서식으로 셀 색깔이 상관관계에 따라서 표현되도록 합니다.

 

조건부 서식 - 색조 - 기타규칙

 

먼저 조건부 서식 -> 색조 -> 기타규칙으로 들어가면 스타일을 지정할 수 있습니다.

 

새 서식 규칙 설정 방법

 

위 그림처럼 3가지 색조로 구분하면 자연스럽게 색상으로 표현됩니다.

수식은 간단히 '=CORREL(미국ETF일일수익률,국내일일수익률)' 요렇게 입력하면 끝입니다.

 

상관관계 (예시)

 

입력하면 위처럼 상관관계가 표시되고 0.03이라서 아주 옅은 빨간삘(?)로 표시가 됩니다. 확실히 국내와 미국 ETF는 상관관계가 그닥 크지는 않아 보입니다. (물론 엑셀파일에 들어있는 국내 주식은 3개뿐이라서 좀 그렇긴 하지만..)

 

상관관계(예시)2

 

그래서 좀 더 빵빵한 예시자료를 위해서 제 본계좌의 상관관계 그래프를 가져와봤습니다. 제법 그럴싸해 보이나요?

일단 마이너스 쪽으로 간 계좌는 국내와 미국 ETF 밖에 없고, 대부분은 상관관계가 있는 편입니다. 확실히 라오어계좌가 ETF를 기반으로 움직이다 보니 미국 ETF, 미국 시장과 상관관계가 엄청 높게 나오네요.

 

국내와 미국시장은 확실히 상관관계가 좀 떨어져 보입니다. 그래서 양쪽 다 분산해서 투자해야 하는 걸까요?? 달러와 원화의 분산투자도 자연스럽게 될거고..

 

아무튼 상관관계까지 엑셀에 업데이트가 완료되었습니다.

 

 

 

마무리

몇 개 정리안했다고 생각했는데 포스팅에 제법 시간이 많이 걸렸네요. 별거 아닐지 몰라도 제법 정성이 많이 들어간 엑셀 파일입니다. 엑셀 파일만으로 투자수익률이 엄청나게 상승하거나 하지는 않습니다. 하지만 많은 선배 고수님들은 기록의 중요성을 강조하시더라구요.

 

저도 그 말에 완전히 동의하는 바입니다. 매달 정리하는 게 힘이 들 때가 있지만 한달한달 투자일지를 적으면서 어떤 일이 있었나 복기도 할 수 있고, 멘탈을 단단히 잡는데도 확실히 도움이 됩니다.

 

투자자 분들 각자가 알맞게 잘 활용해 주시면 감사하겠습니다. ^^ 종종 또 업데이트 하겠습니다. -끝-