kazuの金融ブログkazuの金融ブログMenuオフ会ビットコイン証券アナリストまとめ管理人プロフィールオススメのFX業者kazuの金融講座案内ページkazu旅Excelでポートフォリオ理論を実証!株式3銘柄の時系列データで分散投資を考えるポートフォリオをExcelで作りたい!証券アナリストの試験も終わり、久々にブログ書く時間ができました。今回は、株式投資をしている人が一度は憧れる(?)、Excelで作る株式ポートフォリオの作成手順について紹介したいと思います。実際にポートフォリオ作ってみた今回はトヨタ自動車、ソニー、みずほ銀行への分散投資を行った際のポートフォリオをgoogleスプレッドシートにて作成しました。以下が分散時のポートフォリオとなります。このようにグラフにすると、個別銘柄だけではリスク7.14%が最低だったのが、分散投資を行うことで6.83%までリスクを下げることが分かりやすくなりますね。作る手順ですが、まずは2009年10月〜1015年10月の5年間の月次時系列データ(マネックス証券より抽出)を元に、月次リターンとリスク、銘柄間の相関係数を算出しました。これらと合わせ、投資配分(10%単位)を変数として、ポートフォリオの月次リターン/リスクがどのように変化するのかを表したのが上記グラフとなります。以下の方法を覚えれば、銘柄数を増やしてもポートフォリオのリターン/リスクを計ることができるので、リンクも参照しながら是非実践して作ってみましょう。docsそれでは作成手順です。リターンを求めるまずリターンの出し方です。リターンは単純平均となるので、前月比騰落率の範囲を=averageで計算します。図ではトヨタ自動車の過去5年間平均リターンを計算しています。(1.19%)リスクを求める次にリスクです。株式投資におけるリスクとは、リターンがどれだけ大きく上振れ、下振れするか?です。この計算については株価がランダムウォークし、正規分布に従うことを前提にするのが一般的です。そのためリスク計算については、標準偏差という値を求めます。deviation)(σ:シグマ)は関数=stdevで範囲を指定すれば、一発で出てきます。※具体的には、{(個々のリターン)-(平均リターン)}^2を合算し、平方根を取ることで求められます。図では、トヨタ自動車の標準偏差を求めています。(7.14%)相関係数を求める次に相関係数についてです。相関係数(correlation)(ρ:ロー)とは、ある2つのデータがどの程度似通っているかを、数値で表したものです。同じ傾向であれば1.0、完全に独立した動きであれば0、全く逆の傾向があれば-1.0という値を取ります。Excelでは関数=correlで2つのデータ範囲を指定することで、求められます。図では、トヨタ自動車とみずほ銀行の相関係数を求めています。(0.66)ちなみに相関係数0.66となると、完全に一致はしないが、ある程度同じような値動きをする関係になります。共分散を求めるここからがポートフォリオ作成のキモになります。3銘柄(A,B,C)にWA,WB,WCの比率で分散投資した際の、ポートフォリオのリスク(σP)の計算式は下記となります。この式のρσσを、共分散(covariance)と言います。この共分散を計算するステップとして、先ほど求めた標準偏差を対角行列の形に表します。(図のオレンジ枠部)この対角行列と、同じく先ほど求めた相関係数を、行列関数(=MMULT)を使い、共分散の式を作成します。行列関数のため、トヨタ自動車の位置に共分散の計算式を入力すると、他の計算結果も同時に得られます。ポートフォリオのリターンを求めるここまでくると、ポートフォリオのリターン/リスク分析は出来たも同然です。投資比率に応じたポートフォリオのリターンを求めます。ポートフォリオ1(PF1)では、トヨタ自動車株100%、ソニー0%、みずほ銀行0%に投資をするとします。株式リターンは単純平均となるので、(トヨタリターン)✕(投資比率)+(ソニーリターン)✕(投資比率)+(みずほリターン)(投資比率)=1.19%✕1+0.93%✕0+0.62✕0=1.19%が得られます。これも先ほどと同様に、行列関数(=MMULT)を使い、各銘柄のリターンと投資比率を掛けあわせます。(ここでは=transposeという、行と列を入れ替える関数を使い、行列計算を行えるようにしています。)ポートフォリオのリスクを求める最後にポートフォリオのリスクを求めます。共分散については求めているため、投資比率を行列関数を使い掛け合わせることで、リスクを求めていきます。先ほどの3銘柄分散時の計算式は2乗されている点に注意し、平方根を忘れずに計算しましょう。平方根は=sqrtという関数で計算できます。今回は10%毎にそれぞれの投資比率を変更し、PF66まで作成しました。これらを縦軸リターン、横軸リスクでプロットしたものが、最初に紹介したポートフォリオとなります。行列関数を使えば投資対象が100銘柄でも楽に計算できるので、是非マスターしたいところですね。EL-509-M-AX最後までご覧頂きありがとうございましたこのブログを書いているのはこんな人です。良かったらプロフィールをご覧くださいね。最新情報もSNSで配信中!ブログ以外でも、LINE@twittergoogle+やっています。フォロー頂けると、最新情報やブログ非公開の話、オフ会情報などを受け取れます。KAZU金融基礎知識を身に付けたい方向けに、kazuの金融講座もやってますよ。ありがとうございました。AMPermalink初めてコメントさせていただきます。Freeシリーズは半年前に登場したばっかで5年間の前月騰落率を出そうにもデータがないんですがこの場合は、その商品がベンチマークにしているTOPIXだとかダウ平均株価とかで5年間の前月騰落率を出して、そこから共分散を出したりして、ポートフォリオのリスクを算出したほうが良いのでしょうか。AMPermalinkくりやまさんiFreeはいずれもインデックスファンドなので、おっしゃる通りベンチマークとなっている原資産で計算した方が精度いいと思います。アクティブファンドはある程度投資期間が経ったものを、ポートフォリオに組み入れるべきですけどね。PMPermalink御丁寧な回答ありがとうございます。AMPermalinkすみません。AMPermalinkMSCIをベースにしているe-maxisと、様々なベンチマークを使用しているiFreeでは微妙に投資対象が違うので、正確にやるなら代用はしない方がベターですね。(WGBI)https://www.yieldbook.com/m/indices/single.shtml?ticker=WGBIなど海外サイトから探せばありそうです。インデックス投資ですし、大まかな資産クラスが合っていれば誤差と範囲かもしれませんね。PMPermalinkベンチマークが一つ違うだけで値動きもそんな違ってくるものですかね。PMPermalink大まかな資産クラスが合っていれば誤差の範囲だと思いますよ。AMPermalinkはじめまして。機会係数曲線を調べていて、こちらを参考にさせていただきました。投資信託の配分比率を検討するため、上記計算式をExcelで使っています。私自身は、投資経験3ヶ月です。(国内株現物とネット証券のオープン投信のみ)質問です。相関係数ρに負の値が存在する場合、共分散が負になる部分があり、SQRTの引数が負になってエラーになることがあります。この場合、どうやってリスクのポートフォリオを求めればいいでしょうか。PMPermalinkヌリイカさんこんばんは。ポートフォリオのリスクを求める際の計算式が間違っていると思われます。相関係数も共分散も、負になる場合はありますが、そのままSQRTすることはありません。SQRTする対象はσ(シグマ)の二乗のため、必ず0以上となっています。MMULTの位置によっても計算式が変わるので、一度見直して見てください。PMPermalinkkazuさまレスありがとうございました。相関係数ρのとりうる値は、-1<ρ<-1ですよね。計算方法はρ×σ×σなので、ρ<0の場合は負になります。その負になる所が、たまたま投資比率が高い(90%以上)場合に、SQRTの中が負になるのです。極端な投資比率が原因なのでしょうか。いうケースは考慮しない方がよいということかなと、思っています。すみませんがアドバイスをお願いいたします。PMPermalinkヌリイカさん返信ありがとうございます。下記計算式に、銘柄A、B、Cの標準偏差、それぞれの共分散、投資比率を代入してみてください。負にならないと思います。(そもそもポートフォリオの標準偏差σを二乗しているので、必ず0以上になります。)また下記図は極端な例として、トヨタとみずほ銀行の相関係数が-1.0(完全な逆相関)だったときのポートフォリオを作ってみました。分散図で一番左側にある点が最もリスクが低く、このときの投資比率はトヨタ:ソニー:みずほ銀行=50:0:50でした。このケースでも、σの二乗は負になることなく、もちろんSQRT後もエラーになりません。参考になれば幸いです。AMPermalinkkazuさまたびたびすみません。以下のケースを見てください。ρは1回、σは2回掛けるので、ρσσが負になることがあります。すると、SQRTの引数が負になります。PMPermalinkヌリイカさん面白い問題、ありがとうございます。相関係数:ρAB=-1、ρAC=0、ρBC=-1、(分散σ^2ではなく)標準偏差:σA=0.2、σB=0.1、σC=0.2、投資比率:WA=0.2、WB=0.6、WC=0.2となります。このポートフォリオのリスクは、=0.04*0.04+0.36*0.01+0.04*0.04+2*0.2*0.6*(-1)*0.2*0.1+2*0.2*0.2*0*0.2*0.2+2*0.6*0.2*(-1)*0.1*0.2=-0.0028となり、確かにマイナスになりました。ええっ!なんで!と一瞬戸惑ったのですが、相関係数があり得ない値になっています。AとBが完全な逆相関(ρAB=-1)、BとCが完全な逆相関(ρBC=-1)の関係のとき、AとCは完全な相関(ρAC=1)関係になります。=0.04*0.04+0.36*0.01+0.04*0.04+2*0.2*0.6*(-1)*0.2*0.1+2*0.2*0.2*1*0.2*0.2+2*0.6*0.2*(-1)*0.1*0.2=0.0004となり、正の値になります。これを平方根(SQRT)した0.02が、ポートフォリオのリスクになります。PMPermalinkkazuさまコメントありがとうございました。ありえない係数だったのですね。実はこのページを見つけた初日に、自分で投信10銘柄のポートフォリオを組んでみて、配分率15通りのうち1つで、リスクが「#NUM!」となってびっくりしたため、今回の質問をさせていただきました。その時のデータは、保存していなかったので、似たようなケースができないか試行錯誤した結果見つけたのが、前回の私の数値です。10銘柄で何通りも試していたので、どこかの計算式が間違っていたのだろうと思います。今回、親切に説明してくださって、ありがとうございました。PMPermalinkkazuさん始めまして。最近、株式投資に興味があり、サイトをめぐっている内に、こちらのブログにたどり着きました。面白い記事だったので、実際に無作為に選んだ11銘柄で、Excel(Microsoft)でやってみました!投資比率を、10%と5%に分けてPF55通りまで作り、ポートフォリオのリターンとリスクまでは、ブログの様に算出することが出来ました。55通りの中で、どれか一番最適な投資かを決めたいと考えているのですが、この場合どのようにすればよろしいのでしょうか?私の場合、最後に算出したポートフォリオの(リターン-リスク)が最大になるものが一番期待値が高いと考えていましたが、これが正しいのかどうかもわかりません。余りにも、無知で申し訳ございませんが、よろしければ、教えて下さると幸いです。PS.株式投資について、おすすめの本も教えて頂けると幸いです。PMPermalinkmikaさんコメントありがとうございます。一般的には、リターン÷リスクで計算される、シャープレシオが大きいものが最適な投資になります。x軸にリスク、y軸にリターンを持っていることを考えると、シャープレシオはそのポートフォリオにおける傾きにあたります。傾きが大きいものほど、少ないリスクで高いリターンを上げられるポートフォリオとなるわけです。http://amzn.to/2ABypjC高校生でこの記事に行き着くのは、かなりポテンシャル高いと思うので、ちょっと難しめの本も紹介しました。Y:リターン)http://25-500.com/wp/wp-content/uploads/2017/11/185133.png原点(0,0)から各ポートフォリオへの直線の傾きが最大のものが、リスクに対するリターンが優れていることになります。私は、以下の書籍で「機会曲線」に興味を持って、こちらのサイトにたどり着きました。社会人向けですが、高校生の学力でも大丈夫だと思います。よかったらお読みください。http://www.kadokawa.co.jp/product/321612000501/kazuさま各URLがまずかったら削除をお願いします。PMPermalinkヌリイカさんこんばんは。補足ありがとうございます。外部リンクはSEO的に減らしたいので、画像をコピーさせてもらいました。オススメの本も、面白いですよね。ぬりいかさんありがとうございました!シャープ・レシオについて簡単に勉強しました。リターン-リスクではなく、リターン÷リスクでしたね。わかりやすく教えて頂きありがとうございます。本についても、本屋さんで読んでみます!丁寧に教えて頂き、ありがとうございました。PMPermalinkkazuさんはじめまして、勉強させていただいています。10銘柄のポートフォリオを作成したいと思い投資比率の表を投資配分(10%単位)で作成したいのですが楽に作成する方法はございますでしょうか。手で入力していると気が狂いそうです^^;よろしくお願いいたします。AMPermalinkさぼてんさんコメントありがとうございます。next文で書くと思います。PMPermalinkkazuさんアドバイスありがとうございます。証券アナリスト2017勉強会2018年6月に証券アナリスト2次、CFAを独学で受験予定です。20〜30代前半を中心とした勉強会にもし興味があれば、メンバーに参加してみませんか?(現在70名超え!)ブログ更新情報も受信できます。ThemeGrill.。