슬기로운 자본주의 생활법

안녕하세요 서대리입니다.

 

이번 포스팅에서는 개인연금/IRP를 ETF로 운용하시는 분들을 위한 구글스프레드용 관리양식을 공유하고자 합니다. 대단한 양식은 아니지만 연금ETF포트폴리오를 관리하는데 어려움이 있으신 분들은 참고하시면 좋을 것 같습니다^^

 

구글스프레드시트로 만든 양식은 아래 링크를 통해서 확인부탁드리며, 간단하게 사용방법을 알려드리도록 하겠습니다.

 

링크 접속 후 [파일] - [사본만들기]를 통해서 본인의 구글드라이브에 저장 후 사용하시면 됩니다.

 

 

연금관리양식Ver1.0

투자일지 일자,연도,월,일,목적,매수/매도,종목코드,거래종목,투자지역,거래수,체결단가 (원),체결단가 (달러),체결금액 (원),체결금액 (달러) 2019. 8. 7,2019,8,7,개인IRP,매수,245340,TIGER 미국다우존스30,미국,1,₩15,910,₩15,910,$0.00 2019. 8. 16,2019,8,16,개인IRP,매수,273130,KODEX 종합채권(AA-이상),한국,1,₩109,880,₩109,880,$0.00 2019. 8. 28,

docs.google.com

 

양식 사용방법

 

 

위에 보이는 것처럼 연금ETF포트폴리오의 전체 수익률과 연간 입금액, 자산별 비중&수익률 등을 쉽게 파악하게끔 양식을 만들려고 노력했습니다. 그럼 시트별로 입력해야할 내용이 어떤 것인지 알아보도록 하겠습니다. 

 

원활한 설명을 위해 시트마다 번호를 적어뒀으니 참고해주시고, 시트마다 노랑색 음영된 부분만 수정해주면 됩니다.

 

 

#2번-투자일지

투자일지에는 매수나 매도한 종목을 기입하시면 됩니다. 앞에서 설명드린 것처럼 노랑색 음영된 부분만 입력하시면 되는대요. 아래 예시처럼 간단하게 입력하면 됩니다. 참 쉽죠? 다만 거래종목명 입력하실 때는 주의하셔야 합니다. 종목코드를 불러와야하기 때문에 ETF상품명을 전부 제대로 입력하셔야 합니다. 그렇지 않으면 수식에서 종목코드를 제대로 불러올 수 없고 결과적으로 1번 시트에서도 제대로 반영이 안됩니다.

연금계좌 특성 상 거의 매도할 일은 없겠지만 만약 매도를 했다면 아래와 같이 입력하시면 됩니다. (아래 3번째 줄) '매수/매도' 칸에 매도를, '거래수'에는 매도한 수량만큼 마이너스수량으로 입력하시면 됩니다.

 

 

 

#3번-입금내역

해당 시트에는 연금계좌에 입금한 내역을 적으면 됩니다. 마찬가지로 노랑색 음영부분만 아래 예시처럼 적어주시면 됩니다. 

 

사실 해당 시트는 올해 얼마나 납입했는지 알아보려고 만든 시트이기 때문에 귀찮으시면 입력하지 않아도 됩니다. (1번 종합시트 상단의 입금그래프&세액공제 금액 확인용도)

 

 

#4번-배당내역

이 시트에서는 배당금 발생 시 배당내역을 작성하시면 됩니다. 예시로 12월25일에 TIGER부동산인프라고배당ETF로부터 1,000원 배당금이 발생했다고 가정하면 아래와 같이 작성하시면 됩니다. 

 

 

 

4번시트에 입력하시면 1번 종합시트의 Dividends칸에 자동으로 배당금이 반영됩니다.(빨간상자 부분)

 

 

#1번-퇴직연금

2~4번까지 사전작업을 완료하셨다면 이제 거의 끝났습니다. 마지막으로 대쉬보드에 해당하는 1번 시트만 살짝 손봐주면 됩니다.(마찬가지로 노랑색 부분)

 

 

연금 : 개인IRP or 개인연금

 

Name : 포트폴리오 보유종목 풀네임 기입(제대로 입력 시 Symbol부분의 종목코드가 ETF명에 맞게 변경됩니다)

 

구분 : 자신의 포트폴리오에 맞게 알아서! 저는 자산유형별로 묶기 위해서 선진국, 신흥국, 리츠, 채권으로 나눠봤습니다. 이 내용이 우측 상단의 차트에 반영될 것입니다. 다만 저 차트를 손보기 위해서는 한가지 작업이 추가로 필요한대요.

 

차트를 밑으로 내리면 간단한 표가 하나 나옵니다. 여기서 노랑색 음영부분을 구분에서 입력한 내용 그대로 입력해주시면 됩니다. 그러면 알아서 차트를 통해 수익률과 비중 등을 확인할 수 있습니다.

 

 

 

아직 부족한 점이 많습니다.

증권사 앱에서 제공하는 정보들은 거의 단순 수익률 정도라 아쉬움이 많아서 직접 만들긴 했는데.. 아직 부족한 부분이 많습니다. 구글스프레드시트의 기능은 정말 엄청나지만 제가 아는 기능이 몇개 안되다보니 단순합니다. 그래도 이렇게 보면 포트폴리오를 점검하는 시간도 줄고 리밸런싱하기에도 쉽기 때문에 만족합니다^^

 

간단한 연금ETF포트폴리오 관리양식이지만 사용해보시고 더 추가되었거나 보완됐으면 하는 부분있으면 언제든지 말씀해주세요ㅎㅎ

 

*글로 설명하기가 애매한 부분이 많아서 조만간 유튜브로 설명영상을 올리도록 하겠습니다.(월별 수익률 차트는 도저히 글로 쓸 자신이 없어서 이번 포스팅에서 생략)

*수식이 제대로 작동되지 않는다던지 사용에 어려움이 있다면 언제든지 댓글로 문의해주세요. 최대한 빠르게 답변드릴 수 있도록 하겠습니다.

*입력된 종목은 예시로 작성한 것이며 추천종목이 아닙니다.

 

 

이 글을 공유합시다

facebook twitter googleplus kakaostory naver

본문과 관련 있는 내용으로 댓글을 남겨주시면 감사하겠습니다.

  1. Favicon of https://alienworker.tistory.com 외계인노동자 2019.12.29 02:05 신고

    엑셀을 잘 다루시네요! 만들어 놓으신거 감사히 잘 쓰겠습니다!ㅎㅎ

  2. 고태운 2019.12.30 13:48


    핸드폰에서는 에러 없이 잘 열리는데..

    왜 PC에서는 구글 드라이브로 들어가더라도 권한이 만료되었다고 편집이 안될까요?

  3. 주영민 2020.01.20 14:16

    아 사본만들기를 해야 하는군요 감사히쓰겠습니다

  4. 준준 2020.01.31 15:57

    우와 감사합니다!

  5. T Dragon 2020.02.06 19:05

    감사합니다 ~

    KODEX WTI원유선물(H) 가 종목코드가 다른걸로 나오더라고요 ~ 왜그런지 모르겟네요
    앞에 코드만 강제로 바꾸니 현재주가수정시켜 세팅했습니다 ~

    여러가지로 도움이 많이 될거 같습니다 ~
    좋은 글 감사하고 ~ 자주 방문하겠습니다

  6. Favicon of https://https:// T Dragon 2020.02.07 10:39

    이런 고급시트를 사용을 안해봐서 1~4번 연동하는 기능이 잘 안되네요 ㅜㅜ
    혹시 유튜브 영상으로 설명한번 가능하시면 부탁드립니다 ㅜ

    그리고 유튜브채널있으시면 링크 알려주시면 구독하겠습니다 ^^

    • 안녕하세요 T드래곤님 ㅎㅎ

      유튜브 계정만 있고 아직 시작을 못해서 당장에 보여드릴 영상자료는 없습니다 ㅠㅠ 안그래도 조만간 유튜브로도 제작해볼 생각이었는데 완성되면 포스팅에서 추가해두겠습니다 ㅎㅎ

      혹시 지금 어느 단계에서 막히시나요?? 자세히 알려주시면 확인해보고 최대한 자세히 답변드리겠습니다 ^^

      늘 방문 감사힙니다 ㅎㅎ

  7. 박은지 2020.02.29 22:44

    서대리님~! 올려주신 엑셀 정말 유용하게 잘 쓰고있습니다... 한눈에 보기 편하고 너무 멋져요 !!
    근데 제가 엑셀을 잘 못해서 이유를 모르겠는데요..ㅠㅠ
    서대리님이 바꾸라는 것만 바꿨는데 , 시트2에 수량을 입력하면 시트1과 연동이 하나도 안돼요 ㅠ,ㅠ
    도대체 뭐가 문제인지 모르겠씁니댜...흑흑

    • 칭찬감사합니다^^

      노랑색 부분만 수정하셨나요?? 양식에서 가장 중요한 부분이 종목명을 정확하게 쳐야해요. 띄어쓰기까지 완벽하게 맞춰서 쳐야 종목코드를 불러오기 때문에 이 부분을 한번 확인부탁드립니다 ㅠ

      혹시 수정하신 스프레드시트 링크를 공유해주시면 제가 한번 봐볼게요^^

    • 박은지 2020.03.01 19:12

      음 종목명도 정확히 입력했는데.. 공유는어케하는거죠..^^;;;

  8. 조원섭 2020.03.24 22:30

    안녕하세요 서대리님~
    조대리 라고 합니다 ㅋㅋ
    공유해주신 스프레드시트 너무너무너무 잘쓰고있습니다.
    왜 엑셀보다 구글스프레드시트를 사용하시는지 알겠더라구요.
    덕분에 구글스프레드시트의 파워도 알아가고 투자도 시작하게됬습니다.
    자료 감사드립니다.

    • 안녕하세요 조대리님^^

      구글 스프레드시트 사용하다보면 구글 주식이 사고싶어질 정도로 매력적인 것 같습니다 ㅎㅎ

      써보시고 문의사항 있으시면 언제든지 댓글로 남겨주세요 ㅎㅎ

  9. wait 2020.05.02 21:02

    안녕하세요! IRP, ETF 관련 검색하다가 이 블로그에 오게 되었습니다
    아직 모르는게 많은데 좋은 정보가 너무 많아서 저에게 큰 도움이 되고 있답니다
    정말 감사합니다
    아울러 구글스프레드시트도 공유해주셔서 감사합니다
    아직 IRP 계좌로 ETF를 투자하고 있지 않은지라 조만간 ETF 투자를 하게 되면 꼭 사용해볼게요

  10. yhkim 2020.05.20 15:21

    혹시 외국 ETF나 주식도 연동 가능하게 해주실 수 있는지 여쭤봅니다 ㅠㅠ 너무 잘 사용하고 있어요

    • 도움이 되셨다니 다행입니다^^

      해외주식과 ETF도 연동가능하게 세팅되어 있습니다 ㅎㅎ

      Symbol칸에 티커를 입력하면 가격과 차트 등이 자동으로 반영됩니다^^

      참고로 해외주식은 중국주식빼고는 기본적으로는 구글함수로 다 끌고옵니다 ㅎㅎ

      앞으로도 쓰시다가 문의사항 있으시면 문의해주세요~

  11. Favicon of http://https:// yhkim 2020.05.22 12:08

    아아 해결은 됐습니다 종목코드에 바로 입력을 하니깐 가져오는데 이게 달러 기준으로 가져오더라구요.. 가져온 값을 원화로 바꿔서 표기하고 싶은데 방법이 없을까요??

    • 그것도 방법이 있습니다.

      "현재가" 나오는 셀의 수식에 아래와 같은 수식을 추가하면 됩니다.(Mkt Price 부분)

      #추가할 수식 *googlefinance("USD"&"KRW")

      Ex>
      =GOOGLEFINANCE(D16,"price")*googlefinance("USD"&"KRW")

      그러면 현재 달러환율을 기준으로 자동으로 원화표기해줍니다^^

  12. Favicon of https://brownstar21.tistory.com 주린주린이 2020.06.18 00:14 신고

    서대리님 종목추천 글부터 최근에 다른 곳을 통해 연금저축보험을 펀드로 이전하며 여기까지 왔네요. 너무나도 감사드립니다!
    저도 올시즌 스타일로 연금계좌 고나리하며 하려고 하는데요, 공유해주신 스프레드에서 1번시트의 세금부분을 위한부분!

    =SUMIFS('3번-입금내역'!$H:$H,'3번-입금내역'!$G:$G,"연금",'3번-입금내역'!$C:$C,2020)
    * 제가 IRP가 아니라... 임의로 제목은 연금으로 수정하였습니다.

    여기서 타계좌에서 이전한 금액도 1번시트의 입금금액으로 잡혀버리는데요, 혹시 이전금액에 대한 부분만 제외할 수 있는 방법이 없을까요?? 제가 엑린이라ㅠㅠㅠ 2시간동안 연구해서 거의 다 따라잡았는데 이 부분에서 막혀버리네요ㅠ 귀찮아도 이 것도 정리하고 싶어서요...

    • Favicon of https://brownstar21.tistory.com 주린주린이 2020.06.18 00:22 신고

      아, 제가 스스로 산식을 수정하였습니다.
      기존산식 - (이전금액-1~5월입금액)
      이러니까 얼추 맞아떨어지네요.

      엑셀이 너무 거대해서 만드신 것에 대비해서 일부분만 수정하며 따라가는데도 2시간이나 걸렸는데ㅠㅠ 큰 틀을 수정하기에는 너무 어렵네요ㅠㅠ 엑셀공부 좀 해야겠어요ㅠㅠ 소중한 자료 너무 감사드립니다!

  13. 여니지 2020.09.09 14:51

    와 정말 좋은 자료네요.
    개인연금에서 ETF 포트폴리오 처음 시작했어요.
    연금 관리에 딱 맞는 자료 공유해주셔서 감사해요^^