實驗四 收益與風險與EXCEL金融計算實驗四內容：運用Excel進行股票投資以及投資組合收益率與風險的計算；【知識準備】理論知識：課本第三章 收益與風險，第四章 投資組合模型，第五章 CAPM實驗參考資料：金融建模使用EXCEL和VBA電子書 第三章，第四章，第五章【實驗項目內容】請打開參考金融建模使用EXCEL和VBA電子書 第三章相關章節（3.3,3.4,3.5）完成以下實驗1.單只股票收益率與風險的計算A打開數據文件“實驗四組合的回報與風險.xls”選擇“1單個股票回報與風險計算實例”子數據表格；B期望收益的計算（完成表格中黃色標記的單元格的計算）；(1)計算股票的每月收益率, 在C3單元格定義=(B3-B4)/B4如下圖 （2）計算股票的月期望收益率，在F5單元格定義=AVERAGE($C$3:$C$62) （3）計算股票的年收益率，在G5單元格定義=F5*12C、方差與標準差的計算在EXCELL中方差，樣本方差，標準差，樣本標準差分別用VAR、STEDV?？梢酝ㄟ^EXCELL中的工具欄fx/統計。(1)計算月度股票收益率的方差, 在F6單元格定義=VAR($C$3:$C$62) （2）計算月度股票收益率的標準差，在F7單元格定義=SQRT(F6)（3）計算年度股票的年收益率方差與標準差，在G6單元格定義=F6*12*12在G7單元格定義=F7*12D通過比較中國股市數據和美國股市數據總結收益與風險的關系2.兩資產股票收益率與風險的計算A打開數據文件“實驗二之一組合的回報與風險.xls”選擇“2兩資產組合回報與風險計算實例”子數據表格；B.組合協方差的計算，在D77單元格定義=COVAR(E5:E64,F5:F64)*12C.組合相關系數的計算，在D78單元格定義=CORREL(E5:E64,F5:F64)D.組合收益率樣本均值與樣本方差的計算在D78單元格定義=(D73+E73)/2在D79單元格定義=D74*D71+E74*E71在D80單元格定義=D742*D72+2*D74*E74*D77+E742*E72在D81單元格定義=D81(1/2)E.注意比較兩組樣本的相關系數F.試總結組合相關系數與投資組合分擔風險的關系2.多資產股票收益率與風險的計算A打開數據文件“實驗二之一組合的回報與風險.xls”選擇“3多資產組合回報與風險計算實例”子數據表格；B組合相關系矩陣數的計算D142單元格定義為=CORREL(INDEX($C$68:$H$127,0,$B142),INDEX($C$68:$H$127,0,D$140)其他類似不需自己實驗C.組合協方差矩陣的計算先選定協方差矩陣單元格區域（如下圖一），然后定義為=D142:I147*TRANSPOSE(C136:H136)*C136:H136，（如下圖二），輸入完需同按Ctrl+Shift+Enter（如下圖三）3.組合收益率樣本均值與樣本方差的計算定義單元格D157為=MMULT(C134:H134,J150:J155) 輸入完需同按Ctrl+Shift+Enter定義單元格D158為=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155) 輸入完需同按Ctrl+Shift+Enter定義單元格D159為=SQRT(D158)4.組合投資分散風險的作用是不是十分明顯？5.(選作)參考前面實驗已完成的“實驗四組合的回報與風險.xls”，嘗試自己收集你感興趣的3-6只股票的原始數據完成：A單只股票收益與風險的計算；B多支股票組合收益與風險的計算?！緦嶒烅椖吭怼繀⒖假Y料實驗參考材料的Doc文件【實驗項目步驟與結果】1.單只股票收益率與風險的計算A打開數據文件“實驗四組合的回報與風險.xls”選擇“1單個股票回報與風險計算實例”子數據表格；B期望收益的計算（完成表格中黃色標記的單元格的計算）；(1)計算股票的每月收益率, 在C3單元格定義=(B3-B4)/B4如下圖 （2）計算股票的月期望收益率，在F5單元格定義=AVERAGE($C$3:$C$62) （3）計算股票的年收益率，在G5單元格定義=F5*12C、方差與標準差的計算在EXCELL中方差，樣本方差，標準差，樣本標準差分別用VAR、STEDV?？梢酝ㄟ^EXCELL中的工具欄fx/統計。(1)計算月度股票收益率的方差, 在F6單元格定義=VAR($C$3:$C$62) （2）計算月度股票收益率的標準差，在F7單元格定義=SQRT(F6)（3）計算年度股票的年收益率方差與標準差，在G6單元格定義=F6*12*12在G7單元格定義=F7*12D通過比較中國股市數據和美國股市數據總結收益與風險的關系中國股市預期收益和方差都比美國股市高，說明中國股市收益高，風險大，美國股市收益低，風險小。2.兩資產股票收益率與風險的計算A打開數據文件“實驗二之一組合的回報與風險.xls”選擇“2兩資產組合回報與風險計算實例”子數據表格；B.組合協方差的計算，在D77單元格定義=COVAR(E5:E64,F5:F64)*12C.組合相關系數的計算，在D78單元格定義=CORREL(E5:E64,F5:F64)D.組合收益率樣本均值與樣本方差的計算在D79單元格定義=(D73+E73)/2在D80單元格定義=D74*D71+E74*E71在D81單元格定義=D742*D72+2*D74*E74*D77+E742*E72在D82單元格定義=D81(1/2)E.注意比較兩組樣本的相關系數組合1的相關系數大，組合2的相關系數小F.試總結組合相關系數與投資組合分擔風險的關系相關系數大，投資組合分擔分險小2.多資產股票收益率與風險的計算A打開數據文件“實驗二之一組合的回報與風險.xls”選擇“3多資產組合回報與風險計算實例”子數據表格；B組合相關系矩陣數的計算D142單元格定義為=CORREL(INDEX($C$68:$H$127,0,$B142),INDEX($C$68:$H$127,0,D$140)其他類似不需自己實驗C.組合協方差矩陣的計算先選定協方差矩陣單元格區域，然后定義為=D142:I147*TRANSPOSE(C136:H136)*C136:H136，輸入完需同按Ctrl+Shift+Enter3.組合收益率樣本均值與樣本方差的計算定義單元格D157為=MMULT(C134:H134,J150:J155) 輸入完需同按Ctrl+Shift+Enter定義單元格D158為=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155) 輸入完需同按Ctrl+Shift+Enter定義單元