슬기로운 자본주의 생활법

이번 포스팅에서는 제가 실제 이용하고 있는 구글스프레드시트로써 '포트폴리오설계 양식'과 계획대로 자산을 편입할 수 있게 종목별 매수수량을 알려주는 '매수도우미 양식'를 공유하고자 합니다.

 

마찬가지로 자신의 구글드라이브에 [사본 만들기]로 저장하신 후에 이용하시면 됩니다.^^

 

 

연금관리양식Ver1.0

1번-퇴직연금 파이어족 서대리 연금ETF포트폴리오 관리양식 https://happy-life88.tistory.com/ IRP DASHBOARD,2019' Deposit Developed,Emerging,Korea,REITs,Bond,Cash ₩58,675,+1.6%,Amount,₩240,200,₩70,825,₩447,290,₩215,325,₩1,411,905,₩1,271,855 Overall P&L,P&L(%),Mkt Value,₩258,260,₩78,

docs.google.com

 

*2개라고 했지만 실제로 하나의 시트에 다 들어있습니다.

(시트명 : 펀드설계&매수도우미)

*지난번에 공유한 연금포트폴리오 관리양식에 추가해서 Ver1.5가 되었습니다.

 

 

포트폴리오설계 양식

시각적으로는 뭔가 있어보이나 사실 특별한 양식은 아닙니다. 간단하게 자신이 편입할 ETF들을 기입하고 세부비중을 입력하는 표입니다. 기본적으로 ETF에 따라서 빨간색 박스 안 부분은 수동으로 기입하시면 됩니다.

 

이 중에서 신경써야할 부분만 간단하게 설명드리겠습니다.

 

#종목명

제대로 입력하지 않으면 종목코드가 '000000'으로 뜰 것입니다. 그러면 당연히 현재주가도 반영이 되지 않기 때문에 뒤에서 설명할 매수도우미 양식도 사용할 수 없습니다. 그렇기 때문에 종목명이 제대로 입력됐는 지 확인해주세요!

 

#세부비중

내가 운용할 포트폴리오에서 각각의 ETF들 비중을 의미합니다. 개인의 투자성향에 맞게 ETF비중을 기입하시면 됩니다. 이 부분에 따라서 포트폴리오 총 보수와 매수도우미 양식이 계산되기 때문에 중요합니다.

 

#운용보수

ETF별로 운용보수를 입력하시면 세부비중에 따라서 포트폴리오 총 보수를 확인할 수 있습니다.(표 하단) 

 

*운용보수 같은 경우는 네이버금융에서 크롤링해올 수 있긴 하지만 실제 투자설명서와 차이가 있어서 수식을 걸지 않았습니다.

 

 

매수도우미 양식

포트폴리오설계 양식에서 오른쪽으로 이동하면 매수도우미 양식이 있습니다. 해당 양식은 계획한 비중대로 ETF를 매수할 수 있게 계산해주는 양식입니다. 아마 실질적으로 이 양식의 결과값이 실질적으로 도움이 되지 않을까 싶습니다^^

 

이 표에서는 매수할 총 금액(투자금액)만 입력하면 나머지는 수식으로 자동화되어있습니다. 마찬가지로 몇가지 주의사항만 말씀드리겠습니다.

 

실제필요주식수대로 매수만 하면 끝!

 

1. 하단 '투자금액'에 매수할 총 금액 입력

 

2. '실제필요주식수'에 도출된 수량만큼 매수하기

 

3. '매수 후 잔액'과 '매수-계획GAP'을 보고 비중이 마이너스인 ETF 중에서 추가매수

 

'실제필요 주식수'대로 매수를 하면 예시처럼 투자금액이 조금 남을 것입니다. 이는 당연한 현상입니다. 정확하게 계획한 비중대로 매수를 하려면 '세부비중별 주식수'처럼 ETF를 소수점으로 사야합니다. 하지만 국내상장ETF는 소수점 거래가 없기 때문에 '실제필요 주식수'처럼 소수점을 제외한 수량을 매수해야하고 그러면 자연스럽게 금액은 남을 수 밖에 없습니다. 남는 금액은 상황에 맞게 ETF를 추가매수하시면 됩니다. 매수하지 않고 현금성 자산으로 킵해두는 것도 방법입니다.(진리의 케바케)

 

연금포트폴리오뿐만 아니라 배당주 포트폴리오 등 다양하게 활용할 수 있으니 사용해보시고 궁금한 점이나 제안해주실 사항이 있으면 댓글 남겨주세요. 그럼 다들 성투하시길^^

 

 

이 글을 공유합시다

facebook twitter googleplus kakaostory naver

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

  1. 이전 댓글 더보기
  2. 구안토 2020.02.22 17:22

    좋은 자료 감사합니다. 잘 사용하겠습니다.

  3. 오천수 2020.02.23 03:32

    종은자료 정말 감사합니다.
    저와 종목도 비슷해서 더 좋네요.
    조금 어렵습니다만.... 공부를 해야겠죠?
    코로나 조심하시고 건승하세요~ ^^

  4. 오천수 2020.02.24 21:07

    안녕하세요?
    코로나 극복 잘 하고 계신지요?

    좋은 자료 잘 다운받았는데, 제 종목을 바꿔서 해보려니까 잘 안되네요~
    제가 엑셀이나 스프레드시트를 잘 못하니 답답하기만 합니다. ㅠㅠ

    일단 종목명을 변경하니 종목코드와 가격등은 잘 불러옵니다.
    근데 Quantity 와 Amount 가 0으로 나옵니다.

    이거 어떻게 해보려고 2번-투자일지에 가서 위에 입력된것 복사해서 바꿔보고...
    중간에 행 삽입해서 바꿔봐도 잘 안되네요~~
    무식이 죕니다.

    주변에 물어볼 사람도 마땅찮고.... 죄송스럽게도 이렇게 질문글 올립니다.
    도움 부탁드립니다.

    • 답변이 늦어서 죄송합니다 ㅠ

      혹시 2번 투자일지에서 천수님께서 매수한 종목들도 입력하셨나요?? 투자일지에 매수나 매도한 수량을 입력해야 1번 종합시트에서 호환이됩니다 ㅎㅎ

      자세한 상황을 몰라서 정확한 답변을 드리기 어려운 점 양해부탁드립니다. 아니면 혹시 작성하신 스프레드시트 공유링크를 올려주실 수 있으신가요??(작동이 안되는 부분 체크해서)

      공유해주시면 제가 한번 봐보고 다시 답변드리겠습니다^^

  5. 김지헌 2020.03.09 23:35

    전 최근에야 구글스프레드쉬트를 알고 이것저것 만들어보고 있었는데, 이건 정말 잘 만드셨네요. 그것도 공유까지...
    정말 감사드리고, 행복한 투자하시길 빕니다.

  6. 뽀글이 2020.03.18 10:09

    올려주신 양식을 참고하여
    제가 매수하고 싶은 etf 정보를 기입하고 이제막 매수 시작했습니다
    (보험에서 펀드로 최근에 이전)

    한가지 궁금한게,
    매수비용이 200만원이라하면 당일에 양봉인 종목이 있을 것이고 음봉인 종목이 있을 것인데요
    아무래도 사람심리가 양봉보다는 음봉일때 매수하고 싶은지라...

    당일에 양봉뜬 종목은 그 수량을 정해놨다가 나중에 음봉뜰때 차근차근 매수하는 편이신가요 ?
    아니면 양봉음봉 상관없이 당일 매수 금액 정했으면 바로 다 매수하시는건가요 ?
    현재 이전후 900만원이 있는데 하루에 150씩 분할 매수하려합니다 (음봉 종목)

    • 안녕하세요^^

      저의 투자스타일에 대해서 말씀드리면 일단 적립식 매수는 왠만하면 월급날 당일이나 그 다음날 매수합니다.

      국내주식형ETF말고는 체결이 쉽게 되는 편은 아니다보니 왠만하면 호가정도에 매수를 걸어놓고 회사업무를 보는데요. 왠만하면 장 마감 때 매수체결되어 있더라고요 ㅎㅎ

      물론 좀 더 싸게 사면 좋긴 하지만 예측은 무의미하기 때문에 꾸준히 매수한다것에 의의를 두는 편입니다. 오늘도 코스피 1600이 이렇게 허무하게 깨질 것이라 예측한 사람이 얼마나 있을까요 ㅠ

      그리고 지금같이 변동성이 어마무시한 장에서는 하루씩 분할매수보다는 최소 1주나 2주텀을 두고 매수하는 것이 좋을 것 같습니다. 주식시장에 영향력이 가장 큰 미국이 이제 코로나시작이라.. 조금 천천히 진입하시는 것이 낫다는 생각입니다. 물론 이것은 제 개인적인 생각이라 참고만 해주세요 ㅎㅎ

      실제로 투자하기 어려운 상황이다보니 5년치 퇴직연금을 이번에 DC형으로 바꾸면서 한달씩 분할매수 중입니다.

    • 뽀글이 2020.03.19 08:58

      맞습니다! 저도 현재 예수금의 50%정도 매수했는데 너무많이 급락중이라...

      나머지 50%는 텀을 좀더 길게 가져가야할거 같습니다... 장이 너무심하네요 ㅠㅠ 외인들 다 도망가고

      저도 퇴직연금이 아직 DB형인데..
      이 시기에 DC형으로 바꿔서 굴려야 하나 싶습니다..

    • 우선 미국의 확진자 증가폭이 줄어들고 미국시장이 어느정도 안정화된 다음에 금액을 투입하는 것이 안전할 것 같습니다.

      그리고 지금은 사실상 환율이 계속 오를 가능성이 있기 때문에 달러로 미리미리 환전해서 가지고 있는 것이 중요할 것 같네요 ㅠ

  7. 진솔 2020.04.02 03:35

    이제 막 경제에 관심을 갖게 된 21살 대학생입니다. 보잉 주가글로 시작해서 잘 읽고 있습니다~

  8. Favicon of https://fantasticpotato.tistory.com 환상구르 2020.04.28 08:52 신고

    와!!!!
    좀만 더 빨리 올걸그랬네요ㅜ
    저도 최근에 개인연금펀드관련 시트만들었는데 서대리님께 훨씬 좋아보이네요!
    잘쓰겠습니다 항상많은 도움이 됩니다

  9. 후불 2020.05.06 13:24

    구글 스프레드시트 잘 쓰고 있습니다 ㅎㅎ 감사합니다~

    혹시 추가매수(리밸런싱)를 할 시에는 시트를 어떻게 활용하시는지 알려주실 수 있을까요? ㅎㅎ

    • 리밸런싱은 연금관리양식에서 나오는 비중을 기준으로 합니다.

      근데 기본적으로 저는 주식형은 매도하지 않고 채권이나 금ETF만 기준수익률을 달성하면 매도하는 방식으로 접근하고 있습니다 ㅎㅎ

  10. 주린이 2020.06.18 21:34

    연금관리 양식 잘보고 가요.
    엑셀을 이쁘게 잘 만드시는거 같은데...
    엑셀 추천해줄만한거 책이나 강의자료 있으시면 공유 부탁드려요.

    • 칭찬감사합니다^^

      엑셀의 기능적인 부분은 취준생 시절 "컴퓨터활용능력 1급" 자격증 공부하면서 많이 늘었던 것 같습니다.

      그리고 디자인적인 부분은 회사생활을 하면서... 하도많은 자료를 만들다보니 자연스럽게 습관화되었네요 ㅠㅠ

      제 개인적인 경험상, 엑셀 공부를 계획중이시라면 컴퓨터활용능력 1급 공부를 강추합니다^^ 여기서 공부하는 기능들만 잘 다루면 왠만한 부서에서 엑신 타이틀 획득 가능한 것 같아요 ㅎㅎ

      P.S>제가 따로 본 책은 없지만 유튜버 중에 "오빠두엑셀" 추천드립니다^^

  11. 초등주식 2020.07.10 13:27

    안녕하세요. 이번에 여기저기 알아보다가 너무 좋아서 글 탐독후 주식 하나씩 담아 두고 있습니다.
    그런데 양식을 보니 개인연금이 있고 퇴직연금이 있는데 따로 구분하신 이유가 있나요???
    개인연금은 주식일반계좌로 따로 운용하시는 건지요???
    퇴직연금은 연금저축계좌로 돌리시는거 같은데...

    • 안녕하세요^^

      먼저 칭찬해주셔서 감사합니다. 문의주신 내용에 답변드립니다.

      1. 개인연금과 퇴직연금을 나눈 이유는 둘의 운용방식이 다르기 때문입니다.

      개인연금계좌에는 국내상장 etf를 제한없이 담을 수 있고 안전자산 30%강제룰도 없죠.

      반면 퇴직연금/IRP는 레버리지나 파생etf를 담을 수 없고 증권사가 정한 안전자산을 강제적으로 30% 담아야하기 때문에 둘을 나눠서 관리합니다.

      다만 저는 자산배분의 효과를 더욱 극대화하기 위해 거의 개인연금계좌에만 입금&투자를 진행하고 있습니다.

      2. 개인연금도 증권사 개인연금계좌를 개설했고 거기서 운용하고 있습니다. 연금계좌다보니 마찬가지로 출금이 제한되지만 연말정산 때 400만원까지 세액공제가 가능합니다^^

      참고로 요즘 개인연금계좌 개설하면 거의 모든 증권사가 수수료무료 이벤트를 진행하고 있어요 ㅎㅎ

  12. 2020.07.13 22:55

    ETF 관련 공부를 하다가 구글링을 통해 사이트를 방문하게 되었는데 정말 훌륭하세요.
    많은 사람들에게 도움을 주고 계십니다 :)

  13. Favicon of https://van1ife.tistory.com 령맨 2020.07.23 16:28 신고

    구독 늘렀습니다. 서대리님.
    평소에 글 잘 보고 있습니다. 좋은 글 앞으로도 부탁드려요. 자주 올게요.
    제 블로그도 놀러오세요. 감사합니다. ^^

  14. 나나 2020.07.30 11:44

    서대리님 포트 공유 감사합니다. ㅎㅎ 한 가지 질문 드려도 될까요? 지난달에 타 금융기관에서 받은 퇴직금을 삼증 irp로 옮겼는데, etf 매수하려고 하니 예수금이 뜨질 않아서요.. 한 가지 질문 드려도 될까요? 지난달에 타 금융기관에서 받은 퇴직금을 삼증 irp로 옮겼는데, etf 매수하려고 하니 예수금이 뜨질 않아서요.. 타 금융기관에서 이전한 퇴직금으로는 운용할 수 없는건가요? 직원 상담 연결 해보려고 해도 계속 통화 연결중이라 해결이 안되네요;; 답답한 마음에 서대리님께 댓글 남겨봅니다.

    • 안녕하세요^^

      아마 아직 계좌가 이관되지않은 것 같습니다. 저도 타기관에서 미래에셋대우로 irp / 개인연금 이전했었는데요. 바로되지않고 약간의 시간텀이 있었던 것으로 기억합니다 ㅎㅎ

  15. Favicon of https://https://https://https:// wait 2020.08.03 06:53

    안녕하세요 서대리님!
    공유해주신 구글스프레드를 잘 쓰고 있습니다
    공유해주셔서 정말 감사합니다
    다름 아니라 여쭈어볼게 있는데요
    1번 시트 오른쪽 상단 Amount / Mkt Value/ P&L(%) 그래프의
    (Developed, Emerging, Korea 등등 써있는 부분을 어떻게 수정하는지 알 수 있을까요?
    전 Korea, Emerging 은 없고 Gold를 포트폴리오에 넣었는데
    차트 수정으로 들어가서 이것저것 만져봤는데도 도통 잘 안되네요

    • 안녕하세요^^

      글로는 설명하기가 조금 애매한대...한번 설명해보겠습니다 ㅎㅎ 설명이 부족할 수 있는 점 미리 양해부탁드립니다.

      1. 차트를 다른 곳으로 옮기세요. 그러면 차트 있던 자리에 차트데이터가 나옵니다.

      2. 차트데이터 입력란을 보시면
      첫줄에 원하는 "구분"값을 입력하시면 됩니다.

      현재는 Developed / EM 등으로 되어 있을텐데요. 이 부분을 원하시는 내용으로 바꾸시면 됩니다^^

      조만간 유튜브로 양식 메뉴얼을 찍어서 공유하도록 하겠습니다.

  16. Favicon of https://https://https://https://https:// wait 2020.08.06 08:34

    서대리님!! 댓글 감사합니다!!
    덕분에 해결이 되었어요 ^^
    유투브도 새 영상 올라올 때마다 잘 보고 있답니다
    앞으로도 잘 부탁드릴게요

  17. 2020.08.06 15:16

    비밀댓글입니다

  18. 주니러비 2020.09.17 15:14

    감사합니다! 잘 쓰겠습니다.

  19. dynsg 2021.01.05 11:39

    최근에 ETF에 관심이 생겨 검색결과 너무나 써보고 싶은 양식이네요~
    회사 보안상 구글 드라이브에 접속이 안되는데 엑셀파일을 컴퓨터에 다운받아 사용도 가능한지 궁금합니다.
    숨겨진 시트도 있고 연결되는 사이트도 있는것 같은데... 컴퓨터에 다운받아 사용하면 자동처리 되는 부분이 안되는지요..
    양식이 너무 훌륭해서 꼭 써보고 싶어 댓글 남깁니다.^^

    • 안녕하세요~

      우선 양식을 좋게 봐주셔서 감사합니다 ㅎㅎ 다만 제가 해당양식에 대해서 별도의 엑셀파일을 가지고 있지 않아서 공유가 어렵습니다 ㅠㅠ

      그리고 말씀하신 것처럼 주가 등은 구글자체 함수들을 이용하는 것이기 때문에 엑셀로 변환하면 100% 활용이 안될 거라 생각합니다 ㅠ

    • dynsy 2021.01.07 13:05

      답변 감사합니다.
      양식 활용을 못해 아쉽습니다만 블로그에 좋은 글들이 많은것 같습니다. 종종 방문하겠습니다. 새해 복 많이 받으세요

    • 유튜브도 함께 운영중에 있으니 시간되시면 한번 놀러와주세요^^

      채널명은 "서대리TV"입니다.

      dynsy님도 새해 복 많이 받으세요^^

  20. 2021.03.05 11:51

    안녕하세요! 네이버 블로그에서 타고 들어와 좋은 양식 받아갑니다!
    이제 연금저축 펀드로 이전해서 시드준비해놓고 포트폴리오 만드는 초보 입니다.
    좋은 양식, 운용 노하우 공유해주셔서 감사합니다!

  21. 2021.04.09 16:33

    양식 잘쓰고 있습니다. 감사합니다.
    연금 월별 현황시트 업데이트방법은 어느곳을 참조하면 될까요?
    투자 수익률 부분이 오류가 나는데
    "좌수", "손익 Index" 가 업데이트 되어야 하는 것 같은데 이게 무엇을 의미하는지 모르겠습니다.