No.29 Portfolio Optimization with Excel part.2
前々回、金融ポートフォリオの最適化をExcelでやってみました。
今回は、その続きです。
先日の記事は、これでした!
その時は、2つのキーワードが登場しました。
・期待リターンの最大化
・リスクの最小化(期待収益率のばらつきを最小化)
この間は、2番目のリスクの最小化のみを対象にしていたわけですが、
上記の2つをミックスし、ポートフォリオの最適化を図るのが、
「マーコヴィッツの平均・分散モデル」になります。
平均・分散モデルを定式化すると以下の通りです。
Ⅰ.
Ⅱ.
Ⅲ.
Ⅳ.
少し補足をしておくと、Ⅰは、分散(は分散共分散行列)の最小化となり、
Ⅱは、期待収益率を一定数cとして制約を設ける条件であり、
Ⅲは、投資比率の合計は1であり
Ⅳは、各銘柄は0以上であり、つまり空売りは考慮しない、
となります。(定式化の内容は前回とほぼ同様ですね。)
ところで、前回、分散を最小化する際、Excel上では、共分散を求めていませんでした。
実は、各シナリオにおけるポートフォリオの標準偏差を最小化していました。
(つまり、定式化に厳密に沿った形式ではありませんでした。)
どちらでも、結果は同じになるので、共分散よりもExcel上でシンプルになるので、
標準偏差を利用していました。
# 数式的には、といえるからです。
同じになるか確認してみます。
前回と同じ、以下の3銘柄のポーとフォリオ最適化を考えます。
銘柄 | 冷夏 | 平年並み | 猛暑 | 期待値 |
1. Aビール社 | -10 | -3 | 6 | 2 |
2. Bエネジー社 | 6 | 3 | 1 | 5 |
3. Cガス社 | 10 | 15 | -10 | -1 |
確率 | 1/3 | 1/3 | 1/3 |
Excelでは、以下のようなシートを作成します。
1. Aビール社 | 2. Bエネジー社 | 3. Cガス社 | ||
1. Aビール社 | 112 | 84 | -74 | |
2. Bエネジー社 | 84 | 84 | -75 | |
3. Cガス社 | -74 | -75 | 67 | |
分散 | 0 | 0.0047 | 0.0053 | 0.0010 |
まず、各銘柄の共分散を求めます。Excelでは、COVARIANCE関数を利用します。
上記の各銘柄の各シナリオにおけるリターンデータから、ぞれぞれの共分散を算出します。
次に、個別に作成しておいた投資配分率のデータと共分散行列の積を算出します。
が目的関数であることに注意です。
例えば、Aビール社の分散セルは、"=Aビール社投資配分率*SUMPRODUCT(Aビール社共分散列,投資配分率)"となります。その他も同様に算出し、それらをSUM関数で合計します。
それが最小化の対象、すなわち、目的関数となります。
あとは、前回と同様、Excelのソルバー機能で最適化を実施します。
最適解は、前回と同じく、(0, 0.472, 0.528)になった筈です。
では、いよいよ、平均・分散モデルの出番です。
ほとんど、Excel上ではモデルは完成していますので、期待収益率cを制約条件に追加するだけです。
つまり、各銘柄の期待値と投資配分率の積の合計の条件を追加するわけです。(つまり、)
ここでは、と設定して、再び、ソルバーで最適解を求めてみます。
最適解は、(0, 0.472, 0.528)となった筈です。
最初に実施した最適解における期待収益率(1.83)を設定したわけですから当然の結果ですね。
もし、とすれば、(0, 0.492, 0.508)となる筈です。
ここまでは、話が簡単になるように、リターンは既知のものとしてきましたが、
実際では、そのデータが必要になります。
通常は、株式であれば、過去の株価データから以下のように算出する必要があります。
つまり、任意の銘柄の収益率は、将来の株価から現在の株価の差を現在の株価で割った値として算出します。
ここまでは、Excelのソルバー機能を使って、簡単なポートフォリオの最適化を実践してきました。
次回は、実際の株価データを利用して、且つ、Rで実践してみたいと思います。