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

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

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

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

 投資は、リバランスするように積立資金を配分することが大切です。例えば、「資産A」の評価額が目標割合より多い場合、「資産A」に資金を配分してはいけません。積立投資は、目標割合より少ない資産に資金を配分します。

 ただ、この「積み立て投資の配分の計算」を毎月するのは面倒です。では、投資の配分を簡単に計算するには、どうすればいいのでしょうか?

 積立投資の資金配分を自動で計算できる、エクセルファイルを作りましょう。

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

完成図


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

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

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

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


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

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

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

自分で入力する欄を作成

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

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

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

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

a.各資産の評価額が、自動で計算される欄を作成
評価額は、口数×基準価額÷10000口 で計算します。 例えば「E2」には、=B2*C2/10000 と入力します。 基準価額は、10000口あたりの価格ですので、最後に10000で割ります。

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

b.全体の評価額が、自動で計算される欄を作成
全部の評価額を合計する、計算式を入力します。 例では、=SUM(E2:E10) と入力しています。

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

c.各資産の投資割合が、自動で計算される欄を作成
各資産のグループを合計した数値を、全体の評価額で割ります。 例では、上から、
=SUM(E2:E3)/E11
=SUM(E4:E7)/E11
=SUM(E8:E9)/E11 と入力しています。
例では、上から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.日本株式の割合に、バランスファンドの評価額分を追加する
例では、日本株式の評価額の合計をカッコでくくり、黄色枠:バランスファンドの日本株式の評価額分「+E11*H11」を追加しています。

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

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

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

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

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

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

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

F8に、=E11*J11/E13 を追加


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

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

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」の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」のC2をクリックして、ENTERキーを押す

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

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

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

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

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

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

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

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

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

シート:Sheet1をクリック

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

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

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

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

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

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

b.評価額を修正する
評価額だけ修正が必要になります。

i.シート:「積み立て用」のG2を、「=D2*E2/10000」から「=C2*E2/10000」に修正する

シート:積み立て用のG2を、=D2*E2/10000から=C2*E2/10000に修正

ii.修正した式を、下のセルにコピーする

修正した式を、下のセルにコピー

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

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

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

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

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

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

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

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

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

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


3.自動計算の設定

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

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

ファイルタブをクリック

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

オプションをクリック

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

アドインをクリック

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

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

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

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

イ.目標の設定

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をクリック


4.使用方法

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

種類に、数式を入力

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

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


使用時の注意点

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

投資額を確認したら、ソルバーの結果のキャンセルをクリック

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

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

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

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


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

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

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

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










カテゴリー一覧

Powered by
Movable Type 4.292