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

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

« 基準価額を自動取得して、資産管理できるエクセルファイルの作り方 | メイン | 信託報酬にこだわることの大切さに気づくために、毎年いくら支払うことになるか計算してみる »

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

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

 ただ、この「資金配分の計算」は、目標割合からのズレが少ない場合、面倒になります。ここでは、資産全体で5万円しか目標割合からのズレがない場合で考えてみましょう。この場合、5万円を超える積立投資分は、目標割合にしたがって資金を配分しなければなりません。

例えば、15万円を積立投資する場合、ズレをリバランスするために5万円を使った残りの、10万円を配分します。目標割合が、「国内株式:25%・外国株式:25%・国内債券:50%」なら、

  • 国内株式に2.5万円(=10万円 × 25%)投資する
  • 外国株式に2.5万円(=10万円 × 25%)投資する
  • 国内債券に5万円(=10万円 × 50%)投資する

―と計算しなければならないのです。

 では、資金配分を簡単に計算するには、どうすればいいのでしょうか?

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

  • ●完成図(こんなファイルを作ります)
  • 1.資産額の計算表を作成
  • 2.積立額の計算表を作成
  • 3.ソルバーを設定
  • 4.使用方法
  • ●使用時の注意点

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

完成図


1.資産額の計算表を作成

 現在の資産の評価額、投資割合を計算する表を作ります。表の作り方は過去に記事にしていますので、参考にしてみてください。

資産額の計算表の作り方
保有資産の評価額・各資産への投資割合が、自動で計算されるファイルです。
作り方はこちら→基準価額を自動取得して、資産管理できるエクセルファイルの作り方

資産額の計算表
※クリックで拡大します。


2.積立額の計算表を作成

ア. 1.の計算表から、数値を転記する欄を作成
転記する欄
a.銘柄名を転記する欄を作成
銘柄名の転記欄
b.口数を転記して、となりの「購入口数」の数値と合計する欄を作成
今持っている口数と、追加投資によって購入される口数との合計が表示される欄です。
口数を転記して、購入口数と合計する欄
c.基準価額を転記する欄を作成
基準価額の転記欄
イ. ア.のデータを元に、計算される欄を作成
ア.のデータから自動で計算される欄
a.評価額が計算される欄を作成
評価額は、「口数×基準価額÷10,000」で計算します。最後に10,000で割るのは、基準価額が10,000口あたりの数値で表示されるからです。
評価額の表示欄
b.全体の評価額が計算される欄を作成
すべての評価額を合計した数値が表示される欄です。
全体の評価額の表示欄
c.投資割合が計算される欄を作成
各資産のグループへの投資割合が表示される欄です。投資割合は、各資産のグループを合計した数値を、全体の評価額で割って計算します。
例では、上から、
=SUM(G15:G16)/G24
=SUM(G17:G20)/G24
=SUM(G21:G22)/G24 と入力しています。
例では、上から2段、4段、2段で、投資資産のグループ分けをしています。 内訳は、国内株式(TOPIX)に2銘柄、外国株式に4銘柄、国内債券に2銘柄です。
投資割合の表示欄
ウ. 積立額の自動計算に必要な欄を作成
積立額の計算に必要な欄
a.追加投資額が計算される欄を作成
この欄には、「0」を入力します。
追加投資額の表示欄
b.追加投資によって、購入できる口数が計算される欄を作成
計算式は、「追加投資額÷基準価額×10,000」です。
追加投資による購入口数の表示欄
c.投資額を入力する欄を作成
この欄は、投資したい額を入力する欄です。今は「0」を入力しておきます。
投資額の入力欄
d.投資額を配分した余りが計算される欄を作成
投資額から、追加投資の総額を引いた残りを表示する欄です。
余りの表示欄

3.ソルバーを設定

ソルバーを有効にする
ソルバーはデフォルトで有効になっていないため、有効にします。
※ソルバーが有効になっている場合は、この手順をスキップ
a.上の「ファイル」タブをクリック
ファイルをクリック
b.左のメニューから「オプション」をクリック
オプションをクリック
c.左のメニューの「アドイン」をクリック
アドインをクリック
d.右下の「管理(A):」で「Excelアドイン」を選択し、「設定(G)」をクリック
Excelアドインを選択し、設定をクリック
e.左のメニューの「ソルバー アドイン」にチェックを入れ、「OK」をクリック
ソルバーアドインをチェックし、OKをクリック
ア. ソルバーを起動する
「データ」タブをクリックし、右端にある「ソルバー」をクリックします。
データタブをクリックし、ソルバーをクリック
イ. 「目標セルの設定:(T)」に、「余り」の欄を指定する
余りの欄を指定
ウ. 「目標値:」は、「指定値:(V)」を選び、「0」に設定する
指定値を選択し、0と入力
エ. 「変数セルの変更:(B)」に、これから積み立てたい銘柄の「追加投資」欄を指定する
投資グループごとにひとつずつ銘柄を選んでください。例では、国内株式グループ、外国株式グループ、国内債券グループに投資したいので、各1銘柄ずつ選んでいます。
投資したい銘柄を指定
オ. 「制約条件の対象:(U)」で、目標の投資割合を設定する
a.「制約条件の対象:(U)」の右にある、「追加(A)」をクリック
追加をクリック
b.「セル参照:(E)」で、一番上の「投資割合」欄をクリック
一番上の投資割合をクリック
c.「制約条件:(N)」に、希望の投資割合を入力
※左の記号は「=」ではなく、「<=」で指定するのがポイントです。 「=」にすると、計算が途中で止まることがあります。
制約条件に、希望の投資割合を入力
d.「追加(A)」をクリック
追加をクリック
e.同じようにすべての投資割合で、a.~d.を実行
図は、次の欄の設定のためにクリックしているところです。
次の欄の投資割合をクリック


補足説明

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

金額を1,000円未満四捨五入すると、表示がすっきりします。
この手順をとばす場合は、こちらをクリック

ア. 「追加投資」欄の、一番上のセルを右クリック
追加投資欄の一番上のセルを右クリック
イ. 「セルの書式設定(F)」をクリック
セルの書式設定をクリック
ウ. 「表示形式」タブの、「ユーザー定義」をクリック
表示形式タブををクリックし、ユーザー定義をクリック
エ. 「種類(T):」に、「[>=500]0,"000";"0"」 と入力
式の意味は、
「[>=500]」 500以上の場合は、
「0,」 千円単位で表示(百の位を四捨五入)してください。
「"000"」 百の位以下は「000」で埋めて表示してください。
「;」 500以上でない場合、
「"0"」 「0」と表示してください。
-という感じです。
式を入力
オ. ア.のセルを、すべての追加投資セルにコピー
すべての追加投資セルにコピー


4.使用方法

ア. 「投資額」に、投資したい額を入力
投資額に投資したい額を入力
イ. 「データ」タブをクリックし、右端にある「ソルバー」をクリック
データタブをクリックし、ソルバーをクリック
ウ. 「ソルバーのパラメータ」の下の方にある、「解決」をクリック
解決をクリックすると、投資額が自動計算される

・使用時の注意点

ア. 1万円未満で表示された分は、投資できるところに配分すること
投資信託は、1万円以上でないと投資できません。 1万円未満は、投資できるところに投資してください。
1万円未満は投資できるところに投資
イ. 投資額を確認したら、「ソルバーの結果」の「キャンセル」をクリックして計算前に戻すこと
「キャンセル」をクリックするのは、「追加投資」の数値を0に戻すためです(0にしておかないと、次に使うときに正確に計算できません)。
キャンセルをクリックすると、計算前の状態に戻る





「おすすめ投資信託」を解説した記事はこちら
>>http://teiiyone.com/blog/cat19/




カテゴリー一覧

投資ツール