如何用Excel計算投資組合的有效前沿？

假設一個投資組合由美債和美股構成，美債為跟蹤美國投資級債券市場走勢的交易所交易基金AGG，美股為跟蹤美國標準普爾500指數走勢的交易所交易基金SPY。

注：在計算有效前沿曲線時投資回報率一般應采用預期回報率，但本文只是為了演示投資組合中資產配置比率的不同給風險和回報所帶來相應的變化，因此計算采用的是實際回報率

第一步在雅虎財經網站上下載AGG和SPY過去一年即2018年12月1日至2019年11月30日的每日收盤價，以數據表中的Adj Close經調整后的收盤價為準，因交易所交易基金會定期分紅派息，而經調整后的收盤價對分紅金額進行了除權，能更準確地代表基金的實際走勢。數據整理如下：

第二步 計算AGG和SPY的每日回報率，回報率計算公式為LN（最新的收盤價/前一個交易日的收盤價），也可以用比較傳統的方法，即（最新的收盤價/前一個交易日的收盤價）-1，在計算每天的回報率時這兩種方法的結果差異很小

第三步參數（年化回報率、每日回報率的標準差和資產回報之間相關系數）的計算

全部計算結果如下：

第四步 計算各種類型組合相應的年化回報率和每日回報率的標準差

先準備以下空表：

由于已知投資組合中AGG和SPY的各項風險收益參數即年化回報率、每日回報率的標準差和資產回報之間相關系數，下面可以結合風險偏好也就是組合中各資產所占權重的變化，計算各組合類型的年化回報率、每日回報率的標準差。

在這里要注意一點，整個投資組合的回報率=單項資產的回報率*該資產在組合中的市值占比，但風險值并不等于組合中單項資產的風險值簡單相加，而是要考慮各項資產的回報率之間的相關系數，具體的計算公式如下：

根據以上公式，計算不同的股債配置比率所對應的投資組合年化回報率和每日回報率的標準差，以組合類型1即債券資產占比為100%，美股占比為零的情況為例，組合1的回報率=美債ABB的年化回報率*ABB在組合中的占比+美股SPY的年化回報率*SPY在組合中的占比=10.66%*100%+16.29%*0%=10.66%

組合1的風險值=ABB年化回報率的平方*ABB在組合中占比的平方+ SPY年化回報率的平方*SPY在組合中占比的平方+2*ABB年化回報率的平方*ABB和SPY年化回報率之間的相關系數*ABB年化回報率*ABB在組合中的占比*SPY年化回報率*SPY在組合中的占比= (3.17%^2*100%^2+14.59%^2*0%^2+2*(-0.2980)* 3.17%*100%*14.59%*0%)的開方=3.17%

各類型組合的回報率和風險值計算結果匯總如下：

第五步 繪制有效前沿曲線

用光標選中組合的回報率和風險值計算結果所在單元格區域，選擇散點圖功能中的“帶平滑線和數據標記的散點圖”生成圖表

第六步 對散點圖略加修飾，即可生成有效前沿曲線

下圖顯示的情況與投資組合優化管理理論相符，風險承受能力強的激進投資者可全倉美股，獲得最高的收益，即圖中最右側的情況；最保守的投資者全倉美國投資級債券，回報率最低，即圖中最左側的情況。值得注意的是，在債券和股票配置比率分別為9成/1成的情況下，投資組合的風險值卻低于全倉債券的投資組合，收益率卻更高一些。

與一般人想象不同的地方在于，股票給人的印象是其波動風險通常要遠遠大于債券，但在債券投資組合中配置了一定比率的股票卻不一定會導致整個投資組合的收益波動風險出現上升，反而有可能降低整個組合的收益波動率，原因在于股票和債券的回報率之間呈現負相關關系，可以平衡各自回報率的波動，從而在降低風險的同時提升整個組合的回報表現。

一定存在這么一個平衡點，即在債券投資組合中配置了何種比率的股票后，整個組合的回報率高于全倉債券的組合，但這兩個組合的風險值卻一致？

第六步 用規劃求解功能算出風險值相等的情況下回報率最大的資產配置比率

100%配置ABB的組合的風險值為3.17%，下面計算組合風險值為3.17%的股債組合中股票的配置比率。

可以看到，股債占比分別為80%和20%的組合的風險值即每日回報率的標準差為3.24%，與100%配置美債的組合的風險值3.17%相差不遠，說明對該組合中美債投資比率略作調整即可算出風險值為3.17%的情況下股債各自的配置比率。

調出excel的規劃求解功能界面，將3.24%的風險值所在的單元格J9作為 “目標單元格”，值“等于”3.17%，“可變單元格”選為AGG配置比率所在的單元格G9，然后點擊“求解”鍵

規劃求解的結果顯示，當美債ABB的配置比率調整為80.95%、美股SPY的占比相應地變為19.05%的情況下，整個組合的風險值與全債組合的風險值均為3.17%，但收益率為11.73%，跑贏全債組合的10.66%。

多元化投資的做法可在一定程度上改善整個投資組合的回報率，同時不需要增加或明顯增加整個投資組合的風險值。但這個改善程度是有限的，需要根據組合中配置的資產類型、各資產的回報率之間的相關程度以及配置的比率進行具體分析。

多元化投資組合的構建原則并不是在投資組合中簡單地將不同類型的資產“堆砌在一起”，而是要選擇回報率的相關程度很低或者負相關的資產，這樣才能真正達到分散投資組合回報波動風險的目的。