投資A2: 股票投資組合設計 Investment Portfolio Design

上回說到如果儲了一筆初次投資資金約美元 USD$13,000。

In the last essay, we assume a person save USD$13,000 and begins his initial investment.

今天要介紹一個投資模型, 它英文名叫"Markowitz Portfolio Optimization Model"

Today I will introduce this "Markowitz Portfolio Optimization Model" to readers.

Markowitz Portfolio Optimization Model 提供了一個用數量化工具的投資模型, 它主要基於投資組合中不同股票的資產平均值回報, 投資組合中不同股票的資產的方差;透過數量法方法讓投資者去分配他的財務資源去設計出一個投資組合平衡風險及回報 。

Markowitz Portfolio Optimization Model provides a method to analyse how good a given portfolio is. It is based only on the means and the variance of the returns of the assets contained in the portfolio. It is a quantitative tool that allows an investor to allocate his resources by considering trade-off between risk and return.

步驟1:由財經網站下載經A1:初步篩選後的股票, 例如 APPLE, GE and NETFLIX (只是本文例子, 不是投資建議)過去5年每月底收市股價

Step 1: Download the data of shares like APPLE, GE and NETFLIX (examples only, not investment recommendation) from financial website with past 5 years monthly closing stock prices after A1 initial screening.

步驟2:計算每個股票每月回報(monthly return):即本月比上一個月股價增長-- [本月收市價減上月收市價]/上月收市價

Step 2: Calculate each stock monthly return by:-- (This month closing share price - previous month closing share price)/previous month closing share price

步驟3: 之後用excel function 計算每個股票平均回報[=AVERAGE], 方差[=VAR] 及標準差[=STDEV]

Step 3 ; Next, using excel function to calculate each stock average return[=AVERAGE], variance[=VAR] and standard[=STDEV] deviation.

步驟4: 之後在excel worksheet 每股票每月計算超額回報(Excess return) = Monthly return /stock average return

Step 4: Then, use excel worksheet to calculate monthly excess return of each stocks.

步驟5:計算股票APPLE, GE and NETFLIX 的Covariance [=COVAR(??:??,??:??)]

Step 5: Calculate stocks APPLE, GE and NETFLIX Covariance [=COVAR(??:??,??:??)]

步驟6:計算股票APPLE, GE and NETFLIX 的相關系數

Step 6: Calculate APPLE, GE and NETFLIX correlation

由於這分析需excel 輔助及概念比較深, 下一篇投資A3會把餘下步驟寫出及輔以excel solver 計算實例完成這個模型的介紹。

Due to this analysis need the assistance of excel and the concept is complicate, next essay Investment A3 will write out the rest of the steps with excel solver calculation to finish this model introduction.