Monte Carlo Method 엑셀로 간단히 만들어보기

*2021-11-23 글

최근 카지노에서 공의 위치와 속도, 휠의 속도와 가속도 등을 계산해 수많은 돈을 챙겨 나간 일당의 이야기를 들어보셨나요?

이에 이어 러시아의 수학자인 Andrey Markov는, 독립적이지 않은 상황도 패턴을 따른다는 것을 증명하기 위해 시 작품에서 수천 개의 문자 쌍을 세곤 하였습니다.

그 쌍을 사용하여 각 문자의 조건부 확률을 계산한 결과, 앞에서 특정 문자나 공백이 주어지면 다음 문자가 A, T 또는 공백이 될 가능성이 높다는 것을 증명해내었습니다.

정말 신기한 확률적 분석의 세계입니다.

그래서 준비했습니다.

Monte Carlo Method은 통계적분석방법 중 하나로, "미래는 불확실하다"는 가정 하에 난수(Random Number)를 활용한 "반복적 무작위 시뮬레이션" 방법입니다.

1. 종가 기준 수익률의 로그표현

=LN(B2/B3)

2. 평균 수익률과 표준편차의 계산

평균 수익률

=AVERAGE(C3:C582)

표준편차

=STDEV(C3:C582)

3. 1달 & 1년 치 계산

시장은 7일 중 5일 활성화된다는 가정하에,

1달의 경우 20, 1년의 경우 250을 곱해주어 계산합니다.

1달 평균 수익률

=F2*20^(1/2)

1년 평균 수익률

=F2*250^(1/2)

1달 표준편차

=F3*20^(1/2)

1년 표준편차

=F3*250^(1/2)

4. 실험횟수 & 난수 적용하기

실험횟수엔 순차적인 숫자를,

그 옆엔

=마지막 종가*(1+NORMINV(RAND(),평균수익률,표준편차))

를 기입해주시면 됩니다.

마지막 종가가 아닌 첫번째 종가를 사용하게 되면, 미래의 평균 수익률과 표준편차를 사용한 것이 되기에 논리에 맞지 않습니다.

NORMINV함수 내 RAND함수는 난수를 의미합니다.

=$B$582*(1+NORMINV(RAND(),$F$2,$F$3))

1Day에 일일 수익률과 표준편차를 사용하였으니, 1Month 와 1Year에는 각각 1달치와 1년치 값을 사용해주면 되겠죠?

1Month

=$B$582*(1+NORMINV(RAND(),$F$4,$F$6))

1Year

=$B$582*(1+NORMINV(RAND(),$F$5,$F$7))

5. 최소값 & 최대값

4번과정이 마무리 된 이후 실험 횟수를 늘려주세요. 저의 경우엔 1000번까지 늘렸습니다.

그 다음으로, 난수가 적용 된 예상값의 최소값과 최대값을 만들어 주세요

=MIN(I2:I1001)

=MAX(I2:I1001)

6 구간 및 빈도수

첫번째 구간은 다음과 같이 계산해주시면 됩니다.

최소값+(최대값-최소값)/구간 수

저의 경우는 20개 구간을 만들 것이기에 /20으로 나타내었습니다.

=N3+(N4-N3)/20

그 다음 구간 부터는

이전 구간 값+(최대값-최소값)/구간 수

=P3+($N$4-$N$3)/20

2번째 구간의 식을 구간수에 맞게 내려주시면 됩니다.

그 다음 구간수에 맞게 잡아주시고 frequency함수를 적어주시면 됩니다.

=FREQUENCY(I2:I1001,P3:P22)

이상태까지 되셨다면, Enter가 아닌

Ctrl + Shift + Enter

을 함께 눌러주세요.

분포도 완성(Day)

Month 와 Year또한 위의 과정으로 진행해주시면 됩니다.

추가

1. 신뢰도 구간 함수

먼저 예상값들의 평균과 표준편차를 만든 이후,

신뢰도 95%구간 상한선 = 평균+NORMSINV(0.95)*표준편차/SQRT(실험횟수)

=N5+NORMSINV(0.95)*N6/SQRT(1000)

신뢰도 95%구간 하한선 = 평균-NORMSINV(0.95)*표준편차/SQRT(실험횟수)

=N5-NORMSINV(0.95)*N6/SQRT(1000)

2. 변동계수

표준편차/평균

=N6/N5

변동계수가 낮을수록 분석값에서의 편차가 적다는 것(정밀도)을 의미합니다.

3. 왜도(Skewness) 및 첨도(Kurtosis)

SKEW와 KURT를 사용해주시면 됩니다.

=SKEW(I2:I1001)

=KURT(I2:I1001)

왜도(Skewness)는 0에서 많이 벗어나 있을수록 정규분포 대비 좌측 혹은 우측으로 많이 치우쳐져있다는 것을 의미합니다.

첨도(Kurtosis)는 0에서 많이 벗어나 있을수록 정규분포 대비 더 완만하거나 가파른 것을 의미합니다.

비교

실제 값과 예상된 값을 비교해 본 결과,

하루 뒤의 값과는 0.6%, 한 달 뒤의 값과의 오차는 -2.9% 를 나타낸 것으로 보였습니다.

표본을 1000개밖에 사용하지 않아 정밀도가 떨어지는 것으로 판단됩니다.

금융자산의 수익률은 정규분포에 가까울 것이라는 가정 하에 몬테카를로 시뮬레이션이 유의미해지는 것이라, 주로 학술적 뒷받침으로 사용되며 실무적으로는 사용되지 않는 모습입니다.

그러나 동시에, 몬테카를로 분석을 표준편차의 추세와 수익률의 추세까지 포함하여 계산해보면 어떨지 궁금하기도 합니다.

이후 발전 된 시뮬레이션으로 돌아오겠습니다.