먼저 아래와 같은 월간 데이터를 마련합니다.
이 데이터를 기준으로 월간 수익률을 계산합니다.
같은 방식으로 인텔까지 드래그 하시고..
기본 데이터에서 T-Bill은연간 riskfree rate였으므로 앞과 달리 월환산,백분율로 수정합니다.
만든 수식들의 아래값을 채웁니다.(구획 설정 후 더블클릭하면 채워지는 건 아시죠?)
이제 각각의 종목들의 월평균 수익률 값들이 risk free rate 대비 얼마나 초과 수익률이발생하는지 구해봅니다.
RFR 에 해당하는 열(여기서는 O열)에 달러를 붙이고 옆으로 주욱 드래그해도 되겠죠.
만든 수식들의 아래값을 채웁니다.(구획 설정 후 더블클릭하면 채워지는 건 아시죠?)
자. 이제 각 초과 수익률 값의 평균과 표준편차를 구해봅니다.
먼저 월마트. 월마트 초과 수익들 값을 평균화 시킵니다.
표준편차도 구합니다.
다른 값들도 구합니다(주욱 드래그하면 쉽게 채워집니다.)
혹시나 MVF 그래프를 만드실 분들은 S&P 500의 평균과 표준편차를 표시할 때 Monthly Excess Return으로 잡으면 안됩니다. Monthly Return 을 별도로 구해서 그 값으로 하셔야 합니다.
방법은 똑같습니다. 데이터의 대상만 월초과수익에서 월수익으로 바꾸면 됩니다.
Market Risk Premium과 Average Risk-Free Rate를 구해봅니다.
시장(곧 SP500)위험으로 인한 수익은 SP500의 초과수익 평균입니다. 여기서 초과라는 것은 시장 위험 -무위험이죠.
Risk free rate의 평균도 구해봅니다.
이들은 월 평균이니 년으로 환산합니다. 네 12를 곱하면 됩니다.
여기서부터 어렵죠. 기술 들어갑니다.
데이터 탭에서 데이터 분석을 누르고
나오는 팝업창에서 회귀분석을 선택하고
입력범위와 이름표, 신뢰수준 그리고 선적합도를 선택합니다.(선적합도를 선택하면 잔차는 자동으로 표시됩니다.)
그러면 아래와 같은 S&P 500과 walmart 간의 회귀분석 결과값이 도출됩니다.
이어서 Pfizer, JP Morgan, Boeing, Intel의 회귀분석을 차례차례 수행합니다.
아까 입력범위의 y값만 바꾸면 됩니다. P자리에 Q,R,S,T를 차례로 대입하여 회귀분석 결과값 시트를 생성합니다.
아래 Sheet1,Sheet2,Sheet3,Sheet4,Sheet5의 시트명을 각 브랜드 Walmart, Pfizer, JPMorgan, Boeing, Intel로 바꿉니다.
(JP Morgan은 나중에 함수 적용시 오류를 막기 위해 JPMorgan으로 붙여서 기입하는게 좋습니다)
이제 각 사의 Alpha, t-stat,p-value,Beta, t-stat, p-value,R2을 찾아서 집어넣습니다.
(노란색 아래는 셀 수식입니다. 참조하세요)
결과값입니다.
먼저 분산을 구하고, 각 변수들의 공분산을 구합니다.
자기 분산 구하기
공분산구하기
Z33셀에서 주의할 점( 대부분의 사람들의 실수)
제대로 쓴거
=VAR.S(I5:I220) = 0.003055
대부분 사람들의 실수
=COVARIANCE.S($I$5:$I$220,I5:I220) = 0.003055
공분산 안써야지라고 맘 먹었으나 실수하는 거
=VAR.S($I$5:$I$220,I5:I220) = 0.003048
결과적으로 다음의 값들이 도출됩니다.
해찾기를 사용하여 푸는 문제입니다.
우리의 엑셀에는 X33~X38까지 서식이 적용되어있습니다.
Z31~AD31까지 변수 5개 확률, 그리고 합의 내용입니다.
일단 5개의 확률을 합하면 1이므로, 모두가 같다는 가정으로 출발, 0.2를 Z31~AD31에 입력합니다.
해찾기를 사용하기 위해 창을 열어봅니다.
데이터 탭의 해찾기를 누르고
그리고 해찾기를 사용합니다.
근데...어디에 무슨 값을 넣어야 할지 막막합니다.
교수님의 기존 연습 문제에는 위에서 구한 공분산 표 아래에
각 변수들의 포트폴리오로 이루어진 각 변수 확률에 공분산을 곱한 값의 서식이 있습니다.
그리고 그 서식들의 합이 PF var로 주어졌었죠.
이 값이 목표설정에 해당하는 셀이었습니다.
으로 기본틀이 만들어졌습니다.
이제 하나하나 해보겠습니다.
실행할때 아래의 메세지 나오는 거 정상입니다.
이와 같이 하나하나 제한조건의 $W$65를 65부터 차례대로 72까지 변경해가며 내용을 채웁니다.
이렇게 채워집니다.
역시 해찾기입니다만, 내용이 달라졌습니다.
목표설정이 PF Var($Z$47)이 아닌 PF S($z$53)입니다.
또한 위의 문제를 풀기 위해서는 최소값을 선택해야 했으나, 이번엔 최대값을 선택해야 합니다.
또한 위의 제한 조건에 종속에서 PF mean = MV Frontier에서 MV Frontier값을 계속 조정했으나 이번엔 필요없습니다.
결과로 인해 변수셀이 다음과 같이 도출되었습니다.
이 값을 아래의 w1~w5 아래에 기입하고 PF Var과 PF Mean도 기입할 수 있습니다.
나머지는 서식으로 자동으로 채워지므로
와 같은 결과가 도출됩니다.
고생하셨습니다.