投資信託ガイドTop > 投資に役立つエクセルファイル > リバランスの資金配分を自動計算する、エクセルファイルの作り方

« 基準価額を自動取得して、資産管理できるエクセルファイルの作り方 | メイン | 投信積立シミュレーションを、エクセルで簡単にする方法 »

リバランスの資金配分を自動計算する、エクセルファイルの作り方

 投資では、はじめにきめた資産配分を維持すること(=リバランス)が大切です。資産配分を維持することで、想定以上の損失をふせぐことができます。また、資産配分を維持するには、増えすぎた資産をうったり、減りすぎた資産を買ったりします。

 ただ、この「リバランスの配分の計算」をするのは面倒です。では、リバランスの配分を簡単に計算するには、どうすればいいのでしょうか?

 リバランスの資金配分を自動で計算できる、エクセルファイルを作りましょう。


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

完成図


リバランス額計算用エクセルファイルをダウンロード

 エクセルファイルを1から作るのは大変なので、完成したエクセルファイルをダウンロードできるようにしておきます。ダウンロードしたエクセルファイルを見ながら作成方法を確認すると、よりわかりやすいと思います。

・ダウンロードリンク(バランスファンドに投資しない人用)
>> auto_rebalance_02_02.xlsx

・ダウンロードリンク(バランスファンドに投資する人用)
>> auto_rebalance_balance_02_02.xlsx


1.いま持っている資産を入力するシートを作成

現在の資産の評価額、投資割合を計算する表を作ります。

ア.自分で入力する欄を作成

自分で入力する欄を作成

a.銘柄名を入力する欄を作成
b.口数を入力する欄を作成
c.基準価額を入力する欄を作成

※基準価額を自動で取得するエクセルファイルの作り方を解説した記事もあります。この記事の方法と組み合わせるとかなり便利になります。よろしければご確認ください。
基準価額を自動取得して、資産管理できるエクセルファイルの作り方

イ.自動で計算される欄を作成

自動で計算される欄を作成

a.各資産の評価額が、自動で計算される欄を作成

評価額は、口数×基準価額÷10000口 で計算します。 例えば「G2」には、=C2*E2/10000 と入力します。 基準価額は、10000口あたりの価格ですので、最後に10000で割ります。

各資産の評価額が、自動で計算される欄を作成

b.全体の評価額が、自動で計算される欄を作成

全部の評価額を合計する、計算式を入力します。 例では、=SUM(G2:G10) と入力しています。

全体の評価額が、自動で計算される欄を作成

c.各資産の投資割合が、自動で計算される欄を作成

各資産のグループを合計した数値を、全体の評価額で割ります。 例では、上から、
=SUM(G2:G3)/G11
=SUM(G4:G7)/G11
=SUM(G8:G9)/G11 と入力しています。
例では、上から2段、4段、2段で、投資資産のグループ分けをしています。内訳は、国内株式に2銘柄、外国株式に4銘柄、国内債券に2銘柄です。

各資産の投資割合が、自動で計算される欄を作成


補足説明:バランスファンドに投資する場合の投資割合

上では、「ひとつの資産に投資する銘柄だけ」に投資する場合を解説しました。

ただ、バランスファンドを買っている方もおおいかもしれません。バランスファンドの場合、ひとつで複数の資産に投資しますので、評価額を投資資産別にくばらなくてはなりません。

そこで、バランスファンドの資産配分の方法を解説します。

バランスファンドに投資する場合の投資割合

ア.バランスファンドの運用会社のホームページで、資産配分を調べる
ここでは「eMAXIS Slim 全世界株式(オール・カントリー)」の資産配分を調べます。

a.運用会社の銘柄のホームページにアクセスする
eMAXIS Slim 全世界株式(オール・カントリー)の運用会社は、三菱USJ国際投信です。

運用会社の銘柄のホームページにアクセス

※画像は、三菱USJ国際投信のホームページより引用

b.バランスファンドの資産配分を調べる
eMAXIS Slim 全世界株式(オール・カントリー)の場合、資産配分は月報に書かれています(このファンドの場合、数値が毎月かわりますので、定期的にしらべる必要があります)。ちなみに、月報は、毎月12日くらいに更新されるようです。

バランスファンドの資産配分を調べる

※画像は、三菱USJ国際投信のホームページ及び、月報より引用

c.バランスファンドの投資配分を、銘柄の行のみぎに追加する

バランスファンドの投資配分を、銘柄の行のみぎに追加

なお、ここに更新日や、更新用URL(運用会社のホームページのURL)を書いておくと次に更新するときに便利です。

更新日や、更新用URLを書いておく

イ.バランスファンドの評価額を、各資産にくわえる計算式を追加する
eMAXIS Slim 全世界株式(オール・カントリー)は、日本株式・先進国株式・新興国株式に投資しますので、計算式を追加します。

a.日本株式の割合に、バランスファンドの評価額分を追加する
例では、日本株式の評価額の合計をカッコでくくり、黄色枠:バランスファンドの日本株式の評価額分「+G10*J10」を追加しています。

日本株式の割合に、バランスファンドの評価額分を追加

b.先進国株式の割合に、バランスファンドの評価額分を追加する
例では、先進国株式の評価額の合計をカッコでくくり、黄色枠:バランスファンドの先進国株式の評価額分「+G10*K10」を追加しています。

先進国株式の割合に、バランスファンドの評価額分を追加

c.新興国株式の割合に、バランスファンドの評価額分を追加する
例では、新興国株式に投資する投資信託を買っていません。そのため、黄色枠:バランスファンドの新興国株式の投資割合を計算する欄を、新しくつくります。

i.「8行」を右クリックして、「挿入(I)」をクリック
これで、新興国株式の投資割合を計算する行をつくります。

8行を右クリックして、挿入をクリック

ii.「H8」に、=G11*L11/G13 を追加
バランスファンドの新興国株式の評価額分を、評価額全体でわって、新興国株式の割合を計算します。

H8に、計算式を追加


(追記)2-1.売買リバランス計算用の欄を作成

ア.1.の計算表から、数値を転記する欄を作成

1.の計算表から、数値を転記する欄を作成

a.銘柄名を転記する欄を作成

i.一番上の銘柄名を「A2」から、「A16」に転記する

・A16に、「=」を入力する

A16に、=を入力

・A2をクリックして、ENTERキーを押す

A2をクリックして、ENTERキーを押す

・A16に、「= A2」と入力される

A16に、= A2と入力される

ii.転記した行を、下のセルにコピーする

・A2を、下のセルにコピーする

A2を、下のセルにコピー

b.口数を転記して、となりの「購入口数」の数値と合計する欄を作成

i.一番上の口数を、「C2」から「C16」に転記して、計算式を追加する

・C16に、「=」を入力する

C16に、=を入力

・C2をクリックし、「+d16」と入力して、ENTERキーを押す

C2をクリックし、+d16と入力して、ENTERキーを押す

・C16に、「=C2+D16」と入力される

C16に、=C2+D16と入力される

ii.転記した行を、下のセルにコピーする

・C16を、下のセルにコピーする

C16を、下のセルにコピー

c.基準価額を転記する欄を作成

i.一番上の基準価額を、「E2」から「E16」に転記する

・E16に、「=」を入力する

E16に、=を入力

・E2をクリックして、ENTERキーを押す

E2をクリックして、ENTERキーを押す

・E16に、「=E2」と入力される

E16に、=E2と入力される

ii.転記した行を、下のセルにコピーする

・E16を、下のセルにコピーする

E16を、下のセルにコピー

イ.ア.のデータを元に、計算される欄を作成

a.「自動で計算される欄:G列・H列」をコピーする

i.G2をクリックし、シフトを押しながらH11をクリックしてコピーする

G2をクリックし、シフトを押しながらH11をクリックしてコピー

ii.G16を右クリックして、貼り付ける

G16を右クリックして、貼り付ける

ウ.リバランス額の自動計算に必要な欄を作成

リバランス額の自動計算に必要な欄を作成

a.売買金額が計算される欄を作成

この欄には、「0」を入力します。

売買金額が計算される欄を作成

b.売買によって、購入(または売却)される口数が計算される欄を作成

計算式は、「売買金額÷基準価額×10,000」です。

売買によって、購入(または売却)される口数が計算される欄を作成

c.「売買金額の合計」が計算される欄を作成

全ての銘柄の売買金額を合計します。例では、「=SUM(B16:B23)」と入力しています。

売買金額の合計が計算される欄を作成


2-2.自動計算の設定

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

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

ファイルタブをクリック

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

オプションをクリック

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

アドインをクリック

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

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

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

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

イ.目標の設定

a.ソルバーを起動する
「データ」タブをクリックし、右端にある「ソルバー」をクリックします。

ソルバーを起動

b.「目標セルの設定:(T)」に、「リバランス合計」の欄を指定する

目標セルの設定に、リバランス合計の欄を指定

c. 「目標値:」は、「指定値:(V)」を選び、「0」に設定する

目標値は、指定値を選び、0に設定

ウ.制約条件の設定

a. 「変数セルの変更:(B)」に、リバランスの際に売買したい銘柄の「売買金額」欄を指定する

投資グループごとにひとつずつ銘柄を選んでください。例では、国内株式グループ、外国株式グループ、国内債券グループの割合をリバランスしたいので、各1銘柄ずつ選んでいます。

変数セルの変更に、リバランスの際に売買したい銘柄の売買金額欄を指定

b. 「制約条件の対象:(U)」で、目標の投資割合を設定する

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

制約条件の対象の右にある、追加をクリック

ii.「セル参照:(E)」で、一番上の「投資割合」欄をクリック

セル参照で、一番上の投資割合欄をクリック

iii.「制約条件:(N)」に、希望の投資割合を入力
※左の記号は「<=」にしてください。

制約条件に、希望の投資割合を入力

iv.「追加(A)」をクリック

追加をクリック

v.同じようにすべての投資割合で、a.~d.を実行
図は、次の欄の設定のためにクリックしているところです。

同じようにすべての投資割合で、a.~d.を実行

c.「制約のない変数を非負数にする(K)」のチェックをはずす
計算結果が負数(=マイナスの数値)になることもありますので、チェックをはずしておきます。

制約のない変数を非負数にするのチェックをはずす


2-3.使用方法

ア.「データ」タブをクリックし、右端にある「ソルバー」をクリック

ソルバーを起動

イ.「ソルバーのパラメータ」の下の方にある、「解決」をクリック

ソルバーのパラメータの下の方にある、解決をクリック


使用時の注意点

ア. 投資額を確認したら、「ソルバーの結果」の「キャンセル」をクリックして計算前に戻すこと
「キャンセル」をクリックするのは、「積み立て額」の数値を0に戻すためです(0にしておかないと、次に使うときに正確に計算できません)。

投資額を確認したら、ソルバーの結果のキャンセルをクリックして計算前に戻す


3-1.積み立て計算用のシートを作成

ア.積み立て計算用のシートを作成

a.+をクリックして、シートを追加

+をクリックして、シートを追加

b.「Sheet2」をダブルクリックして、シート名を「積み立て用」に変更

シート名を「積み立て用」に変更

イ.1.の計算表から、数値を転記する欄を作成

計算表から、数値を転記する欄を作成

a.シート:「積み立て用」をクリック

シート:積み立て用をクリック

b.銘柄名を転記する欄を作成

i.一番上の銘柄名を「Sheet1」から、「積み立て用」に転記する

・シート:「積み立て用」のA2に、「=」を入力する

シート:積み立て用のA2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のA2をクリックして、ENTERキーを押す。

シート:Sheet1のA2をクリックして、ENTERキーを押す

・シート:「積み立て用」のA2に、「=Sheet1!A2」と入力される

シート:積み立て用のA2に、=Sheet1!A2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「積み立て用」のA2を、下のセルにコピーする
残りの銘柄名も全てコピーされます。

シート:積み立て用のA2を、下のセルにコピー

c.口数を転記して、となりの「購入口数」の数値と合計する欄を作成
この欄は、今持っている口数と、追加投資によって購入される口数との合計が表示される欄です。

i.一番上の口数を、「Sheet1」から「積み立て用」に転記して、計算式を追加する

・シート:「積み立て用」のC2に、「=」を入力する

シート:積み立て用のC2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のC2をクリックし、「+d2」と入力して、ENTERキーを押す

シート:Sheet1のC2をクリックし、+d2と入力して、ENTERキーを押す

・シート:「積み立て用」のC2に、「=Sheet1!C2+D2」と入力される

シート:積み立て用のC2に、=Sheet1!C2+D2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「積み立て用」のC2を、下のセルにコピーする
残りの口数も全てコピーされます。

シート:積み立て用のC2を、下のセルにコピー

d.基準価額を転記する欄を作成

i.一番上の基準価額を「Sheet1」から、「積み立て用」に転記する

・シート:「積み立て用」のE2に、「=」を入力する

シート:積み立て用のE2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のE2をクリックして、ENTERキーを押す

シート:Sheet1のC2をクリックして、ENTERキーを押す

・シート:「積み立て用」のE2に、「=Sheet1!E2」と入力される

シート:積み立て用のE2に、=Sheet1!E2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「積み立て用」のE2を、下のセルにコピーする
残りの基準価額も全てコピーされます。

シート:積み立て用のE2を、下のセルにコピー

ウ.イ.のデータを元に、計算される欄を作成

a.シート:「積み立て用」に、Sheet1の「自動で計算される欄:E列・F列」をコピーする

i.シート:「Sheet1」をクリックする

シート:Sheet1をクリック

ii.シート「Sheet1」のG列をクリックし、シフトを押しながらH列をクリックしてコピーする
右クリックをしてから「コピー(C)」をクリックするとコピーできます。

シート:Sheet1のE列をクリックし、シフトを押しながらF列をクリックしてコピー

iii.シート:「積み立て用」をクリックする

シート:積み立て用をクリック

iv.シート:「積み立て用」のG列を右クリックして、貼り付ける
G列を右クリックしてから「貼り付け(P)(のアイコン)」をクリックすると貼り付けられます。

シート:積み立て用のG列を右クリックして、貼り付ける

エ. 積立額の自動計算に必要な欄を作成

積立額の自動計算に必要な欄を作成

a.追加投資額が計算される欄を作成
この欄には、「0」を入力します。

追加投資額が計算される欄を作成

b.追加投資によって、購入できる口数が計算される欄を作成
計算式は、「積み立て額÷基準価額×10,000」です。

追加投資によって、購入できる口数が計算される欄を作成

c.投資額を入力する欄を作成
この欄は、投資したい額を入力する欄です。今は「0」を入力しておきます。

投資額を入力する欄を作成

d.投資額を配分した余りが計算される欄を作成
「積み立て額」から、追加投資の合計を引いた残りを表示する欄です。
例では、「=B13-SUM(B2:B12)」と入力しています。

投資額を配分した余りが計算される欄を作成


3-2.自動計算の設定

ア.目標の設定

a.シート:「積み立て用」をクリックする

シート:積み立て用をクリック

b. ソルバーを起動する

ソルバーを起動

c. 「目標セルの設定:(T)」に、「余り」の欄を指定する

目標セルの設定に、余りの欄を指定

d. 「目標値:」は、「指定値:(V)」を選び、「0」に設定する

目標値は、指定値を選び、0に設定

イ.制約条件の設定

a. 「変数セルの変更:(B)」に、これから積み立てたい銘柄の「追加投資」欄を指定する
投資グループごとにひとつずつ銘柄を選んでください。例では、国内株式グループ、外国株式グループ、国内債券グループに投資したいので、各1銘柄ずつ選んでいます。

変数セルの変更に、これから積み立てたい銘柄の追加投資欄を指定

b. 「制約条件の対象:(U)」で、目標の投資割合を設定する

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

制約条件の対象の右にある、追加をクリック

ii.「セル参照:(E)」で、一番上の「投資割合」欄をクリック

セル参照で、一番上の投資割合欄をクリック

iii.「制約条件:(N)」に、希望の投資割合を入力
※左の記号は「<=」にしてください。

制約条件に、希望の投資割合を入力

iv.「追加(A)」をクリック

追加をクリック

v.同じようにすべての投資割合で、a.~d.を実行
図は、次の欄の設定のためにクリックしているところです。

同じようにすべての投資割合で、a.~d.を実行

c.「制約条件の対象:(U)」で、積み立て額の数値を0以上に限定する

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

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

ii.「B2」をクリックし、シフトを押しながら「B9」をクリック

B2をクリックし、シフトを押しながらB9をクリック

iii.記号を「>=」(以上という意味です)にして、制約条件:(N)の入力欄に「0」と入力
こうすることで、「積み立て額」の計算結果は、かならず0以上になります。

記号を>=にして、制約条件の入力欄に0と入力

iv.「OK」をクリック

OKをクリック


3-3.使用方法

ア. シート:「積み立て用」の「積み立て額」に、投資したい額を入力
ここでは、20万円とします。

シート:積み立て用の積み立て額に、投資したい額を入力

イ. 「データ」タブをクリックし、右端にある「ソルバー」をクリック

データタブをクリックし、ソルバーをクリック

ウ. 「ソルバーのパラメータ」の下の方にある、「解決」をクリック

ソルバーのパラメータの解決をクリック


使用時の注意点

ア.ソルバー実行後に、「余り」が0にならなかった場合、ソルバーをもう一度実行すること
ソルバー実行後に、余りが0にならなかった場合(=途中で計算がとまった場合)は、「OK」を押して数値を確定させた後に、もう一度ソルバーを実行してください。すると、のこった金額も配分されます。

ソルバー実行後に、余りが0にならなかった場合、ソルバーをもう一度実行

なお、ソルバーを2回つかった場合、キャンセルで計算前の状態にもどせません。そのため、ご自分で積み立て額を0にもどしてください。

自分で積み立て額を0にもどす


補足説明:「追加投資」を、1,000円未満四捨五入で表示する設定方法

金額を1,000円未満で四捨五入すると、表示がすっきりします。

ア. 「積み立て額」欄の、一番上のセルを右クリック

積み立て額欄の、一番上のセルを右クリック

イ. 「セルの書式設定(F)」をクリック

セルの書式設定をクリック

ウ. 「表示形式」タブの、「ユーザー定義」をクリック

表示形式タブの、ユーザー定義をクリック

エ.「種類(T):」に、「[>=500]0,"000";"0"」 と入力
式の意味は、
「[>=500]」 500以上の場合は、
「0,」 千円単位で表示(百の位を四捨五入)してください。
「"000"」 百の位以下は「000」で埋めて表示してください。
「;」 500以上でない場合、
「"0"」 「0」と表示してください。
-という感じです。

種類に、数式を入力

オ. ア.のセルを、すべての積み立て額セルにコピー

ア.のセルを、すべての積み立て額セルにコピー


(追記)4-1.取り崩しリバランス計算用のシートを作成

ア.取り崩しリバランス計算用のシートを作成

a.+をクリックして、シートを追加

+をクリックして、シートを追加

b.「Sheet3」をダブルクリックして、シート名を「取り崩し用」に変更

Sheet3をダブルクリックして、シート名を取り崩し用に変更

イ.1.の計算表から、数値を転記する欄を作成

1.の計算表から、数値を転記する欄を作成

a.シート:「取り崩し用」をクリック

シート:取り崩し用をクリック

b.銘柄名を転記する欄を作成

i.一番上の銘柄名を「Sheet1」から、「積み立て用」に転記する

・シート:「取り崩し用」のA2に、「=」を入力する

シート:取り崩し用のA2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のA2をクリックして、ENTERキーを押す

シート:Sheet1のA2をクリックして、ENTERキーを押す

・シート:「取り崩し用」のA2に、「=Sheet1!A2」と入力される

シート:取り崩し用のA2に、=Sheet1!A2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「取り崩し用」のA2を、下のセルにコピーする
残りの銘柄名も全てコピーされます。

シート:取り崩し用のA2を、下のセルにコピー

c.口数を転記して、となりの「売却口数」の数値と合計する欄を作成
この欄は、今持っている口数と、取り崩しによって売却される口数との合計が表示される欄です。

i.一番上の口数を、「Sheet1」から「取り崩し用」に転記して、計算式を追加する

・シート:「積み立て用」のC2に、「=」を入力する

シート:積み立て用のC2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のB2をクリックし、「+d2」と入力して、ENTERキーを押す

シート:Sheet1のB2をクリックし、+d2と入力して、ENTERキーを押す

・シート:「積み立て用」のC2に、「=Sheet1!B2+D2」と入力される

シート:積み立て用のC2に、=Sheet1!B2+D2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「取り崩し用」のC2を、下のセルにコピーする
残りの口数も全てコピーされます。

シート:取り崩し用のC2を、下のセルにコピー

d.基準価額を転記する欄を作成

i.一番上の基準価額を「Sheet1」から、「取り崩し用」に転記する

・シート:「取り崩し用」のE2に、「=」を入力する

シート:取り崩し用のE2に、=を入力

・シート:「Sheet1」をクリックする

シート:Sheet1をクリック

・シート:「Sheet1」のE2をクリックして、ENTERキーを押す

シート:Sheet1のE2をクリックして、ENTERキーを押す

・シート:「取り崩し用」のE2に、「=Sheet1!E2」と入力される

シート:取り崩し用のE2に、=Sheet1!E2と入力される

ii.転記した行を、下のセルにコピーする

・シート:「取り崩し用」のE2を、下のセルにコピーする
残りの基準価額も全てコピーされます。

シート:取り崩し用のE2を、下のセルにコピー


ウ.イ.のデータを元に、計算される欄を作成

a.シート:「取り崩し用」に、Sheet1の「自動で計算される欄:G列・H列」をコピーする

i.シート:「Sheet1」をクリックする

シート:Sheet1をクリック

ii.シート:「Sheet1」のG列をクリックし、シフトを押しながらH列をクリックしてコピーする
右クリックをしてから「コピー(C)」をクリックするとコピーできます。

シート:Sheet1のG列をクリックし、シフトを押しながらH列をクリックしてコピー

iii.シート:「取り崩し用」をクリックする

シート:取り崩し用をクリック

iv.シート:「取り崩し用」のG列を右クリックして、貼り付ける
G列を右クリックしてから「貼り付け(P)(のアイコン)」をクリックすると貼り付けられます。

シート:取り崩し用のG列を右クリックして、貼り付ける

エ.取り崩し額の自動計算に必要な欄を作成

取り崩し額の自動計算に必要な欄を作成

a.取り崩し額が計算される欄を作成
この欄には、「0」を入力します。

取り崩し額が計算される欄を作成

b.取り崩しによって、売却する口数が計算される欄を作成
計算式は、「取り崩し額÷基準価額×10,000」です。

取り崩しによって、売却する口数が計算される欄を作成

c.取り崩し額を入力する欄を作成
この欄は、取り崩したい額を入力する欄です。今は「0」を入力しておきます。

取り崩し額を入力する欄を作成

d.取り崩し額を配分した余りが計算される欄を作成
「取り崩し額」から、取り崩し額の合計を引いた残りを表示する欄です。
例では、「=B13-SUM(B2:B12)」と入力しています。

取り崩し額を配分した余りが計算される欄を作成


4-2.自動計算の設定

ア.目標の設定

a.シート:「取り崩し用」をクリックする

シート:取り崩し用をクリックする

b. ソルバーを起動する
「データ」タブをクリックし、右端にある「ソルバー」をクリックします。

ソルバーを起動

c. 「目標セルの設定:(T)」に、「余り」の欄を指定する

目標セルの設定に、余りの欄を指定

d.「目標値:」は、「指定値:(V)」を選び、「0」に設定する

目標値は、指定値を選び、0に設定

イ.制約条件の設定

a.「変数セルの変更:(B)」に、これから取り崩したい銘柄の「取り崩し額」欄を指定する
投資グループごとにひとつずつ銘柄を選んでください。例では、国内株式グループ、外国株式グループ、国内債券グループを取り崩したいので、各1銘柄ずつ選んでいます。

変数セルの変更に、これから取り崩したい銘柄の取り崩し額欄を指定

b. 「制約条件の対象:(U)」で、目標の投資割合を設定する

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

制約条件の対象の右にある、追加をクリック

ii.「セル参照:(E)」で、一番上の「投資割合」欄をクリック

セル参照で、一番上の投資割合欄をクリック

iii.「制約条件:(N)」に、希望の投資割合を入力
※左の記号は「<=」にしてください。

制約条件に、希望の投資割合を入力

iv.「追加(A)」をクリック

追加をクリック

v.同じようにすべての投資割合で、a.~d.を実行
図は、次の欄の設定のためにクリックしているところです。

同じようにすべての投資割合で、a.~d.を実行

c.「制約条件の対象:(U)」で、取り崩し額の数値を0以下に限定する

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

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

ii.「B2」をクリックし、シフトを押しながら「B9」をクリック

B2をクリックし、シフトを押しながらB9をクリック

iii.記号を「<=」(以下という意味です)にして、制約条件:(N)の入力欄に「0」と入力
こうすることで、「取り崩し額」の計算結果は、かならず0以下になります。

記号を<=にして、制約条件の入力欄に0と入力

iv.「OK」をクリック

OKをクリック

d.制約のない変数を非負数にする(K)のチェックをはずす
計算結果が負数(=マイナスの数値)になることもありますので、チェックをはずしておきます。

制約のない変数を非負数にするのチェックをはずす


4-3.使用方法

ア. シート:「取り崩し用」の「取り崩し額」に、取り崩したい額を入力
ここでは、-15万円とします。

シート:取り崩し用の取り崩し額に、取り崩したい額を入力

イ.「データ」タブをクリックし、右端にある「ソルバー」をクリック

データタブをクリックし、ソルバーをクリック

ウ.「ソルバーのパラメータ」の下の方にある、「解決」をクリック

ソルバーのパラメータの下の方にある、解決をクリック


使用時の注意点

ア.「評価額」にマイナスの数値がでた場合は、マイナスの金額分、同じ資産に投資するほかの銘柄をうること

マイナスは、「銘柄を全てうっても、リバランスするために必要な売却額が、マイナスの金額分足りなかった」ことを表しています。

例えば、上の例では、ニッセイ外国株式インデックスファンドの評価額が「-24,325」となっています。これは、銘柄を全てうっても、リバランスに必要な金額に24,325円足りなかったということです。

そのため、リバランスするには、同じく外国株式に投資する銘柄の、「eMAXIS先進国株式インデックス」・「SMTグローバル株式インデックス・オープン」などを、マイナスの金額分うります。

評価額にマイナスの数値がでた場合は、マイナスの金額分、同じ資産に投資するほかの銘柄をうる


補足説明:「取り崩し額」を、-1,000円未満四捨五入で表示する設定方法

金額を-1,000円未満で四捨五入すると、表示がすっきりします。

ア.「取り崩し額」欄の、一番上のセルを右クリック

取り崩し額欄の、一番上のセルを右クリック

イ.「セルの書式設定(F)」をクリック

セルの書式設定をクリック

ウ.「表示形式」タブの、「ユーザー定義」をクリック

表示形式タブの、ユーザー定義をクリック

エ.「種類(T):」に、「[<=-500]0,"000";"0"」 と入力
式の意味は、
「[<=-500]」 -500以下の場合は、
「0,」 千円単位で表示(百の位を四捨五入)してください。
「"000"」 百の位以下は「000」で埋めて表示してください。
「;」 -500以下でない場合、
「"0"」 「0」と表示してください。
-という感じです。

種類に、式を入力

オ.ア.のセルを、すべての積み立て額セルにコピー

ア.のセルを、すべての積み立て額セルにコピー


リバランス額計算用エクセルファイルをダウンロード

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

・ダウンロードリンク(バランスファンドに投資しない人用)
>> auto_rebalance_02_02.xlsx

・ダウンロードリンク(バランスファンドに投資する人用)
>> auto_rebalance_balance_02_02.xlsx



Twitterフォロワー募集中!