實(shí)驗(yàn)四 收益與風(fēng)險(xiǎn)與EXCEL金融計(jì)算

1、實(shí)驗(yàn)四 收益與風(fēng)險(xiǎn)與excel金融計(jì)算實(shí)驗(yàn)四內(nèi)容：運(yùn)用excel進(jìn)行股票投資以及投資組合收益率與風(fēng)險(xiǎn)的計(jì)算；【知識(shí)準(zhǔn)備】理論知識(shí)：課本第三章 收益與風(fēng)險(xiǎn)，第四章 投資組合模型，第五章 capm實(shí)驗(yàn)參考資料：金融建模使用excel和vba電子書(shū) 第三章，第四章，第五章【實(shí)驗(yàn)項(xiàng)目?jī)?nèi)容】請(qǐng)打開(kāi)參考金融建模使用excel和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).xls”選擇“1單個(gè)股票回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例”子數(shù)據(jù)表格；b期望收益的計(jì)算（完成表格中黃色標(biāo)記的單元格的計(jì)算）；(1)計(jì)算股票的每月收益率, 在c3單

2、元格定義=(b3-b4)/b4如下圖 （2）計(jì)算股票的月期望收益率，在f5單元格定義=average($c$3:$c$62) （3）計(jì)算股票的年收益率，在g5單元格定義=f5*12c、方差與標(biāo)準(zhǔn)差的計(jì)算在excell中方差，樣本方差，標(biāo)準(zhǔn)差，樣本標(biāo)準(zhǔn)差分別用var、stedv?？梢酝ㄟ^(guò)excell中的工具欄fx/統(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ù)和

3、美國(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).xls”選擇“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單元格定義=d742*d72+2*d74*e74*d77+e742*e72在d81單元格定義=d81(1/2)e.注意比較

4、兩組樣本的相關(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).xls”選擇“3多資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例”子數(shù)據(jù)表格；b組合相關(guān)系矩陣數(shù)的計(jì)算d142單元格定義為=correl(index($c$68:$h$127,0,$b142),index($c$68:$h$127,0,d$140)其他類似不需自己實(shí)驗(yàn)c.組合協(xié)方差矩陣的計(jì)算先選定協(xié)方差矩陣單元格區(qū)域（如下圖一），然后定義為=d142:i147*transpose(c136:h136)*c136:h136，（如下圖二），輸入完需同按ctrl+shift+ent

5、er（如下圖三）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).xls”，嘗試自己收集你感興趣的3-6只股票的原始數(shù)據(jù)完成：a單只股票收益與風(fēng)險(xiǎn)的計(jì)算；b多支股票組合收益與

6、風(fēng)險(xiǎn)的計(jì)算?！緦?shí)驗(yàn)項(xiàng)目原理】參考資料實(shí)驗(yàn)參考材料的doc文件【實(shí)驗(yàn)項(xiàng)目步驟與結(jié)果】1.單只股票收益率與風(fēng)險(xiǎn)的計(jì)算a打開(kāi)數(shù)據(jù)文件“實(shí)驗(yàn)四組合的回報(bào)與風(fēng)險(xiǎn).xls”選擇“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ì)算在excell中方差，樣本方差，標(biāo)準(zhǔn)差，樣本標(biāo)準(zhǔn)差分別用var、stedv。可以通過(guò)excell中

7、的工具欄fx/統(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).xls”選擇“2兩資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例”子數(shù)據(jù)表格；b.組合協(xié)方差的計(jì)算，在d77單元格定義=c

8、ovar(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單元格定義=d742*d72+2*d74*e74*d77+e742*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).x

9、ls”選擇“3多資產(chǎn)組合回報(bào)與風(fēng)險(xiǎn)計(jì)算實(shí)例”子數(shù)據(jù)表格；b組合相關(guān)系矩陣數(shù)的計(jì)算d142單元格定義為=correl(index($c$68:$h$127,0,$b142),index($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定義單元