運用Excel進行股票投資組合回報最優化
Portfolio Optimizer in Excel
步驟1:輸入沒風險資產回報率, 例如政府債券每月利率/定期存款每月利率
Step 1: Input risk free rate such as monthly government bond rate/fixed deposit monthly interest rate
步驟2:計算股票 Apple, GE, Netflix (只是本文例子, 不是投資建議)預期回報 =average(click all Monthly return cells)
Step 2: Calculate Apple, GE, Netflix (examples only, not investment recommendation) expected returns =average(click all Monthly return cells)
步驟3:建立 variance and covariance matrix, weights 輸入dummy weights, 相同股票variance =var.s (click all Monthly return for apple); 不同股票covariance =covariance.s(all apple Monthly return cells, all GE Monthly return cells), 其他如此類推
Step 3: set up variance and covariance matrix, input dummy weights, same stocks with variance function, different stocks with covariance function.
步驟4:計算Contribution to Variance, use sumproduct function
Step 4: Calculate Contribution to Variance , use sumproduct function
步驟5:加總計算加權總數, 組合預期回報,組合標準差,expected sharpe ratio
step5:sum of weights, portfolio expected return, portfolio SD,expected sharpe ratio
步驟6:運用excel solver 讓excel 自動計算股票投資組合各股票回報最優化權重, 極大化expected sharpe ratio,by changing variable cells 基於局限條件:每個股票權重大個等於零, 加總權重等於1
Step 6: Apply excel solver let excel automatic calculate Portfolio Optimizer with weights , maximize sharpe ratio, by changing variable cells subject to the constraints: each stock weight more or equal to zero, sum of weight equals to one.
Final step: Solve
最後步驟: 解答
附下列一個視頻有示範運用Excel進行股票投資組合回報最優化
Attached a youtube video to demostrate Portfolio Optimizer in Excel
沒有留言:
張貼留言