話題のビットコイン積立をはじめよう!

【3資産(銘柄)以上のポートフォリオ】エクセルでリターン・リスク計算

ポートフォリオのリスク・リターン計算

大学生の時に学んだ「現代ポートフォリオ理論」は2資産(銘柄)までの計算だったので、比較的簡単でした。

しかし私のポートフォリオは、以下の通り10資産(銘柄)を超えています。

ポートフォリオ

資産クラス(日本株式や先進国株式という単位)であれば、「myINDEX」を使うことで効率的フロンティアを導けます。

しかし、ETFや個別株となってくると計算できるサイトが少ないです。

 

なので今回は、ETFや個別株で構成している私のポートフォリオにおけるリターン・リスク計算を行ってみました。

最終的なアウトプットイメージは以下の通りです。

実際に作ったリスク・リターン表

 

以下の人にオススメの記事
  • ETF・個別株3銘柄以上のポートフォリオで運用している
  • 統計学の知識はないけど、自分で計算してみたい

実際に利用したエクセル表や関数も紹介していますので、エクセルが触れる人なら何とかなるはず。

※今回、色々なサイトを参考にしてみましたが、サイトによって算出手順が人によって異なりました。
その中でも、もっとも参考になったのはKazuさんでした。

3資産以上ポートフォリオのリターン・リスクの計算手順

まずはじめに、ポートフォリオのリターン・リスクを計算する流れを示します。

手順
  1. 各銘柄のリスク・リターンを取得する
  2. 銘柄間の相関係数表を作成する
  3. 標準偏差表を作成する
  4. 共分散表を作成する
  5. 投資比率表に入力する
  6. リターン・リスク図などを作成する

手順①:各銘柄のリターン・リスクを取得する

リターン・リスク・相関係数表

リターン・リスク・相関係数表

まず初めに各銘柄のリターン・リスクとして、「Portfolio Visualizer」からインフレ率調整後のCAGRとstdevを取得しています。

 

なお右肩上がりの期間を指定すると、リターンが高く算出されてしまうので、「いつのデータ」を指定するか重要!

というわけで私は、2007年1月からのデータを利用することにしました。

2007年からのデータを採用した理由
  • リターンが高く算出されすぎないように、リターン算出期間に拘りたい
  • リーマンショック(2008年発生)の前年開始とする。その後、チャイナショック、コロナショックが起きている。
  • 2007年1月に存在しない銘柄は、開始日からのデータを利用する。
  • チャイナショック、コロナショックが発生している。

手順②:銘柄間の相関係数表を作成する

銘柄間の相関係数を算出するために、各銘柄の市場価格を知る必要があります。

今回は「Yahoo Finance」から過去データをダウンロードしました。

 

銘柄ごとの株価推移表

銘柄ごとの株価推移表

Yahoo Financeのヒストリカルデータをダウンロードし、各月の終値を入力しました。

2株式分割を考慮した価格を使う

過去に株式分割していたMO(アルトリア・グループ)は、株式分割を考慮した価格では無かったです。

なので株式分割調整後のデータを「Yahoo Japanファイナンス」のデータを反映しました。

 

各銘柄のリスク・リターン、相関係数表

相関係数表

株価推移表のデータをインプットにして、相関係数を算出しました。
※文字を白塗りしている箇所にも値が入力しています。

相関係数を算出する関数

=correl(銘柄Aの株価推移,銘柄Bの株価推移)

手順③:標準偏差表を作成する

標準偏差表

標準偏差表

「手順④:共分散表を作成する」の準備として、各銘柄の標準偏差(=リスク)を上画像のように入力します。
※文字を白塗りしている箇所は空白ではなく「0%」としています。

各銘柄の標準偏差は「手順①:各銘柄のリターン・リスクを取得する」を参照。

手順④:共分散表を作成する

共分散表

共分散表

共分散表の一番左上のセルで、以下の関数を入力します。そうすると共分散表は全て値が自動入力されます。

分散を算出する関数

=mmult(mmult(標準偏差表,相関係数表),標準偏差表)

手順⑤:投資比率表に入力する

投資比率表

投資比率表

沢山のポートフォリオシナリオを作成します。私は70個ほど作成しました!

それぞれのポートフォリオのリスク・リターン・シャープレシオの計算式は以下の通りです。

リターンを算出する関数

=MMULT(TRANSPOSE(リターン・リスク・相関係数表のリターン部分),投資比率表の算出対象ポートフォリオ比率部分)

リスクを算出する関数

=sqrt(MMULT(MMULT(TRANSPOSE(投資比率表の算出対象ポートフォリオ比率部分),共分散表),投資比率表の算出対象ポートフォリオ比率部分))

シャープレシオを算出する関数

=上記で算出したリターン/上記で算出したリスク

※無リスク資産のリターンを”0″としています。

上記の他にも、配当利回り表を作成して「ポートフォリオの配当利回り」を算出しました。

手順⑥:リスク・リターン図などを作成する

ポートフォリオシナリオの比率グラフ

ポートフォリオシナリオの比率グラフ

私が作成したポートフォリオシナリオ(現在のもの+70個)をグラフ化してみたり。

 

リスク・リターン図

リスク・リターン図

リスクとリターンの分布図を作ってみたり。現在のポートフォリオは赤色の星()部分です。

 

配当利回りとシャープレシオ図

配当利回りとシャープレシオ図

配当利回りとシャープレシオの分布図を作ってみました。

 

ぶるたろう

自由にカスタマイズできるのが、Excelで作るメリット!

もっと拡充していくか!!

 

以上です。関連記事も合わせてどうぞ。

▼シーゲル教授の教えに倣いつつ、米国株ポートフォリオを構築しています。バイアンドホールド・配当再投資戦略で頑張ります。

株主優待・配当の実績まとめ【最新】優待・配当金成績をブログ公開!高配当米国株とETFに分散投資中