주식 포트폴리오 작성하는 방법 - 키움증권 산출식

아래 작성된 포트폴리오에서 다른 색으로 표시된 부분이 직접 작성한 것이고 나머지 부분은 수식으로 작성된 곳이다.

인터넷상에서 여러 포트폴리오를 참고하여 작성한 것인데,

막상 작성하고 나서 보니 실제 계좌와 값이 맞지 않는 부분이 꽤 있었다. 증권사 별로 산출식이 조금 다를 수 있으니 이용하고 있는 증권사 산출식을 확인해야 한다.

 

 

실제 내 계좌와 정보가 맞지 않으면 의미가 없기에 내가 사용하고 있는 키움증권 산출식을 참고해보자.

아래는 키움증권 산출식이다. 이를 스프레트시트에 수식으로 작성해 보자.

 

 

일단 HTS 에서 내 계좌 정보를 조회 후, 복사하여 시트로 가져온다.

그리고 종목 코드, 종목명, 매입일, 매입가, 수량을 각각 넣어준다.

사실 종목 코드나 종목명만 넣고 VLOOKUP 함수를 이용하여 가져올 수도 있지만, 내가 보유한 주식이 얼마 없기도 하고 정리도 할 겸 하나씩 기입했다.

 

1) 현재가는

=GOOGLEFINANCE(종목코드, "price")

구글 파이낸스에서 자동으로 값을 가져온다. 하지만 실시간은 아니며 20분 지연이 있다.

종목 코드 앞에 KRX, KOSDAQ이라고 코스피, 코스닥 구분자를 넣었다.

처음에는 숫자만 기입했는데, 구분자가 없으면 값이 업데이트가 잘 되지 않는 경우가 있어 추가했다.

변수는 "price" 말고도 여러 변수가 있으니 확인해 보고 필요할 경우 추가하면 된다.

 

2) 매입금액은

=매입가*수량

 

3) 평가금액은

=현재가*수량

 

4) 보유기간은 주말을 제외한 영업일만 계산하기 위해 NETWORKDAYS라는 함수를 사용하였다.

기본적으로 주말만 제외하기 때문에 공휴일은 제외된다. 공휴일을 변수로 더 추가할 수 있다.

그리고 별도로 &"일"을 추가하였다.

=NETWORKDAYS(시작일, 종료일)&"일"

 

5) 세금은 코스피는 거래세 0.10%+농특세 0.15%, 코스닥은 거래세 0.25%이다.

코스피도 간단하게 0.25%로 계산하기로 하자.

=FLOOR(평가금액*0.0025)

위 산출식에서와 같이 원미만은 절사 하므로 floor 함수를 사용한다.

 

6) 수수료는 매수 수수료+매도 수수료의 합이다.

매수 수수료는 매입금액*수수료 0.015%

매도 수수료는 현재가*수량(=평가금액)*수수료 0.015%

수수료는 10원미만 절사이므로

=FLOOR(매입금액*0.00015, 10)+FLOOR(평가금액*0.00015,10)

 

7) 매도 수수료는 따로 기입하여 준다.

=FLOOR(평가금액*0.00015, 10)

8) 평가손익은

=평가금액-매입금액-수수료-세금

 

9) 수익률은 

=((평가금액-수수료-세금)/매입금액-1)

 

10) 총매입금액은 종목별 매입금액의 합

=sum()

 

11) 총평가금액은 종목별 평가금액의 합

=sum()

 

12) 총손익은 종목별 평가 손익의 합

=sum()

 

13) 수익률은

=총평가금액/총매입금액-1

 

14) 추정자산은 D+2예수금+각 종목의 평가금액의 합-각 종목의 현재가로 매도 시 매도 수수료와 세금의 합

=예수금+총평가금액-세금합-매도수수료합

 

이렇게 정리하고 HTS와 비교해보면 값이 동일하다.

포트폴리오 구성은 사용하면서 필요에 따라 계속 보완이 필요할 것 같다.

 

다음은 매매 수익 현황 작성법을 설명하겠다.

 

 

• 2020/09/13 - [나의 주식] - 주식 포트폴리오 작성하는 방법 - 매매 수익 현황
• 2020/09/06 - [나의 주식] - 구글 스프레드시트를 이용한 포트폴리오 with 구글파이낸스

구글 스프레드시트를 이용한 포트폴리오 with 구글파이낸스

주식 포트폴리오 작성하는 방법 - 매매 수익 현황