ポートフォリオをExcelで作りたい!

証券アナリストの試験も終わり、久々にブログ書く時間ができました。

さて今回は、株式投資をしている人が一度は憧れる(?)、Excelで作る株式ポートフォリオの作成手順について紹介したいと思います。

実際にポートフォリオ作ってみた

今回はトヨタ自動車、ソニー、みずほ銀行への分散投資を行った際のポートフォリオをgoogleスプレッドシートにて作成しました。

以下が分散時のポートフォリオとなります。

このようにグラフにすると、個別銘柄だけではリスク7.14%が最低だったのが、分散投資を行うことで6.83%までリスクを下げることが分かりやすくなりますね。

さて作る手順ですが、まずは2009年10月〜1015年10月の5年間の月次時系列データ(マネックス証券より抽出)を元に、月次リターンとリスク、銘柄間の相関係数を算出しました。

これらと合わせ、投資配分(10%単位)を変数として、ポートフォリオの月次リターン/リスクがどのように変化するのかを表したのが上記グラフとなります。

以下の方法を覚えれば、銘柄数を増やしてもポートフォリオのリターン/リスクを計ることができるので、リンクも参照しながら是非実践して作ってみましょう。

google docs

それでは作成手順です。

 

リターンを求める

Screenshot 2015-10-05 at 21.23.29

まずリターンの出し方です。

リターンは単純平均となるので、前月比騰落率の範囲を=averageで計算します。

図ではトヨタ自動車の過去5年間平均リターンを計算しています。(1.19%)

リスクを求める

Screenshot 2015-10-05 at 21.26.40

次にリスクです。

株式投資におけるリスクとは、リターンがどれだけ大きく上振れ、下振れするか?です。

この計算については株価がランダムウォークし、正規分布に従うことを前提にするのが一般的です。

そのためリスク計算については、標準偏差という値を求めます。

標準偏差(standard deviation)(σ:シグマ)は関数=stdevで範囲を指定すれば、一発で出てきます。

※具体的には、{(個々のリターン)-(平均リターン)}^2を合算し、平方根を取ることで求められます。

図では、トヨタ自動車の標準偏差を求めています。(7.14%)

相関係数を求める

Screenshot 2015-10-05 at 21.40.30

 

次に相関係数についてです。

相関係数(correlation)(ρ:ロー)とは、ある2つのデータがどの程度似通っているかを、数値で表したものです。

全く同じ傾向であれば1.0、完全に独立した動きであれば0、全く逆の傾向があれば-1.0という値を取ります。

Excelでは関数=correlで2つのデータ範囲を指定することで、求められます。

図では、トヨタ自動車とみずほ銀行の相関係数を求めています。(0.66)

ちなみに相関係数0.66となると、完全に一致はしないが、ある程度同じような値動きをする関係になります。

共分散を求める

Screenshot 2015-10-05 at 22.59.41

ここからがポートフォリオ作成のキモになります。

まず3銘柄(A,B,C)にWA,WB,WCの比率で分散投資した際の、ポートフォリオのリスク(σP)の計算式は下記となります。

{ { \sigma }^{ 2 } }_{ P }=w^{ 2 }_{ A }{ \sigma ^{ 2 } }_{ A }+w^{ 2 }_{ B }{ \sigma ^{ 2 } }_{ B }+w^{ 2 }_{ C }{ \sigma ^{ 2 } }_{ C }+2{ w }_{ A }{ w }_{ B }{ \rho }_{ AB }{ \sigma }_{ A }{ \sigma }_{ B }+2{ w }_{ A }{ w }_{ C }{ \rho }_{ AC }{ \sigma }_{ A }{ \sigma }_{ C }+2{ w }_{ B }{ w }_{ C }{ \rho }_{ BC }{ \sigma }_{ B }{ \sigma }_{ C }

特にこの式のρσσを、共分散(covariance)と言います。

Cov({ \sigma }_{ i },{ \sigma }_{ j })={ \rho }_{ ij }{ \sigma }_{ i }{ \sigma }_{ j }

この共分散を計算するステップとして、先ほど求めた標準偏差を対角行列の形に表します。
(図のオレンジ枠部)

この対角行列と、同じく先ほど求めた相関係数を、行列関数(=MMULT)を使い、共分散の式を作成します。

行列関数のため、トヨタ自動車の位置に共分散の計算式を入力すると、他の計算結果も同時に得られます。

ポートフォリオのリターンを求める

Screenshot 2015-10-05 at 23.38.32

ここまでくると、ポートフォリオのリターン/リスク分析は出来たも同然です。

まず、投資比率に応じたポートフォリオのリターンを求めます。

ポートフォリオ1(PF1)では、トヨタ自動車株100%、ソニー0%、みずほ銀行0%に投資をするとします。

株式リターンは単純平均となるので、

(トヨタリターン)✕(投資比率)+(ソニーリターン)✕(投資比率)+(みずほリターン)(投資比率)

=1.19%✕1+0.93%✕0+0.62✕0

=1.19%

が得られます。

これも先ほどと同様に、行列関数(=MMULT)を使い、各銘柄のリターンと投資比率を掛けあわせます。

(ここでは=transposeという、行と列を入れ替える関数を使い、行列計算を行えるようにしています。)

ポートフォリオのリスクを求める

Screenshot 2015-10-05 at 23.39.02

最後にポートフォリオのリスクを求めます。

すでに共分散については求めているため、投資比率を行列関数を使い掛け合わせることで、リスクを求めていきます。

先ほどの3銘柄分散時の計算式は2乗されている点に注意し、平方根を忘れずに計算しましょう。

平方根は=sqrtという関数で計算できます。

Screenshot 2015-10-06 at 20.45.42

 

今回は10%毎にそれぞれの投資比率を変更し、PF66まで作成しました。

これらを縦軸リターン、横軸リスクでプロットしたものが、最初に紹介したポートフォリオとなります。

行列関数を使えば投資対象が100銘柄でも楽に計算できるので、是非マスターしたいですね!

2016年1月追記:銘柄レコメンドシステムについて相談受けました!

最後までご覧頂きありがとうございました

このブログを書いているのはこんな人です。良かったらプロフィールをご覧くださいね。

会社員投資ブログは【こちら】

最新情報もSNSで配信中!

2015年8月からLINE@始めました。詳細は【コチラ】

他にも

やっています。フォロー頂けると、最新情報やブログ非公開の話、オフ会情報などを受け取れます。

※ノリでお金の相談コーナーもやってます。LINE@、google+、Twitterでお気軽にご相談下さい。

ありがとうございました。

Donate with IndieSquare

この記事をみんなとシェアする・・Share on FacebookTweet about this on TwitterShare on Google+Email this to someone

 

この記事が気に入ったら
いいね!しよう

最新情報をお届けします

Twitter でkazuをフォローしよう!

Excelでポートフォリオ理論を実証!株式3銘柄の時系列データで分散投資を考える

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


*