엑셀 해 찾기 회귀분석 - egsel hae chajgi hoegwibunseog

만든 수식들의 아래값을 채웁니다.(구획 설정 후 더블클릭하면 채워지는 건 아시죠?)

이제 각각의 종목들의 월평균 수익률 값들이 risk free rate 대비 얼마나 초과 수익률이발생하는지 구해봅니다.

RFR 에 해당하는 열(여기서는 O열)에 달러를 붙이고 옆으로 주욱 드래그해도 되겠죠.

만든 수식들의 아래값을 채웁니다.(구획 설정 후 더블클릭하면 채워지는 건 아시죠?)

자. 이제 각 초과 수익률 값의 평균과 표준편차를 구해봅니다.

다른 값들도 구합니다(주욱 드래그하면 쉽게 채워집니다.)

혹시나 MVF 그래프를 만드실 분들은 S&P 500의 평균과 표준편차를 표시할 때 Monthly Excess Return으로 잡으면 안됩니다. Monthly Return 을 별도로 구해서 그 값으로 하셔야 합니다.
방법은 똑같습니다. 데이터의 대상만 월초과수익에서 월수익으로 바꾸면 됩니다.

Risk free rate의 평균도 구해봅니다.

이어서 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을 찾아서 집어넣습니다.

결과값입니다.

결과적으로 다음의 값들이 도출됩니다.

해찾기를 사용하기 위해 창을 열어봅니다.
데이터 탭의 해찾기를 누르고

교수님의 기존 연습 문제에는 위에서 구한 공분산 표 아래에
각 변수들의 포트폴리오로 이루어진 각 변수 확률에 공분산을 곱한 값의 서식이 있습니다.

그리고 그 서식들의 합이 PF var로 주어졌었죠.
이 값이 목표설정에 해당하는 셀이었습니다.

으로 기본틀이 만들어졌습니다.

이제 하나하나 해보겠습니다.

실행할때 아래의 메세지 나오는 거 정상입니다.

이와 같이 하나하나 제한조건의 $W$65를 65부터 차례대로 72까지 변경해가며 내용을 채웁니다.

이렇게 채워집니다.

역시 해찾기입니다만, 내용이 달라졌습니다.
목표설정이 PF Var($Z$47)이 아닌 PF S($z$53)입니다.
또한 위의 문제를 풀기 위해서는 최소값을 선택해야 했으나, 이번엔 최대값을 선택해야 합니다.
또한 위의 제한 조건에 종속에서 PF mean = MV Frontier에서 MV Frontier값을 계속 조정했으나 이번엔 필요없습니다.

나머지는 서식으로 자동으로 채워지므로

고생하셨습니다.