ETFやインデックスファンドのリスクや相関係数をExcelで算出して『ぼくのかんがえたさいきょうのぽーとふぉりお』を組んでみる。part2

皆様いつもお世話になっています、ポンチ熊(@ponchiguma1557)です。

先日の記事の続きですが、こちらも以前に書いたことのある内容です。
投資信託のデータベースから基準価額を引っ張ってExcelに貼り付け、そいつをいじってETFやインデックスファンドのデータを調べてみようというお話の続き。

せっかく個別ファンドの相関係数と標準偏差を調べてみたので、それらのファンドを組み合わせたポートフォリオ全体の特性についてもExcel方眼紙で計算してみました。
先に言い訳をしておきますが、滅多に仕事ではExcelを使わないような生活をしていますので、変なところがあっても御勘弁。



今回は逆相関関係にある資産クラスを加えてより実戦的にしてみました。
伝統的資産である日本株式・先進国株式・新興国株式・日本債券・先進国債券・新興国債券・ゴールドの7種のアセットで遊んでみます。

集計期間を5年間取るため、つみたてNISA以降の新規設定ファンドは利用できず、下記のファンドのデータを使用しました。
日本債券・先進国債券は昨今の低コストインデックスファンドに合わせるべく、DC専用ファンドからデータを拝借しています。

 ★日本株式:【1306】TOPIX連動型上場投資信託
 ★先進国株式:インデックスコレクション(外国株式)
 ★新興国株式:SMT 新興国株式インデックス・オープン
 ★日本債券:インデックスコレクション(日本債券)
 ★先進国債券:インデックスF海外債券H有(DC専用)
 ★新興国債券:【1566】上場インデックスファンド新興国債券
 ★金:iシェアーズ ゴールドインデックス
※ポートフォリオの安定に寄与する先進国債券は為替ヘッジ付きのファンドを選択しています。



前回に倣って相関係数と標準偏差を算出し、まずは標準偏差を除いた表を作ってスタートです。
別のシートにリンクを張り付けてこんな表を作りました。B2:H8に相関係数をコピペしています。

基本の計算式を見てみる

まず避けて通れないこの公式を引用。
AとBの相関係数=AとBの共分散÷(Aの標準偏差×Bの標準偏差)
標準偏差=分散(ばらつきの程度)の平方根(ルート) 
 Excelで計算した相関係数も、元は共分散から計算されています。共分散自体は相関係数同様にCOVARIANCE.Pという関数を使っても求められます。

3資産を組み合わせた場合の分散の計算

資産A・B・Cからなる3つのポートフォリオの分散は
(ア)Aの投資比率の2乗×Aの標準偏差の2乗+Bの投資比率の2乗×Bの標準偏差の2乗+Cの投資比率の2乗×Cの標準偏差の2乗
(イ)2×Aの投資比率×Aの標準偏差×Bの投資比率×Bの標準偏差×AとBの相関係数
(ウ)2×Aの投資比率×Aの標準偏差×Cの投資比率×Cの標準偏差×AとCの相関係数
(エ)2×Bの投資比率×Bの標準偏差×Cの投資比率×Cの標準偏差×BとCの相関係数
上の合計になります。資産が4つになれば組み合わせのパターンが増えていきます。
複数の資産のそれぞれについて、個別の投資割合・標準偏差・相関係数を掛け合わせ、全部合計して計算することになります。

7資産を組み合わせた場合の分散の計算


数式や文章で書くと正直よく分かりませんが、この7×7=49マス全てにおいて、横の項目(行)と縦の項目(列)の投資割合・標準偏差・相関係数を掛け合わせて全部合計するわけです。
まずは資産配分の割合は置いておいて、標準偏差・相関係数の掛け合わせからやることにします。



数字をベタ打ちしてもいいのですが、先に計算した標準偏差を上の図のように並べてみます。
今回はB12:H18に標準偏差を並べました。空白のセルには0を入れておいてください。
でもって、MMULT関数を使って上の2つの表同士を掛け合わせます。新しい表の左上のセルに関数を下記のように記入。

=MMULT(MMULT(B12:H18,B2:H8),B12:H18)

と入力すればExcelが相関係数を分散・共分散の表に変換してくれます(B2:H8に相関係数のデータが、B12:H18に標準偏差のデータが存在します)。

普通にEnterを押すと#VALUE!というエラーが出るので、Shift+Ctrlを押しながらEnterを押してください。



何でこの計算が成立するのかは、行列,演算,対角行列,くらいのワードで検索して、数学IIICの教科書を引っ張り出してみてください。

ちなみに式をコピペした後に変なところを触ると『配列の一部を変更することはできません』と表示されてExcelが固まってしまう現象が見られます。。。

投資比率の掛け合わせ

これで標準偏差と相関係数の掛け合わせが終わり、分散・共分散の表に変化しました。
同じ要領で投資比率を掛け合わせて全体の分散を求めます。



B32:H32までに投資比率を入力しました。同じ要領でそれぞれ掛け合わせます。期待リターンは自分の思う数値を入れてあります。
過去データから引っ張ってもいいのですが、それを書くとまた『投資に絶対は無い』とか言って説教を喰らうので雰囲気で適当な数字を入れてあります。

=MMULT(MMULT(B32:H32,B22:H28),TRANSPOSE(B32:H32))

と入力すればExcelが分散の合計を計算してくれます(B32:H32に資産ごとの配分比率のデータが、B22:H28に分散・共分散のデータが存在します)。
普通にEnterを押すと#VALUE!というエラーが出るので、Shift+Ctrlを押しながらEnterを押してください。

TRANSPOSEは行と列を入れ替えて計算する関数です。
元の分散・共分散の表に、縦と横から資産配分の割合を掛け合わせるイメージですね。



標準偏差はB36のルートを取ると求められます。一応書いておくと=B36^0.5になります。
ポートフォリオ全体の期待リターンはそれぞれの資産クラスの期待リターンの加重平均になります。



=MMULT(B33:H33,TRANSPOSE(B32:H32))

ベタ打ちしてもいいのですが、上と同じ要領でこう書いても計算できます(B38に作成)
シャープ・レシオは期待リターンを標準偏差で割って求めます(円の金利はゼロとしています)。一応書くと=B38/B37になります。
B32:H32の投資配分をいじると、それに連動してポートフォリオ全体の分散・標準偏差・期待リターン・シャープレシオも動きます。

上の例で見ると、先進国株式と組み合わせる場合、標準偏差(リスク)の小さい日本債券よりも、標準偏差の大きい為替ヘッジ外債と組み合わせる方が全体としての標準偏差が小さくなるなど、面白いことが見えてきます。
合計が100%に満たなくてもいいですし、100%を超えても計算できます。イメージは掴みにくいですが。

株価の月次データを入手すれば、個別銘柄同士の相性を調べたりするのにも活用できます。
あくまで過去データからの計算なんで、先のことを保障するわけではありませんが、参考にはなると思います。

疲れたので今日はこの辺りで終了。次回はソルバーを使ってシャープレシオを追求するお話です。

ではまた。明日も色んな価値をBuy & Drip...

応援よろしくお願いします♪
にほんブログ村 株ブログ 米国株へにほんブログ村 株ブログへ ブログランキング・にほんブログ村へ

0 件のコメント :

コメントを投稿