使用效率前緣對資產配置做最佳化調整

如果還不瞭解效率前緣（Efficient Frontier）的人請先看一下Wiki（中文、英文）。

Step 1：到Yahoo! Finance下載想要分析的資料。這邊以2009年上半年為分析的區間（1月2日到6月30日，共124天），VTI、VGK、VPL、VWO為投資的四檔ETFs。下載以後整理成Excel表格如這樣的格式：

Date	VTI	VPL	VGK	VWO
2009/6/30	46.27	46.24	40.28	32.15
2009/6/29	46.58	46.66	40.71	32.35
2009/6/26	46.14	46.75	40.18	31.79
2009/6/25	46.23	46.55	40.29	30.9
2009/6/24	45.18	45.44	39.64	31.05

Step 2：計算每一天的報酬率，例如2009/6/30當天的VTI報酬率就是2009/6/30當天收盤價46.27減掉2009/6/29的收盤價46.58再除以2009/6/29的收盤價46.58，也就是(46.27-46.58)/46.58=-0.6655%，公式為：=(B2-B3)/B3。同樣，整理成這樣的格式：

VTI	VPL	VGK	VWO
-0.6655%	-0.9001%	-1.0563%	-0.6182%
0.9536%	-0.1925%	1.3191%	1.7616%
-0.1947%	0.4296%	-0.2730%	2.8803%
2.3240%	2.4428%	1.6398%	-0.4831%
0.2886%	1.1801%	0.3036%	2.8486%

註：Step 2表格會比Step 1少一列。

Step 3：
計算平均報酬率，公式為：=AVERAGE(G2:G124)。
計算標準差，公式為：=STDEV(G2:G124)
計算變異數，公式為：=VAR(G2:G124)

整理成這樣的格式：

2009上半年	VTI	VPL	VGK	VWO
平均報酬率	0.0274%	0.0595%	0.0599%	0.2742%
標準差	2.1637%	2.1304%	2.6699%	2.7048%
變異數	0.00046817	0.000454	0.000713	0.000732

Step 4：將Step 3的資料整理成新的表格，同時加入各ETF在投資組合中的百分比，整理成表格：

	配置比例	報酬率	標準差	變異數
VTI	25.00%	0.0547%	2.1619%	0.000467
VPL	25.00%	0.0707%	2.1150%	0.000447
VGK	25.00%	0.0901%	2.6589%	0.000707
VWO	25.00%	0.2995%	2.6939%	0.000726

註：配置比例先隨便填就可以了，這部份稍後由電腦計算。

Step 5：計算共變數矩陣，公式為：=COVAR(G2:G124,G2:G124)，整理成這樣的表格：

共變數矩陣	VTI	VPL	VGK	VWO
VTI	0.00046436	0.000414	0.00053	0.000238
VPL	0.0004141	0.00045	0.000514	0.000222
VGK	0.00052952	0.000514	0.000707	0.000306
VWO	0.00023779	0.000222	0.000306	0.000726

Step 6：計算加權後的共變數矩陣：

加權後矩陣	VTI	VPL	VGK	VWO
VTI	=M13*M7*M7	=N13*M7*M8	=O13*M7*M9	=P13*M7*M10
VPL
VGK
VWO

註：=M13*M7*M7的意思是共變數矩陣同一格的位置乘上該格所對應的兩ETF配置比例，這邊兩ETF都是VTI，所以是兩個M7。

Step 7：計算整個投資組合：

組合報酬率	計算方式為各ETF的配置比例*報酬率相加，公式為=M7*N7+M8*N8+M9*N9+M10*N10
組合標準差	計算方式為組合變異數的開根號，公式：=M26^0.5
組合變異數	計算方式為加權後矩陣的總和，公式為：=SUM(M19:P22)

Step 8：列出可能的報酬率，並整理成表格：

組合報酬率	組合標準差	VTI	VPL	VGK	VWO
0.06%
0.09%
0.12%
0.15%
0.18%
0.21%
0.24%
0.27%

註：四個ETF的平均報酬率最低為0.0274%，最高為0.2742%，因此以0.03%到0.27%為可能的報酬率範圍，每0.03%為一級。但稍後會得知0.03%無法得解，所以從0.06%開始。

此時Excel表格長相如下：

Step 9：到工具-->增益集，將規劃求解功能打勾後，按確定，再看一次工具，應該會出現規劃求解的選項（如下圖），沒有這個功能請找出Office的光碟安裝。

Step 10：設定規劃求解的參數。目標儲存格設定在組合標準差那一格，設定為「最小值」以求得該報酬率以下最小的變異數。變數儲存格設定為任意三個ETF的配置比例那一格，第四個ETF的配置比例則用1減去另外三個ETF的配置比例（=1-M7-M8-M9）。限制式這邊設定為每一個ETF都至少為0，也可以自行決定每一個ETF至少要佔10%或20%的配置。而最後一個限制式則是組合報酬率是多少，可以逐一等於0.03%開始一直到0.27%的數值或是直接和最後一個表格做連結。設定好之後按求解。

上圖為成功求得解的畫面，這時候組合標準差為該投資報酬率的各種可能組合下最小的值，請把該值填入最後一個表格，並把計算出來的各ETF配置比例也複製貼上（可用選擇性貼上，轉置）。

Step 11：逐一從最低的組合報酬率計算到最高的組合報酬率，將表格填滿。求解的時候只要把限制式那邊的組合報酬率變更再重新求解即可。最後把表格完成如下：

組合報酬率	組合標準差	VTI	VPL	VGK	VWO
0.06%	2.0988%	66.74%	33.26%	0.00%	0.00%
0.09%	1.9938%	44.58%	43.86%	0.00%	11.57%
0.12%	1.9408%	30.71%	45.58%	0.00%	23.71%
0.15%	1.9423%	16.84%	47.31%	0.00%	35.85%
0.18%	1.9980%	2.96%	49.04%	0.00%	48.00%
0.21%	2.1066%	0.00%	39.10%	0.00%	60.90%
0.24%	2.2658%	0.00%	25.99%	0.00%	74.01%
0.27%	2.4660%	0.00%	12.87%	0.00%	87.13%

Step 12：將組合報酬率和組合標準差資料做圖。選擇圖表精靈的XY散佈圖，帶有平滑線的XY散佈圖，便可以畫出效率前緣的圖：

小結：從完成的表格可以得知，在組合報酬率0.12%的時候，有1.9408%的最低組合標準差（風險），這時候的投資組合為：VTI-30.71%，VPL-45.58%，VGK不配置，VWO-23.71%。不過從效率前緣的圖可以看出在報酬率0.12%和0.15%中間還有一個風險更低的點，這時候可以再去細求0.13%、0.14%的解，不過因為相差不太多，這邊我就不做了。做好效率前緣的圖之後，也可以把原本的四個ETF點上去：

Market Portfolio

Risk-Free翻譯為「無風險利率」，可以用現在的利率，不過現在美國的利率0.25%（資料來源）超過這四個ETF的平均的利率，求不出最低風險的最大值，所以這邊用VTI代表美股市場的報酬率當作無風險利率（這樣做是有問題的，但為了讓舉例可以找到Market Portfolio，所以只好將就）。把原本的表格增加兩欄：變異數、最低風險。變異數其實就是標準差的平方（公式：=M59^2），最低風險是（報酬率-無風險利率）/報酬率（公式：=(L59-0.0274%)/R59）。這樣就可以得到下表：其中最低風險的最大值就是Market Portfolio那一個點。

報酬率	標準差	VTI	VPL	VGK	VWO	變異數	最低風險
0.06%	2.0988%	66.74%	33.26%	0.00%	0.00%	0.0004405	0.740075
0.09%	1.9938%	44.58%	43.86%	0.00%	11.57%	0.00039752	1.574748
0.12%	1.9408%	30.71%	45.58%	0.00%	23.71%	0.00037667	2.458382
0.15%	1.9423%	16.84%	47.31%	0.00%	35.85%	0.00037725	3.249809
0.18%	1.9980%	2.96%	49.04%	0.00%	48.00%	0.0003992	3.822641
0.21%	2.1066%	0.00%	39.10%	0.00%	60.90%	0.00044378	4.114685
0.24%	2.2658%	0.00%	25.99%	0.00%	74.01%	0.00051338	4.141142
0.27%	2.4660%	0.00%	12.87%	0.00%	87.13%	0.00060812	3.989373

用這樣的資料繪圖：

	標準差	報酬
Risk Free	0.0000%	0.0274%
Market Portfolio	2.2658%	0.2400%

Excel檔案下載：(請連結到Scribe網頁按Download MS的Excel檔案，直接看格式會亂掉)

EF