2009年8月2日

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

如果還不瞭解效率前緣(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檔案,直接看格式會亂掉)

7 則留言:

  1. 後來才發現Excel裡面有一些錯誤,現在已經更正了,所以如果之前有下載的現在請下載新的版本試試看。至於這篇文章和效率前緣使用的注意事項、投資全球的策略(六)資產配置的驗證一共三篇文章用的圖我就不再更改了,反正也不影響結論,改圖比較累,請大家見諒。

    回覆刪除
  2. 版主你好:
    很欣賞你做的這篇文章。若你方便,是否可以直接跟你要求這個Excel的檔案呢,因為進入scribe後,整個電腦就好像當掉了一樣。
    感謝!!

    回覆刪除
  3. 請用這個網址下載:
    http://dl.dropbox.com/u/4188452/Blogfile/EF.xls

    回覆刪除
  4. 版主您好,Step 2:計算每一天的報酬率的公式是否應該為當日該股的漲跌幅?因報酬率指的是ㄧ段期間的報酬率?謝謝

    回覆刪除
  5. 請問效率前緣的資料裡面,如何去校正股利的報酬呢? 這部份若不考慮, 是否會影響很大?

    回覆刪除
  6. 短期的回測影響不大,要考慮的話也可以把股利加進去,只是前置資料處理比較花功夫而已。

    回覆刪除
  7. 1. 內文有誤:最低風險是(報酬率-無風險利率)/報酬率;分母應該改成標準差;而EXCEL檔內的分母是變異數,此應也有誤?
    2. 無風險利率,是否應該要用『日化』的定存利率?假設以台灣目前最低3年定存利率1.5%,則算出來為(1.015^(1/365)-1)=0.004%;因為做圖的縱座標亦為每日報酬率,而股價上漲也等同複利計算。

    回覆刪除