[PR] 当サイトはアフィリエイト広告による収益を得ています。
ポートフォリオを組んだ場合のリターン・リスクの計算方法を、「資産を組み合わせた場合のリターン計算」・「資産を組み合わせた場合のリスク計算」で、解説しました。
ただ、いろいろなポートフォリオのリターン・リスクを調べたい場合、いちいち計算するのは大変です。どうにか楽に計算できないでしょうか?
それには、投資割合を入力すれば自動でリターン・リスクを計算する、エクセルファイルを作っておけばいいです。ポートフォリオのリターン・リスクの自動計算エクセルファイルの作り方は以下です。
なお、手順内の計算は、"国内債券0%・外国債券30%・国内株式40%・外国株式30%"という投資割合で計算しています。
- 完成図(こんなファイルをつくります)
- ダウンロード
- 自分で入力する欄をつくる
- 計算に必要な数値を入力する欄をつくる
- 自動で計算される欄をつくる
- 実際の使い方例
- (追記)最大損失率・最大利益率からポートフォリオを逆算する方法
- 実際の使い方例
- ダウンロード
完成図(こんなファイルをつくります)
ダウンロード
エクセルファイルを1から作るのは大変なので、完成したエクセルファイルをダウンロードできるようにしておきます。ダウンロードしたエクセルファイルを見ながら作成方法を確認すると、よりわかりやすいと思います。
>> portfolio_ return_risk_01_02.xlsx
自分で入力する欄をつくる
まず、自分で入力する欄をつくります。
ア.投資割合
B2・C2・D2・E2のセルに、投資割合の数値を入力する欄をつくります。ここでは、B2に「0%」・C2に「30%」・D2に「40%」・E2に「30%」と入力しました。ご自分が使うときは、計算したい投資割合を入力してください。
イ.全世界株式の国内株式の割合
A10のセルに、全世界株式の中の、国内株式の割合を入力する欄をつくります。この数値は、全世界株式に投資する際に必要になります。現在は、全世界株式の国内株式の割合が6.7%なので、「6.7%」と入力しています。
なお、全世界株式の国内株式の割合は、eMAXIS Slim 全世界株式(オール・カントリー)の銘柄ページの、「最新の月報」(PDFファイル)で手に入れます。具体的な入手方法は、以下です。
a.運用会社の銘柄のホームページにアクセスする
eMAXIS Slim 全世界株式(オール・カントリー)の運用会社は、三菱USJ国際投信です。
※画像は、三菱USJ国際投信のホームページより引用
b.「最新の月報」をクリックする
国内株式の割合は月報内に書かれています(数値は毎月かわりますので、定期的にしらべる必要があります)。ちなみに、月報は、毎月12日くらいに更新されるようです。
※画像は、三菱USJ国際投信のホームページ及び、月報より引用
計算に必要な数値を入力する欄をつくる
次に、計算に必要な数値を入力する欄をつくります。
ア.相関係数
14行~18行に、相関係数を入力する欄をつくります。なお、相関係数の数値は、年金積立金管理運用独立行政法人(GPIF)が公開している資料(PDFファイル)で手に入れます。
相関係数の数値の具体的な入手方法は、以下です。
a.年金積立金管理運用独立行政法人のホームページにアクセスする
ちなみに、年金積立金管理運用独立行政法人(GPIF)とは、わたしたちの年金を運用している公的機関です。
※上は、スマホでの表示画面です。パソコンでの表示画面だと、左にサイドバーがあります。
b.基本 ポートフォリオの変更について(詳細)をクリックする
項目:「第4期中期目標期間(2020年4月1日からの5カ年)における基本ポートフォリオ」の下の方に、「基本 ポートフォリオの変更について(詳細)[PDF:863KB] 」というリンクがあるので、クリックします。
c.b.のPDF資料の10ページ目に相関係数の数値が載っています。
賃金上昇率の欄はいらないので、それ以外の数値(下の赤枠の数値)をエクセルに入力します。
イ.リターン・リスク
21行~23行に、リターン・リスクのデータを入力する欄をつくります。なお、リターン・リスクは、年金積立金管理運用独立行政法人(GPIF)が公開している資料(PDFファイル)で手に入れます。
リターン・リスクの具体的な入手方法は、以下です。なお、手順b.までは相関係数の入手方法と同じです。
a.年金積立金管理運用独立行政法人のホームページにアクセスする
※上は、スマホでの表示画面です。パソコンでの表示画面だと、左にサイドバーがあります。
b.基本 ポートフォリオの変更について(詳細)をクリックする
(改訂)c.b.のPDF資料の9ページ目で、リターンの数値を手に入れます。
なお、リターンには、「名目リターン」と、「実質的なリターン」の2種類がありますが、名目リターンの方を使います。
d.b.のPDF資料の10ページ目で、リスクの数値を手に入れます。
自動で計算される欄をつくる
最後に、自動で計算される欄をつくります。
ア.入力した割合の合計
入力した割合の合計が、自動で計算されるように式を入力します。G2には、「=SUM(B2:E2) 」と入力しています。
イ.決めたポートフォリオから計算されるリターン・リスク
a.リターン
入力した数値から、リターンが自動で計算されるように式を入力します。E5には、「=B2*B22+C2*C22+D2*D22+E2*E22」と入力しています。
b.リスク
ⅰ.入力した数値から、「分散」が自動で計算されるように式を入力します。
※ 見やすくするため、セル内で改行しています。実際には、改行する必要はありません。
D7のセルに入力した式
=B2^2*B23^2+C2^2*C23^2+D2^2*D23^2+E2^2*E23^2+2*B2*B23*C2*C23*B16+2*B2*B23*D2*D23*B17+2*B2*B23*E2*E23*B18+2*C2*C23*D2*D23*C17+2*C2*C23*E2*E23*C18+2*D2*D23*E2*E23*D18 |
ⅱ.分散を、「リスク」に変換する式を入力します。
E7に、「=SQRT(D7) 」と入力します。
ウ.決めたポートフォリオから計算される最大損失率・最大利益率
a.最大損失率
入力した数値から、最大損失率が自動で計算されるように式を入力します。B5に、「=E5-E7*2」と入力します。
b.最大利益率
入力した数値から、最大利益率が自動で計算されるように式を入力します。B7に、「=E5+E7*2」と入力します。
実際の使い方例
国内債券0%・外国債券30%・国内株式40%・外国株式30%のポートフォリオのリターン・リスクはどのくらいか?
ここでは、実際の使い方の例をあげて解説します。まず、国内債券には投資せず、外国債券に30%、国内株式に40%、外国株式に30%投資した場合の、リターン・リスクを計算してみましょう。なお、計算されたリターン・リスクから、最大でおこりうる損失・利益も同時に計算されます。
ア.まず、投資割合を入力します。
「割合」の欄の、国内債券に「0%」・外国債券に「30%」・国内株式に「40%」・外国株式に「30%」と入力します。
イ.すると、リターン:5.18%・リスク:16.66%と表示されます。
この投資割合の場合、リターンは5.18%、リスクは16.66%のようです。
ウ.また、最大損失率:-28%・最大利益率:39%と表示されます。
この投資割合の場合、最大で-28%の損失におさまる可能性がたかく、最大で+39%の利益におさまる可能性がたかいことが分かりました。
国内債券50%・全世界株式50%のポートフォリオのリターン・リスクはどのくらいか?
次に、国内債券に50%、全世界株式に50%投資した場合に、どのくらいのリターン・リスクになるのか計算してみましょう。なお、全世界株式の国内株式(=日本の株式)の割合は、現在、6.7%ですので、全世界株式50%の中の6.7%を国内株式に投資(3%≒50%×6.7%)し、のこりを外国株式に投資(47%)します。また、計算されたリターン・リスクから、最大でおこりうる損失・利益も同時に計算されます。
ア.まず、投資割合を入力します。
「割合」の欄の、国内債券に「50%」・外国債券に「0%」・国内株式に「3%」・外国株式に「47%」と入力します。
イ.すると、リターン:3.90%・リスク:12.32%と表示されます。
この投資割合の場合、リターンは3.90%・リスクは12.32%のようです。
ウ.また、最大損失率は-21%・最大利益率は29%と表示されます。
この投資割合の場合、最大で-21%の損失におさまる可能性がたかく、最大で+29%の利益におさまる可能性がたかいことが分かりました。
(追記)最大損失率・最大利益率からポートフォリオを逆算する方法
このエクセルファイルで、いろいろなポートフォリオがどのくらいのリターン・リスクになるか計算できます。
くわえて、このエクセルファイルを使えば、最大損失率・最大利益率からポートフォリオを逆算することもできます。では、どうすれば最大損失率・最大利益率からポートフォリオを逆算できるのでしょうか?
エクセルのソルバー機能を使って、最大損失率・最大利益率から、ポートフォリオを逆算します。最大損失率・最大利益率からポートフォリオを逆算する方法は、以下です。
ア.ソルバーを有効化
ソルバーはデフォルトで有効になっていないため、まず有効にします。
※ソルバーが有効になっている場合は、この手順をスキップ。
a.上の「ファイル」タブをクリック
b.左のメニューから「オプション」をクリック
c.左のメニューの「アドイン」をクリック
d.右下の「管理(A):」で「Excelアドイン」を選択し、「設定(G)」をクリック
e.左のメニューの「ソルバー アドイン」にチェックを入れ、「OK」をクリック
a.ソルバーの起動
「データ」タブをクリックし、「ソルバー」をクリックします。
b.最大損失率の設定
ⅰ.目的セルの設定:(T)
「目的セルの設定:(T)」の右の入力欄をクリックし、「B5」をクリックします。
ⅱ.目標値:の指定
「目標値:」は、「指定値:(V)」を選んで、目標額を入力します。ここでは、目標額を「-30」と設定しています。
c.変数セルの変更:(B)
まず、「変数セルの変更:(B)」の下の入力欄をクリックしてから、B2をクリックし、シフトを押しながらE2をクリックします。
d.制約条件の対象:(U)
この設定をすることで、各セルの「計算結果の数値」に条件をつけることができます(このセルは0以上に限定する、このセルは、0以下に限定するなど)。
i.「制約条件の対象:(U)」の右の「追加(A)」をクリック
ⅱ.外国債券の投資割合を「0」に限定する
外国債券は、リスクに対してリターンが少ないため投資しません。そのため、投資割合を0にします。
設定方法
・C2をクリックします。
・真ん中の記号は「=」にし、制約条件(N)は、「0」にします。
・ひきつづき設定をするため、下の真ん中の「追加(A)」をクリック。
ⅲ.国内株式の投資割合を、全世界株式の中の割合に調整する
国内株式の投資割合を、「 (1-$B$2)*$A$10」で計算した割合に限定します。
式の意味は、
「(1-$B$2)」 投資割合全体から、日本債券に投資する割合をのぞく。
「*$A$10」 残った分(=株式に投資する分)に、全世界株式の中の国内株式割合をかける。
-という感じです。
設定方法
・D2をクリックします。
・真ん中の記号は「=」にし、制約条件(N)は、「(1-$B$2)*$A$10」にします。
計算式の内容は、(全体の割合-国内債券の割合)×全世界株式の中の国内株式の割合
・ひきつづき設定をするため、下の真ん中の「追加(A)」をクリック。
ⅳ.投資割合の合計を「100%」に限定する
投資割合が100%を超えないように、100%に限定します。
設定方法
・G2をクリックします。
・真ん中の記号は「=」にし、制約条件(N)は、「100%」にします。
・すべての設定がおわったため、下の左の「OK」をクリックします。
実際の使い方例
国内債券と全世界株式とに投資する場合、最大でも損失が-30%しかでないようにするには、どのようなポートフォリオにすればいいか
ここでは、実際にソルバーを使ってポートフォリオを逆算します。まず、あまりの値下がりに恐ろしくなり、全資産を売却してしまいそうな「最大損失率」(=耐えられない最大損失率)から、ポートフォリオを逆算してみます。
条件:最大損失率-30%、国内債券と全世界株式とに投資
ア.ソルバーを起動します
「データ」クリックし、リボンの一番右にある「ソルバー」をクリックする。
イ.先ほどの「ソルバーの設定方法」の項目で、必要な設定はすでにしてあるので、下にある「解決(S)」をクリックします。
ウ.計算の結果、最大損失率を-30%におさえるには、「国内債券に27%、全世界株式に73%(内訳:国内株式 5%・外国株式 68%)」のポートフォリオにするよいとわかりました。
なお、実際に投資する際は、国内株式に5%・外国株式に68%というようにばらばらに投資する必要はありません。27%分の国内債券インデックスファンドと、73%(=5%+68%)分の全世界株式インデックスファンド(eMAXIS Slim全世界株式(オール・カントリー)など)とに投資すればいいです。
国内債券と全世界株式とに投資する場合、最大でも利益が+40%しかでないようにするには、どのようなポートフォリオにすればいいか
次に、あまりの値上がりに舞い上がり、全資産を売却してしまいそうな「最大利益率」(=耐えられない最大利益率)からポートフォリオを逆算してみます。
条件:最大利益率+40%、国内債券と全世界株式とに投資
ア.ソルバーを起動します
「データ」クリックし、リボンの一番右にある「ソルバー」をクリックする。
イ.目的セルの指定を変更します。
目的セルの設定(T)を、「$B$7」(=最大利益率の計算セル)にして、指定値(V)を「40」にします。
ウ.下にある「解決(S)」をクリックします。
エ.計算の結果、最大利益率を+40%におさえるには、「国内債券に29%、全世界株式に72%(内訳:国内株式 5%・外国株式 67%)」のポートフォリオにするとよいとわかりました。
ダウンロード
最初に設置しているファイルと同じものです。
>> portfolio_ return_risk_01_02.xlsx