.實(shí)驗(yàn)四收益與風(fēng)險(xiǎn)與E*CEL金融計(jì)算實(shí)驗(yàn)四容：運(yùn)用E*cel進(jìn)展股票投資以及投資組合收益率與風(fēng)險(xiǎn)的計(jì)算；【知識(shí)準(zhǔn)備】理論知識(shí)：課本第三章收益與風(fēng)險(xiǎn)，第四章投資組合模型，第五章CAPM實(shí)驗(yàn)參考資料："金融建模—使用E*CEL和VBA"電子書(shū)第三章，第四章，第五章【實(shí)驗(yàn)工程容】請(qǐng)翻開(kāi)參考"金融建模—使用E*CEL和VBA"電子書(shū)第三章相關(guān)章節(jié)〔3.3,3.4,3.5〕完成以下實(shí)驗(yàn)1.單只股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)四組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"1單個(gè)股票回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B．期望收益的計(jì)算〔完成表格中黃色標(biāo)記的單元格的計(jì)算〕；(1)計(jì)算股票的每月收益率,在C3單元格定義=(B3-B4)/B4如下列圖〔2〕計(jì)算股票的月期望收益率，在F5單元格定義=AVERAGE($C$3:$C$62)〔3〕計(jì)算股票的年收益率，在G5單元格定義=F5*12C、方差與標(biāo)準(zhǔn)差的計(jì)算在E*CELL中方差，樣本方差，標(biāo)準(zhǔn)差，樣本標(biāo)準(zhǔn)差分別用VAR、STEDV?？梢酝ㄟ^(guò)E*CELL中的工具欄[f*]/[統(tǒng)計(jì)]。(1)計(jì)算月度股票收益率的方差,在F6單元格定義=VAR($C$3:$C$62)〔2〕計(jì)算月度股票收益率的標(biāo)準(zhǔn)差，在F7單元格定義=SQRT(F6)〔3〕計(jì)算年度股票的年收益率方差與標(biāo)準(zhǔn)差，在G6單元格定義=F6*12*12在G7單元格定義=F7*12D．通過(guò)比擬中國(guó)股市數(shù)據(jù)和美國(guó)股市數(shù)據(jù)總結(jié)收益與風(fēng)險(xiǎn)的關(guān)系2.兩資產(chǎn)股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)二之一組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"2兩資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B.組合協(xié)方差的計(jì)算，在D77單元格定義=COVAR(E5:E64,F5:F64)*12C.組合相關(guān)系數(shù)的計(jì)算，在D78單元格定義=CORREL(E5:E64,F5:F64)D.組合收益率樣本均值與樣本方差的計(jì)算在D78單元格定義=(D73+E73)/2在D79單元格定義=D74*D71+E74*E71在D80單元格定義=D74^2*D72+2*D74*E74*D77+E74^2*E72在D81單元格定義=D81^(1/2)E.注意比擬兩組樣本的相關(guān)系數(shù). >.F.試總結(jié)組合相關(guān)系數(shù)與投資組合分擔(dān)風(fēng)險(xiǎn)的關(guān)系2.多資產(chǎn)股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)二之一組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"3多資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B．組合相關(guān)系矩陣數(shù)的計(jì)算D142單元格定義為=CORREL(INDE*($C$68:$H$127,0,$B142),INDE*($C$68:$H$127,0,D$140))其他類似不需自己實(shí)驗(yàn)C.組合協(xié)方差矩陣的計(jì)算先選定協(xié)方差矩陣單元格區(qū)域〔如下列圖一〕，然后定義為=D142:I147*TRANSPOSE(C136:H136)*C136:H136，〔如下列圖二〕，輸入完需同按Ctrl+Shift+Enter〔如下列圖三〕3.組合收益率樣本均值與樣本方差的計(jì)算定義單元格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.組合投資分散風(fēng)險(xiǎn)的作用是不是十清楚顯？5.(選作)參考前面實(shí)驗(yàn)已完成的"實(shí)驗(yàn)四組合的回報(bào)與風(fēng)險(xiǎn).*ls〞，嘗試自己收集你感興趣的3-6只股票的原始數(shù)據(jù)完成：A．單只股票收益與風(fēng)險(xiǎn)的計(jì)算；B．多支股票組合收益與風(fēng)險(xiǎn)的計(jì)算?！緦?shí)驗(yàn)工程原理】參考資料實(shí)驗(yàn)參考材料的Doc文件【實(shí)驗(yàn)工程步驟與結(jié)果】1.單只股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)四組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"1單個(gè)股票回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B．期望收益的計(jì)算〔完成表格中黃色標(biāo)記的單元格的計(jì)算〕；(1)計(jì)算股票的每月收益率,在C3單元格定義=(B3-B4)/B4如下列圖〔2〕計(jì)算股票的月期望收益率，在F5單元格定義=AVERAGE($C$3:$C$62)〔3〕計(jì)算股票的年收益率，在G5單元格定義=F5*12C、方差與標(biāo)準(zhǔn)差的計(jì)算在E*CELL中方差，樣本方差，標(biāo)準(zhǔn)差，樣本標(biāo)準(zhǔn)差分別用VAR、STEDV?？梢酝ㄟ^(guò)E*CELL中的工具欄[f*]/[統(tǒng)計(jì)]。(1)計(jì)算月度股票收益率的方差,在F6單元格定義=VAR($C$3:$C$62)〔2〕計(jì)算月度股票收益率的標(biāo)準(zhǔn)差，在F7單元格定義=SQRT(F6)〔3〕計(jì)算年度股票的年收益率方差與標(biāo)準(zhǔn)差，在G6單元格定義=F6*12*12在G7單元格定義=F7*12D．通過(guò)比擬中國(guó)股市數(shù)據(jù)和美國(guó)股市數(shù)據(jù)總結(jié)收益與風(fēng)險(xiǎn)的關(guān)系中國(guó)股市預(yù)期收益和方差都比美國(guó)股市高，說(shuō)明中國(guó)股市收益高，風(fēng)險(xiǎn)大，美國(guó)股市收益低，風(fēng)險(xiǎn)小。2.兩資產(chǎn)股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)二之一組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"2兩資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B.組合協(xié)方差的計(jì)算，在D77單元格定義=COVAR(E5:E64,F5:F64)*12C.組合相關(guān)系數(shù)的計(jì)算，在D78單元格定義=CORREL(E5:E64,F5:F64)D.組合收益率樣本均值與樣本方差的計(jì)算在D79單元格定義=(D73+E73)/2. >.在D80單元格定義=D74*D71+E74*E71在D81單元格定義=D74^2*D72+2*D74*E74*D77+E74^2*E72在D82單元格定義=D81^(1/2)E.注意比擬兩組樣本的相關(guān)系數(shù)組合1的相關(guān)系數(shù)大，組合2的相關(guān)系數(shù)小F.試總結(jié)組合相關(guān)系數(shù)與投資組合分擔(dān)風(fēng)險(xiǎn)的關(guān)系相關(guān)系數(shù)大，投資組合分擔(dān)分險(xiǎn)小2.多資產(chǎn)股票收益率與風(fēng)險(xiǎn)的計(jì)算A．翻開(kāi)數(shù)據(jù)文件"實(shí)驗(yàn)二之一組合的回報(bào)與風(fēng)險(xiǎn).*ls〞選擇"3多資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例〞子數(shù)據(jù)表格；B．組合相關(guān)系矩陣數(shù)的計(jì)算D142單元格定義為=CORREL(INDE*($C$68:$H$127,0,$B142),INDE*($C$68:$H$127,0,D$140))其他類似不需自己實(shí)驗(yàn)C.組合協(xié)方差矩陣的計(jì)算先選定協(xié)方差矩陣單元格區(qū)域，然后定義為=D142:I147*TRANSPOSE(C136:H136)*C136:H136，輸入完需同按Ctrl+Shift+Enter3.組合收益率樣本均值與樣本方差的計(jì)算定義單元格D157為=MMULT(C134:H134,J150:J155)輸入完需同按Ctrl+Shift+Enter定義單元格D158為=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155)輸入完需同按Ctrl+Shift+Enter