實驗四：無風(fēng)險資產(chǎn)與多種風(fēng)險型資產(chǎn)最優(yōu)投資組合的模型分析

一、實驗?zāi)康?/p>

通過上機實驗，使學(xué)生充分理解Excel軟件系統(tǒng)管理和根本原理,掌握多

資產(chǎn)投資組合優(yōu)化的Excel應(yīng)用。

二、預(yù)備知識

（一）相關(guān)的計算機知識：Windows操作系統(tǒng)的常用操作；數(shù)據(jù)庫的根底知識；Excel

軟件的根本操作。

（二）實驗理論預(yù)備知識

現(xiàn)代資產(chǎn)組合理論發(fā)端于Markowitz（1952）提出的關(guān)于投資組合的理論。該理論假設(shè)

投資者只關(guān)心金融資產(chǎn)（組合）收益的均值（期望收益）和方差，在一定方差下追求盡可

能高的期望收益，或者在一定的期望水平上盡可能降低投資收益的方差。投資者的效用是

關(guān)于投資組合的期望回報率和方差的函數(shù)，理性的投費者通過選擇有效地投資組合以實現(xiàn)

期望效用最大。該理論第一次將統(tǒng)計學(xué)中期望與方差的概念引入投資組合的研究，提出用

資產(chǎn)收益率的期望來衡量預(yù)期收益.，用資產(chǎn)預(yù)期收益勺標(biāo)準差來度量風(fēng)險的思想。

1、理論假設(shè)

（I）市場上存在n22種風(fēng)險資產(chǎn)，資產(chǎn)的收益率服從多元正態(tài)分布，允許賣空行為的

存在。?=（助⑷2，.,&）,},代表投資到這n種資產(chǎn)上的財富［投資資金）相對份額，

它是n維列向量，有￡例=1,允許例＜0,即賣空不受限制。

/=1

（II）用e表示所有由n種風(fēng)險資產(chǎn)的期望收益率組成的列向量。

￡=R=（&,R：）（1）

G表示資產(chǎn)組合的收益率，石儲,）和。儲,）分別為資產(chǎn)組合〃的期望收益率和收益率標(biāo)準

差。

E（O）=0/.6=汽多（2）

1=1

（III）假設(shè)n種資產(chǎn)的收益是非共線性的（其經(jīng)濟意義為：沒有任何一種資產(chǎn)的期望收益率

可以通過其他資產(chǎn)的線性組合來得到，它們的期望收前是線性獨立的。）o這樣它們的方

差-協(xié)方差矩陣可以表示為：

Q=W?.；?/〃,⑶

由于總是假定非負的總體方差，它還必須是一個正定矩陣，即對于任何非0的n維列向量

都有因此整個資產(chǎn)組合的總體方差為：

(4)

1=17=1

2、均值——方差模型

經(jīng)典的均值一一方差模型可以表述為：

mincr；=(JQco(5)

(6)

*1

。?e=E(9)=R〃⑺

利用拉格朗日乘數(shù)法可以計算在既定期望收益(e)的前提下最小風(fēng)險曲線方程為⑻式:

a；(助)-A/C)2

22;

(y=———―?(E(r)--)+—=>―、-=1(8)

「BC-A2pCC1/CD/C2

該式證明可見(Merton1974),其中

C=〃Q-ZA=〃Q"e=，Q"/,8=e7Q-7,O=8C—A2,/為單位陣。均值一方差模型的解在

空間中是圖1中的弧線，0G是投資組合的有效前沿，即投資者根據(jù)此線上的

點進行投資決策。當(dāng)只考慮風(fēng)險資產(chǎn)時，相應(yīng)的最小方差組合叫點的解(見圖2］為:

C；=_L,￡(7)=4如果進一步考慮無風(fēng)險資產(chǎn)，投資者可以在資本市場借貸，以調(diào)整風(fēng)

險和收益時，有效集位于直線zyGK上(見圖1),G為切點,。為無風(fēng)險利率。同樣利用

拉格朗日乘數(shù)法可以計算出資本市場線的直線方程為:

E(%)=rf-A+zy-C⑼

圖I均值一方差模型幾何示意圖圖2最小方差拋物線和效率邊界

因此，投資者以無風(fēng)險利率。借入資金,并連同已有資金一起全部投資于某一風(fēng)險資產(chǎn),

所形成組合的預(yù)期收益和風(fēng)險(標(biāo)準差)正好使組合位于無風(fēng)險資產(chǎn)的線段的延長線上。

圖3最小標(biāo)準方差雙曲線和效率邊界

3、多種風(fēng)險資產(chǎn)的最優(yōu)投資組合

以上分析說明，在投資組合的期望收益率隊與投資組合標(biāo)準差盤之間的關(guān)系曲線上，

存在一個最低風(fēng)險(標(biāo)準差)的投資組合(即最優(yōu)投資組合)，該投資組合的各項資產(chǎn)投資

比重矩陣的計算公式為：

4、無風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)的最優(yōu)投資組合

當(dāng)無風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)的構(gòu)成投資組合時，首先可以計算出多種風(fēng)險資產(chǎn)的最優(yōu)

投資組合，即資本配置線與風(fēng)險資產(chǎn)的有效邊界相切的那一點。切點所代表的最有投資組

合的期望收益率(和標(biāo)準差叫，的計算公式分別為：

PP2

A-CRF\C(A-CRF)C

式中為無風(fēng)險資產(chǎn)的收益率。參數(shù)A、B、C、D的計算同上。

在多種風(fēng)險資產(chǎn)的最有投資組合中各種風(fēng)險資產(chǎn)的投資比重為：

W一。九"/)

A-CRF

無風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)構(gòu)成的最優(yōu)投資組合，其風(fēng)險和收益落在資本配置線(CAL)上，

計算公式為：即二七+—?/一也」為該直線的斜率.

P

'CRP-ACRP~A

三、實驗內(nèi)容

根據(jù)現(xiàn)代資產(chǎn)組合理論，計算無風(fēng)險資產(chǎn)和風(fēng)險資產(chǎn)間的最優(yōu)投資組合比重，建立無

風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析電子模型。

四、實驗步驟

本實驗通過具體的實例應(yīng)用展開。

1、多種風(fēng)險資產(chǎn)的最優(yōu)投資組合。

5種風(fēng)險資產(chǎn)U、V、W、X、Y的期望收益率、標(biāo)準差，以及它們兩兩之間的相關(guān)系數(shù)

如下表所示，試計算最有投資組合，并繪制該投資組合的期望收益率與標(biāo)準差之間的關(guān)系

由線。具體步驟如下：

(1)在單元格C19中輸入公式二C10*$D$3*D3,在單元格D19中輸入公式=D19*$D$4*D3,

在單元格E19中輸入公式;E1O$D$5*D3,在單元格F19中輸入公式=F1O*$D$6*D3,在單元

格G19中輸入公式二G10*$D$7*D3。然后選取單元格區(qū)域C19：G19,將其向下一直填充復(fù)

制到單元格區(qū)域C23：G23,得到協(xié)方差矩陣。

(2)選取單元格C25,在名稱框中將單元格的名稱改為A,依次將C26、D25、D26定

義名稱為B、C.、Do定義單元格名稱：依次選?。鄄迦耄?，［名稱］,［定義名稱］按鈕。

(3)在單元格C25中輸入公式

=MMULT(MMULT(TRANSPOSE(C3：C7),MINVERSE(C19：G23)),{1；1；1；1；

1}),計算參數(shù)A。

(4)在單元格C26中輸入公式

二MMULT(MMULT(TRANSPOSE(C3：C7),MINVERSE(C19：G23)),C3：C7),

計算參數(shù)B。

(5)在單元格E25中輸入公式

二MMULT(MMULT({1,1,1,1,1),MINVERSE(C19：G23)),{1；1；1；1；1}),

計算參數(shù)c。這里{i,i,i,i,i}表示單位行向量；n；i；i；i；i}表示單位列向量。

(6)在單元格E26中輸入公式=B*C.-AA2,計算參數(shù)D。

(7)在單元格C28中輸入公式二A/C.,計算最優(yōu)投資組合的期望收益率：在單元格E28中

輸入公式二SQRT(1/C.),計算最優(yōu)投資組合的標(biāo)準差。

(8)選取單元格區(qū)域C30：G30,輸入數(shù)組公式二TRANSPOSE((C?C28-A)

/D*MMULT(MINVERSE(C19:G23),C3:C7)+(B-A*C28)/D*MMULT(MINVERSE(C19:G23),{

1；1；1；1；1})),計算最優(yōu)投資組合中各資產(chǎn)的投資比例。計算結(jié)果如下表所示。

(9)設(shè)計繪制圖表的數(shù)據(jù)格式。在單元格13：H7中輸入給定的期望收益率數(shù)據(jù).

(10)在單元格J3中輸入公式=SQRT(C./D*(I3-A/C.”2+1/C.),并將此單元格向下一直填充

復(fù)制到單元格J17,計算不同期望收益率下的標(biāo)準差。

(II)選取單元格區(qū)域13：J17,單擊工具欄上的［圖表向?qū)В莅粹o，作期望收益率和標(biāo)準差

的平滑散點圖，方法同前面的實驗。

(12)在圖表區(qū)域中單擊鼠標(biāo)右鍵，在彈出的快捷鍵中選擇翻開［源數(shù)據(jù)］,在其對話框中翻

開系列選項卡，單擊［添加］按鈕，然后在［X值］欄中輸入=sheeil!$D$3:$D$7,在［Y值］欄中輸

入=$1^自1!$C$3:$C$7,然后單擊確定按鈕。關(guān)于圖形的修飾方法同前。

2、多種風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析模型

建立一個多種風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析模型

具體過程如下。

(1)首先設(shè)計數(shù)據(jù)的微調(diào)按鈕。以資產(chǎn)P的期望收益率為例，設(shè)計微調(diào)按鈕的方法如下：

在［視圖］菜單的［工具欄］子菜單中單擊［窗體］命令，翻開［窗體］工具欄，單擊［微調(diào)項］按鈕，

然后在D3單元格的位置拖發(fā)出一個矩形［微調(diào)項］控件，并調(diào)整其大??；將鼠標(biāo)移到新建立

的［微調(diào)項］控件上，單擊右鍵，出現(xiàn)快捷菜單中的［設(shè)置控件格式］命令，翻開［設(shè)置控件格式］

對話框，翻開［控制］選項卡，在［單元格鏈接］欄中填入$口$3,然后單擊［確定］按鈕，這樣就

建立了資產(chǎn)P的微調(diào)按鈕。用同樣的方法可以建立其他資產(chǎn)的期望收益率、標(biāo)準差以及相

關(guān)系數(shù)的微調(diào)按鈕。

(2)建立各種資產(chǎn)期望收益率和標(biāo)準差數(shù)值與［微調(diào)項］控件的聯(lián)系。在單元格C3中輸入

=D3/10()(),并將其向卜填充復(fù)制到單兀格C7,建立期望收益率微調(diào)按鈕與期望收益率數(shù)

值的關(guān)系。在單元格E3中輸入二F3/1000,并將其向下填充復(fù)制到單元格E7,建立標(biāo)準差

微調(diào)按鈕與標(biāo)準差數(shù)值的關(guān)系，使微調(diào)按鈕每次調(diào)整對應(yīng)的參數(shù)數(shù)值變動0.1%0

(3)建立各種資產(chǎn)之間相關(guān)系數(shù)數(shù)值與相關(guān)系數(shù)微調(diào)按鈕的關(guān)系。計算各種資產(chǎn)之間的相

關(guān)系數(shù)的單元格及相應(yīng)的公式如下：

單元格DIO,=C11/100-1,為資產(chǎn)P與Q的相關(guān)系數(shù)；單元格E10,=C12/100-1,為資產(chǎn)P

與R的相關(guān)系數(shù)；單元格F10,=C13/100-1,為資產(chǎn)P與S的相關(guān)系數(shù)；

單元格GIO,=C14/100-1,為資產(chǎn)P與T的相關(guān)系數(shù);單元格Eli,=D12/100-1,為資產(chǎn)Q

與R的相關(guān)系數(shù)；單元格F11,=D13/100-1,為資產(chǎn)Q與S的相關(guān)系數(shù)；單元格G11,

=D14/100-1,為資產(chǎn)Q與T的相關(guān)系數(shù);單元格F12,=EI3/1()()-1,為資產(chǎn)R與S的相關(guān)

系數(shù)；單元格G12,=E14/100-1,為資產(chǎn)R與T的相關(guān)系數(shù)；單元格G13,=F14/100-1,

為資產(chǎn)s與T的相關(guān)系數(shù)。這樣可使微調(diào)按鈕每次調(diào)整對應(yīng)的參數(shù)值變動0.01。

(4)輸入計算協(xié)方差矩陣的公式。各單元格的計算公式為：

單元格C19,=C10*E3*E3；單元格D19,二D10*E3*E4；單元格E19,=E10*E3*E5；

單元格F19,=F10*E3*E6；單元格G19,=G10*E3*E7o

單元格C20,=D19；單元格D20,=D11*E4*E4；單元格E20,=E11*E4*E5；單元格F20,

=FI1*E4*E6：單元格G20,=GI1*E4*E7O

單元格C2L=E19；單元格D21,=E20；單元格E21,=E1元E5*E5；單元格F21,=F21*E6*E5；

單元格G2L=G12*E7*E5o

單元格C22,=F19；單元格D22,=F20：單元格E22,=F21：單元格F22,=F13*E6*E6：

單元格G22,=G13*E7*E6o

單元格C23,=G19；單元格D23,=G20；單元格E23,=G21；單元格F23,二G22；

單元格G23,=G14*E7*E7o

(5)分別選取單元格C25,C26,E25,E26,對其重新命名，分別為A,B,C.,D。

(6)在單元格C25輸入公式

=MMUET(MMULT(TRANSPOSE(C3：C7),MINVERSE(C19：G23)),{1；1；1；1：

1}),計算參數(shù)A。

(7)在單元格C26中輸入公式

=MMULT(MMULT(TRANSPOSE(C3：C7),MINVERSE(Cl9；G23)),C3：C7),

計算參數(shù)Bo

(8)在單元格E25中輸入公式

二MMULT(MMULT{1,1,1,1,1},MINVERSE(C19：G23)),{1；1；I；1；1)),

計算參數(shù)Co

(9)在單元格E26中輸入公式=B*C.-A八2,計算參數(shù)的。

(10)在單元格C28中輸入公式=A/C.,計算最優(yōu)投資組合的期望收益率；在單元格E28

中輸入公式二SQRT(1/C.),計算最優(yōu)投資組合的標(biāo)準差。

(11)選取單元格區(qū)域C30：G30,輸入下面的數(shù)組公式，計算最優(yōu)投資組合中各項資產(chǎn)的

投資比例：

=TRANSPOSE((C.*C28-A)/D*MMULT(MINVERSE(C19:G23),C3:C7)+(B-A*C28)/D*MMUL

T(MINVERSE(C19:G23),{}))o

BCDEFB

1數(shù)據(jù)

2資產(chǎn)期望收益率收益率調(diào)節(jié)標(biāo)準差標(biāo)準差調(diào)節(jié)

.

3▼

P7.60%766.20%62

4-±_l

Q5.60%568.10%81

__4

5▼▼

R10.20%1027.00%70

6▼▼

S6.70%674.50%45

7—▼±_I▼

T15.90%1596.00%60

8相關(guān)系數(shù)

9資產(chǎn)PQRST

10P10.27-0.110.480.10

_1

11，i

Q-±-12710.270.090.21

12,

—s_1

R8912710.460.12

13▲i▲i▲

_1_1

26

.—±_J

11T-±總-J1-±.-J1▼▼1

110121112126

ABCDEF

16中間計算參數(shù)

17協(xié)方差矩陣

18資產(chǎn)PQRST

19P0.38%0.14%-0.05%0.13%0.04%

20Q0.14%0.66%0.15%0.03%0.10%

21R-0.05%0.15%0.49%0.14%0.05%

22S0.13%0.03%0.14%0.20%0.07%

23T0.04%0.10%0.05%0.07%0.36%

24參數(shù)A、B、C、

D計算

25A71.53C743.59

26B10.21D2474.43

27最優(yōu)投資組

合

28期望收益率9.62%標(biāo)準差3.67%

29資產(chǎn)PQRST

30比重20.77%7.20%15.19%32.04%24.79%

31

(12)設(shè)計繪制圖表的數(shù)據(jù)格式，在單元格118：J32中輸入給定的期望收益率數(shù)據(jù)。在單

元格J18中輸入公式二SQRT(C./D*(118-A/C.『2+l/C.),并將此單元格向下一直填充復(fù)制到

單元格J23,計算不同期望收益率下的標(biāo)準差。

IJ

16繪圖數(shù)據(jù)

17期望收益

率標(biāo)準差

180%6.42%

192%5.56%

204%4.79%

216%4.17%

228%3.77%

2310%3.67%

2412%3.89%

2514%4.38%

2616%5.07%

2718%5.88%

2820%6.77%

2922%7.71%

3024%8.69%

3126%9.70%

3228%10.72%

(13)選取單元格區(qū)域118：J32,單擊工具欄上的［圖表向?qū)В莅粹o繪制［平滑散點圖］,其

他步驟同前。最后得到投資組合期望收益率與標(biāo)準差的曲線。

通過對各種資產(chǎn)的期望收益率和標(biāo)準差，以及各種資產(chǎn)之間相關(guān)系數(shù)的大小進行調(diào)節(jié),

可以觀察計算結(jié)果的變化及圖形的變化情況，從而可以很方便地分析各種不同風(fēng)險資產(chǎn)的

期望收益率、標(biāo)準差，以及相關(guān)系數(shù)下的最優(yōu)投資組合情況。上面以5種風(fēng)險資產(chǎn)的情況

為例介紹了多項風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析模型的建立過程。利用上述方法同學(xué)

們可以建立任意多種風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析模型。

3、無風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)的最優(yōu)投資組合

在上例所給的條件中，假設(shè)投資組合中除了包括U、V、W、X、Y五種風(fēng)險資產(chǎn)外，還包

括一項無風(fēng)險資產(chǎn)，其收益率為R戶6%。試確定最優(yōu)投資組合，并繪制投資組合的期望收

益率與標(biāo)準差的關(guān)系曲線。

計算步驟如下：

(1)選取單元格F3，在［插入］菜單的［名稱］子菜單中選擇［定義］命令，在出現(xiàn)的［定義名稱］

對話框中，將所選取的單元格定義為rf。

(2)相關(guān)系數(shù)、協(xié)方差矩陣以及參數(shù)A、B、C、D的計算與上例同。

(3)在單元格C28中輸入公式二(B-A*rf)/(A-C.*rf).計算最優(yōu)投資組合的期望收益率；

在單元格E28中輸入公式二SQRT(D/C./(A-C.*rf)八2+LC),計算最優(yōu)投資組合的標(biāo)準差。

(4)選取單元格區(qū)域C30：G30,輸入下面的數(shù)組公式，計算在考慮無風(fēng)險資產(chǎn)存在時風(fēng)

險資產(chǎn)的最優(yōu)投資組合：

二TRANSPOSE(MMULT(MINVERSE(C19：G23),C3：C7-r^{1;1;1;1;1}))/(A-C.*rf))。

ABCDEFG

1數(shù)據(jù)

J9期望收益

資產(chǎn)率標(biāo)準差無風(fēng)險資產(chǎn)收益率

3U15.0%7.0%6%

4V12.0%5.0%

5W13.0%10.0%

6X18.0%20.0%

7Y14.0%8.0%

8相關(guān)

系數(shù)

9資產(chǎn)UVWXY

10U10.560.080.320.5

11V0.5610.370.080.28

12W0.080.3710.350.13

13X0.320.080.351-0.1

14Y0.50.280.13-0.11

15

16中間計算參數(shù)

17協(xié)方差矩陣

18資產(chǎn)UVWXY

19U0.49%0.20%0.06%0.45%0.28%

20V0.20%0.25%0.19%0.08%0.11%

21W0.06%0.19%1.00%0.70%0.10%

22X0.45%0.08%0.70%4.00%-0.16%

23Y0.28%0.11%0.10%-0.16%0.64%

24參數(shù)A、B、C、D的計算

25A二59.72266C=472.8945

26B二7.83521D二138.4309

27最優(yōu)投資組合

28期望

收益

率13.56%標(biāo)準差4.91%

29資產(chǎn)UVWXY

30比重22.51%41.61%6.80%5.95%23.13%

(5)在單元格13：117中輸入給定的期望收益率數(shù)據(jù)。在單元格J3中輸入公式

=SQRT(C./D*(I3-A/C.-2,并將此單元格向下一直填充復(fù)制到單元格J17,計算僅有風(fēng)險資

產(chǎn)構(gòu)成的投資組合不同期望收益率所對應(yīng)的標(biāo)準差。

(6)在單元格J18中輸入0%，在單元格J19中輸入=E28，在單元格J2O中輸入20%,

在單元格KI8中輸入公式=rf+D*$E$28/(C.*$C$28-A)*J18,并將其往下一直填充復(fù)制到單元

格K2O,得到資本配置線上收益率與標(biāo)準差的三對數(shù)據(jù)。

IJK

1繪圖數(shù)據(jù)

9期望收益

率標(biāo)準差資本配置線CAL上的收益率

30%23.79%

42%20.18%

54%16.60%

66%13.09%

78%9.71%

810%6.69%

912%4.74%

1014%5.25%

n1

16%7.74%

12

118%10.94%

n3

20%M.38%

14

122%17.92%

1524%21.51%

1626%25.14%

1728%28.78%

n8

0%6.00%

19

14.91%13.56%

2020%36.80%

(7)選取單元格區(qū)域13：JI7,繪制［平滑散點圖］，步驟同前。這里要注意無風(fēng)險資產(chǎn)數(shù)

據(jù)的添加。單擊［添加］按鈕，添加系列3,然后在［x值］欄中輸入二sheetl!$J$18:$J$20,在［Y

值］欄中輸入二sheetl!$K$18:$K$2()。單擊［確定］按鈕。

如下列圖所示。

在上圖中，向右開口的雙曲線代表的是僅有風(fēng)險資產(chǎn)的投資組合，其中的上半支為風(fēng)險資

產(chǎn)的有效投資組合，即效率邊界。直線代表的是無風(fēng)險資產(chǎn)和風(fēng)險資產(chǎn)構(gòu)成的投資組合，

直線與雙曲線的切點即為無風(fēng)險資產(chǎn)和風(fēng)險資產(chǎn)的最優(yōu)投資組合。

4、無風(fēng)險資產(chǎn)與多種風(fēng)險資產(chǎn)投資組合的動態(tài)計算與分析模型

以無風(fēng)險資產(chǎn)與5種風(fēng)險資產(chǎn)構(gòu)成的投資組合為例，建立一個無風(fēng)險資產(chǎn)與多種風(fēng)險資

產(chǎn)投資組合的動態(tài)計算與分析模型。各種以合資數(shù)據(jù)的微調(diào)按鈕設(shè)計、協(xié)方差的計算、參

數(shù)A、B、C、D的計算與前面一致。所不同的是，該模型中增加了一個無風(fēng)險資產(chǎn)收益率

的微調(diào)按鈕，其單元格鏈接為$G$4,單元格G5中的計算公式為=64/1000,從而使每次調(diào)節(jié)

對應(yīng)的無風(fēng)險資產(chǎn)收益率變動0.1%.

BCDEF(

2資產(chǎn)期望收益率收益率調(diào)節(jié)標(biāo)準差標(biāo)準差調(diào)節(jié)▲

▼

■,1

3P12.20%1226.20%62

??一?_

_?_1▼

4Q11.10%11][7.30%7370

▲

▼_*_

5R11.40%L7.00%707.00%

_*_1▼

6S13.10%1316.90%69

.

▼

7T15.90%1598.60%86

8

9資產(chǎn)PQRST

10P10.120.490.540.18

.

▼

11Q-e-11210.310.140.21

.

▼

12R14913110.460.12

_*_1_*_1_*_1

1326

▲▲▲.

14T,118-121112.1261

15

16中間計算參數(shù)

17協(xié)力差矩陣

18資產(chǎn)PQ