ポートフォリオのリターン・リスク自動計算エクセルファイルの作り方

投資信託ガイドTop > 投資に役立つエクセルファイル > ポートフォリオのリターン・リスク自動計算エクセルファイルの作り方

« 資産を組み合わせた場合のリスク計算 | メイン | 企業型確定拠出年金は、会社が従業員のために準備する―退職金 »

ポートフォリオのリターン・リスク自動計算エクセルファイルの作り方

 ポートフォリオを組んだ場合のリターン・リスクの計算方法を、「資産を組み合わせた場合のリターン計算」・「資産を組み合わせた場合のリスク計算」で、解説しました。

 ただ、いろいろなポートフォリオのリターン・リスクを調べたい場合、いちいち計算するのは大変です。どうにか楽に計算できないでしょうか?

 それには、投資割合を入力すれば自動でリターン・リスクを計算する、エクセルファイルを作っておけばいいです。ポートフォリオのリターン・リスクの自動計算エクセルファイルの作り方は以下です。

 なお、手順内の計算は、"国内債券0%・外国債券30%・国内株式40%・外国株式30%"という投資割合で計算しています。


完成図(こんなファイルをつくります)

完成図


ダウンロード

 エクセルファイルを1から作るのは大変なので、とちゅうまで作成済のエクセルファイルをダウンロードできるようにしておきます。ダウンロードしたエクセルファイルを見ながら作成方法を確認すると、よりわかりやすいと思います。
>> portfolio_ return_risk_01_01.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%」と入力します。

割合の欄に入力

イ.すると、リターン:2.88%・リスク:16.66%と表示されます。
この投資割合の場合、リターンは2.88%、リスクは16.66%のようです。

リターンとリスクとの計算結果

ウ.また、最大損失率:-30%・最大利益率:36%と表示されます。
この投資割合の場合、最大で-30%の損失におさまる可能性がたかく、最大で+36%の利益におさまる可能性がたかいことが分かりました。

最大損失率と最大利益率との計算結果

国内債券50%・全世界株式50%のポートフォリオのリターン・リスクはどのくらいか?

 次に、国内債券に50%、全世界株式に50%投資した場合に、どのくらいのリターン・リスクになるのか計算してみましょう。なお、全世界株式の国内株式(=日本の株式)の割合は、現在、6.7%ですので、全世界株式50%の中の6.7%を国内株式に投資(3%≒50%×6.7%)し、のこりを外国株式に投資(47%)します。また、計算されたリターン・リスクから、最大でおこりうる損失・利益も同時に計算されます。

ア.まず、投資割合を入力します。
「割合」の欄の、国内債券に「50%」・外国債券に「0%」・国内株式に「3%」・外国株式に「47%」と入力します。

割合の欄に入力

イ.すると、リターン:1.60%・リスク:12.32%と表示されます。
この投資割合の場合、リターンは1.60%・リスクは12.32%のようです。

リターンとリスクとの計算結果

ウ.また、最大損失率は-23%・最大利益率は26%と表示されます。
この投資割合の場合、最大で-23%の損失におさまる可能性がたかく、最大で+26%の利益におさまる可能性がたかいことが分かりました。

最大損失率と最大利益率との計算結果


(追記)最大損失率・最大利益率からポートフォリオを逆算する方法

 このエクセルファイルで、いろいろなポートフォリオがどのくらいのリターン・リスクになるか計算できます。

 くわえて、このエクセルファイルを使えば、最大損失率・最大利益率からポートフォリオを逆算することもできます。では、どうすれば最大損失率・最大利益率からポートフォリオを逆算できるのでしょうか?

 エクセルのソルバー機能を使って、最大損失率・最大利益率から、ポートフォリオを逆算します。最大損失率・最大利益率からポートフォリオを逆算する方法は、以下です。


ア.ソルバーを有効化
ソルバーはデフォルトで有効になっていないため、まず有効にします。
※ソルバーが有効になっている場合は、この手順をスキップ

a.上の「ファイル」タブをクリック

ファイルタブをクリック

b.左のメニューから「オプション」をクリック

オプションをクリック

c.左のメニューの「アドイン」をクリック

アドインをクリック

d.右下の「管理(A):」で「Excelアドイン」を選択し、「設定(G)」をクリック

管理でExcelアドインを選択し、設定をクリック

e.左のメニューの「ソルバー アドイン」にチェックを入れ、「OK」をクリック

ソルバー アドインにチェックを入れ、OKをクリック

イ.ソルバーの設定方法

a.ソルバーの起動
「データ」タブをクリックし、「ソルバー」をクリックします。

ソルバーの起動

b.最大損失率の設定

ⅰ.目的セルの設定:(T)
「目的セルの設定:(T)」の右の入力欄をクリックし、「B5」をクリックします。

目的セルの設定

ⅱ.目標値:の指定
「目標値:」は、「指定値:(V)」を選んで、目標額を入力します。ここでは、目標額を「-30」と設定しています。

目標値の設定

c.変数セルの変更:(B)
まず、「変数セルの変更:(B)」の下の入力欄をクリックしてから、B2をクリックし、シフトを押しながらE2をクリックします。

B2をクリックしてシフトを押しながらE2をクリック

d.制約条件の対象:(U)
この設定をすることで、各セルの「計算結果の数値」に条件をつけることができます(このセルは0以上に限定する、このセルは、0以下に限定するなど)。

i.「制約条件の対象:(U)」の右の「追加(A)」をクリック

制約条件の対象の右の追加をクリック

ⅱ.外国債券の投資割合を「0」に限定する
外国債券は、リスクに対してリターンが少ないため投資しません。そのため、投資割合を0にします。

設定方法

・C2をクリックします。

C2をクリック

・真ん中の記号は「=」にし、制約条件(N)は、「0」にします。

真ん中の記号は=にし、制約条件は、0にする

・ひきつづき設定をするため、下の真ん中の「追加(A)」をクリック。

追加をクリック

ⅲ.国内株式の投資割合を、全世界株式の中の割合に調整する
国内株式の投資割合を、「 (1-$B$2)*$A$10」で計算した割合に限定します。
式の意味は、
「(1-$B$2)」 投資割合全体から、日本債券に投資する割合をのぞく。
「*$A$10」 残った分(=株式に投資する分)に、全世界株式の中の国内株式割合をかける。
-という感じです。

設定方法

・D2をクリックします。

D2をクリック

・真ん中の記号は「=」にし、制約条件(N)は、「(1-$B$2)*$A$10」にします。
計算式の内容は、(全体の割合-国内債券の割合)×全世界株式の中の国内株式の割合

真ん中の記号は=にし、制約条件を入力

・ひきつづき設定をするため、下の真ん中の「追加(A)」をクリック。

追加をクリック

ⅳ.投資割合の合計を「100%」に限定する
投資割合が100%を超えないように、100%に限定します。

設定方法

・G2をクリックします。

G2をクリック

・真ん中の記号は「=」にし、制約条件(N)は、「100%」にします。

真ん中の記号は=にし、制約条件を入力

・すべての設定がおわったため、下の左の「OK」をクリックします。

OKをクリック


実際の使い方例

国内債券と全世界株式とに投資する場合、最大でも損失が-30%しかでないようにするには、どのようなポートフォリオにすればいいか

 ここでは、実際にソルバーを使ってポートフォリオを逆算します。まず、あまりの値下がりに恐ろしくなり、全資産を売却してしまいそうな「最大損失率」(=耐えられない最大損失率)から、ポートフォリオを逆算してみます。

条件:最大損失率-30%、国内債券と全世界株式とに投資

ア.ソルバーを起動します
「データ」クリックし、リボンの一番右にある「ソルバー」をクリックする。

ソルバーを起動

イ.先ほどの「ソルバーの設定方法」の項目で、必要な設定はすでにしてあるので、下にある「解決(S)」をクリックします。

解決をクリック

ウ.計算の結果、最大損失率を-30%におさえるには、「国内債券に33%、全世界株式に67%(内訳:国内株式 4%・外国株式 63%)」のポートフォリオにするよいとわかりました。

計算の結果

 なお、実際に投資する際は、国内株式に4%・外国株式に63%というようにばらばらに投資する必要はありません。33%分の国内債券インデックスファンドと、67%(=4%+63%)分の全世界株式インデックスファンド(eMAXIS Slim全世界株式(オール・カントリー)など)とに投資すればいいです。

国内債券と全世界株式とに投資する場合、最大でも利益が+40%しかでないようにするには、どのようなポートフォリオにすればいいか

 次に、あまりの値上がりに舞い上がり、全資産を売却してしまいそうな「最大利益率」(=耐えられない最大利益率)からポートフォリオを逆算してみます。

条件:最大利益率+40%、国内債券と全世界株式とに投資

ア.ソルバーを起動します
「データ」クリックし、リボンの一番右にある「ソルバー」をクリックする。

ソルバーを起動

イ.目的セルの指定を変更します。
目的セルの設定(T)を、「$B$7」(=最大利益率の計算セル)にして、指定値(V)を「40」にします。

目的セルの設定をB7にして、指定値を40に変更

ウ.下にある「解決(S)」をクリックします。

解決をクリック

エ.計算の結果、最大利益率を+40%におさえるには、「国内債券に24%、全世界株式に76%(内訳:国内株式 5%・外国株式 71%)」のポートフォリオにするとよいとわかりました。

計算の結果


ダウンロード

最初に設置しているファイルと同じものです。
>> portfolio_ return_risk_01_01.xlsx










カテゴリー一覧


Powered by
Movable Type 4.292