excel在投資組合理論教學(xué)中的應(yīng)用

《excel在投資組合理論教學(xué)中的應(yīng)用》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。

1、EXCEL在投資組合理論教學(xué)中的應(yīng)用李吉棟（河北經(jīng)貿(mào)大學(xué)金融學(xué)院，石家莊，050061）摘要：投資組合理論是金融學(xué)科的一個(gè)重要理論，內(nèi)容比較抽象，數(shù)學(xué)模型多，學(xué)生理解起來(lái)很困難。在投資組合理論的教學(xué)過(guò)程中，利用EXCEL的數(shù)據(jù)運(yùn)算和圖表功能，將抽象的理論知識(shí)直觀地演示出來(lái)，不但使教學(xué)內(nèi)容深入淺出，易于理解，也可以幫助學(xué)生將理論知識(shí)與投資實(shí)踐結(jié)合起來(lái)，激發(fā)學(xué)生們的學(xué)習(xí)興趣。關(guān)鍵詞：EXCEL；投資組合理論；規(guī)劃求解投資組合理論是金融學(xué)科的一個(gè)重要理論。該理論認(rèn)為，在由若干證券構(gòu)成的所有可能的投資組

2、合中，只有部分組合是有效的，理性投資者在這些有效組合中選擇最適合自己的組合?；趯?duì)證券期望收益率、標(biāo)準(zhǔn)差以及協(xié)方差的估計(jì)，我們可以找到這些有效組合，即投資組合前沿。在以往的教學(xué)實(shí)踐中，學(xué)生們普遍反映這部分內(nèi)容非常抽象，數(shù)學(xué)模型多，難以把這些理論和模型與投資實(shí)踐聯(lián)系起來(lái)，理解起來(lái)很困難。如果在教學(xué)過(guò)程中能夠利用中國(guó)證券市場(chǎng)的真實(shí)數(shù)據(jù)，將這些數(shù)學(xué)模型演算一下，再借助于必要的圖形分析，使這些抽象的理論和模型能夠直觀地演示出來(lái)，對(duì)學(xué)生理解教學(xué)內(nèi)容會(huì)大有幫助。EXCEL是一款功能強(qiáng)大的電子表格數(shù)據(jù)處理軟件

3、，而且具備豐富的圖表演示功能，非常適合在投資組合理論教學(xué)中使用。下面筆者將結(jié)合自己的教學(xué)實(shí)踐，介紹在投資組合理論教學(xué)中如何應(yīng)用EXCEL。一、運(yùn)用EXCEL演示證券的相關(guān)性對(duì)組合風(fēng)險(xiǎn)的影響投資組合理論的核心思想就是風(fēng)險(xiǎn)的分散化。投資者之所以要持有多個(gè)證券，其根本原因就在于部分風(fēng)險(xiǎn)會(huì)隨著持有證券個(gè)數(shù)的增加而有所降低。影響證券組合風(fēng)險(xiǎn)分散化效果的一個(gè)重要因素就是證券之間的相關(guān)性。我們一般是通過(guò)分析兩個(gè)風(fēng)險(xiǎn)證券的組合來(lái)幫助學(xué)生理解證券相關(guān)性與風(fēng)險(xiǎn)分散化之間的關(guān)系。首先，假設(shè)市場(chǎng)上有兩個(gè)風(fēng)險(xiǎn)證券，知道這

4、兩個(gè)證券的期望收益率、標(biāo)準(zhǔn)差和相關(guān)系數(shù)，計(jì)算出這兩個(gè)證券的不同權(quán)重組合的期望收益率和標(biāo)準(zhǔn)差，畫(huà)出投資組合曲線；然后改變兩個(gè)證券的相關(guān)系數(shù)，比較投資組合曲線的變動(dòng)情況。市場(chǎng)上兩個(gè)風(fēng)險(xiǎn)證券的相關(guān)數(shù)據(jù)如圖1所示。按照期望收益率和標(biāo)準(zhǔn)差的計(jì)算公式，計(jì)算出一系列不同權(quán)重組合的期望收益率和標(biāo)準(zhǔn)差，用EXCEL繪圖功能中的散點(diǎn)圖，畫(huà)出這一系列不同權(quán)重組合期望收益率和標(biāo)準(zhǔn)差的相關(guān)關(guān)系圖，如圖1所示。圖1相關(guān)系數(shù)為0.5時(shí)的兩證券組合再利用EXCEL的微調(diào)按鈕，調(diào)整證券的相關(guān)系數(shù)。添加微調(diào)按鈕的方法是：點(diǎn)擊“視圖

5、”按鈕下面的“工具欄”，選擇“窗體”，點(diǎn)擊微調(diào)項(xiàng)，在B4單元格處添加微調(diào)按鈕（如圖1），在微調(diào)按鈕上點(diǎn)擊右鍵，在對(duì)話框中設(shè)置當(dāng)前值、步長(zhǎng)和單元格鏈接（$B$4），再設(shè)定相關(guān)系數(shù)單元格與微調(diào)按鈕鏈接單元格的關(guān)聯(lián)，C4=B4/10-1，如圖1所示。然后用鼠標(biāo)點(diǎn)擊微調(diào)按鈕的向上箭頭或向下箭頭，調(diào)整相關(guān)系數(shù)大小，證券組合曲線也隨之變動(dòng)，如圖2所示。通過(guò)EXCEL的微調(diào)按鈕，教師在PPT上可以連續(xù)地調(diào)整相關(guān)系數(shù)，圖中的曲線也隨之連續(xù)地移動(dòng)，證券相關(guān)系數(shù)與證券組合標(biāo)準(zhǔn)差之間的關(guān)系就直觀的演示出來(lái)了。圖2利用

6、微調(diào)按鈕調(diào)整相關(guān)系數(shù)后的兩證券組合二、運(yùn)用ECEL模擬證券組合的可行集和有效集證券組合的有效集是可行集的子集，它們滿足的條件是：在所有期望收益率相同的組合中，它們的標(biāo)準(zhǔn)差最??；在所有標(biāo)準(zhǔn)差相同的組合中，它們的期望收益率最大。如果能利用中國(guó)證券市場(chǎng)的真實(shí)數(shù)據(jù)，模擬出一組證券組合的可行集區(qū)域，學(xué)生們就可以非常直觀地理解有效集和投資組合前沿的概念了。下面通過(guò)一個(gè)例題來(lái)說(shuō)明模擬證券組合可行集的過(guò)程。首先找到4個(gè)證券，西山煤電、浦發(fā)銀行、中國(guó)衛(wèi)星和新興鑄管，選取2006年8月到2011年8月的月度收盤(pán)價(jià)格

7、數(shù)據(jù)（本例題的數(shù)據(jù)來(lái)自于大智慧，為復(fù)權(quán)后數(shù)據(jù)），計(jì)算它們每一期的收益率，以及平均收益率、收益率標(biāo)準(zhǔn)差和協(xié)方差數(shù)據(jù)，如圖3所示（第5行到第61行數(shù)據(jù)隱藏）。收益率均值用AVERAGE()函數(shù)計(jì)算，標(biāo)準(zhǔn)差用STDEV()函數(shù)計(jì)算，協(xié)方差矩陣用數(shù)據(jù)分析工具庫(kù)中的協(xié)方差工具產(chǎn)生，具體步驟是：點(diǎn)擊“工具”中的“數(shù)據(jù)分析”按鈕（如果沒(méi)有安裝，需要點(diǎn)擊“加載宏”，選擇“分析工具庫(kù)”安裝），選擇“協(xié)方差”，在對(duì)話框的輸入?yún)^(qū)域選擇F1:I62，在輸出區(qū)域選擇A67，選中“標(biāo)志位于第一行”后點(diǎn)擊確定，得到左下半部分

8、的協(xié)方差矩陣，再根據(jù)協(xié)方差矩陣的對(duì)稱(chēng)關(guān)系補(bǔ)齊右上半部分即可。圖3證券平均收益率、標(biāo)準(zhǔn)差和協(xié)方差矩陣計(jì)算有了基礎(chǔ)數(shù)據(jù)后就可以進(jìn)行數(shù)據(jù)模擬了，具體步驟是：首先用RAND()在A74:D74區(qū)域產(chǎn)生4個(gè)均勻分布的隨機(jī)數(shù)，按四個(gè)隨機(jī)數(shù)的相同比例折算成4個(gè)證券的權(quán)重，如圖4所示。然后利用隨機(jī)產(chǎn)生的權(quán)重?cái)?shù)據(jù)計(jì)算該組合的期望收益率和標(biāo)準(zhǔn)差，期望收益率的計(jì)算公式為B80=MMULT(F64:I64,TRANSPOSE(A77:D77))，標(biāo)準(zhǔn)差的計(jì)算公式為C80=SQRT(MMULT(MMULT(A77:D77