2021年9月25日 星期六

投資A3: 運用Excel進行股票投資組合回報最優化 Portfolio Optimizer in Excel

運用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

Portfolio Optimizer in Excel Excel股票投資組合回報最優化

沒有留言:

張貼留言