ポートフォリオを組んだ場合のリターン・リスクの計算方法を、「資産を組み合わせた場合のリターン計算」・「資産を組み合わせた場合のリスク計算」で、解説しました。
ただ、いろいろなポートフォリオのリターン・リスクを調べたい場合、いちいち計算するのは大変です。どうにか楽に計算できないでしょうか？
それには、投資割合を入力すれば自動でリターン・リスクを計算する、エクセルファイルを作っておけばいいです。ポートフォリオのリターン・リスクの自動計算エクセルファイルの作り方は以下です。
なお、手順内の計算は、"国内債券0%・外国債券30%・国内株式40%・外国株式30%"という投資割合で計算しています。
- 完成図（こんなファイルをつくります）
- ダウンロード
- 自分で入力する欄をつくる
- 計算に必要な数値を入力する欄をつくる
- 自動で計算される欄をつくる
- 実際の使い方例
- （追記）最大損失率・最大利益率からポートフォリオを逆算する方法
- 実際の使い方例
- ダウンロード
完成図（こんなファイルをつくります）
ダウンロード
エクセルファイルを１から作るのは大変なので、完成したエクセルファイルをダウンロードできるようにしておきます。ダウンロードしたエクセルファイルを見ながら作成方法を確認すると、よりわかりやすいと思います。
＞＞ 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．基本 ポートフォリオの変更について（詳細）をクリックする
項目：「第４期中期目標期間（2020年4月1日からの５カ年）における基本ポートフォリオ」の下の方に、「基本 ポートフォリオの変更について（詳細）[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