積立投資は、リバランスするように資金を配分することが大切です。例えば、「資産A」の評価額が目標割合より10％多い場合、「資産A」に資金を配分してはいけません。積立投資は、目標割合より少ない資産に資金を配分します。
ただ、この「資金配分の計算」は、目標割合からのズレが少ない場合、面倒になります。ここでは、資産全体で5万円しか目標割合からのズレがない場合で考えてみましょう。この場合、5万円を超える積立投資分は、目標割合にしたがって資金を配分しなければなりません。
例えば、15万円を積立投資する場合、ズレをリバランスするために5万円を使った残りの、10万円を配分します。目標割合が、「国内株式：25％・外国株式：25％・国内債券：50％」なら、
- 国内株式に2.5万円（＝10万円 × 25％）投資する
- 外国株式に2.5万円（＝10万円 × 25％）投資する
- 国内債券に5万円（＝10万円 × 50％）投資する
―と計算しなければならないのです。
では、資金配分を簡単に計算するには、どうすればいいのでしょうか？
積立投資の資金配分を自動で計算できる、エクセルファイルを作りましょう。
- ●完成図（こんなファイルを作ります）
- １．資産額の計算表を作成
- ２．積立額の計算表を作成
- ３．ソルバーを設定
- ４．使用方法
- ●使用時の注意点
- ●リバランス額計算用エクセルファイル（動作確認だけ）をダウンロード
完成図（こんなファイルを作ります）
１．資産額の計算表を作成
現在の資産の評価額、投資割合を計算する表を作ります。表の作り方は過去に記事にしていますので、参考にしてみてください。
| ・ | 資産額の計算表の作り方 |
| 保有資産の評価額・各資産への投資割合が、自動で計算されるファイルです。 作り方はこちら→基準価額を自動取得して、資産管理できるエクセルファイルの作り方 |
※クリックで拡大します。
２．積立額の計算表を作成
| ア． | １．の計算表から、数値を転記する欄を作成 |
|
|
| 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．投資額を配分した余りが計算される欄を作成 | |
| 投資額から、追加投資の総額を引いた残りを表示する欄です。 | |
|
|
３．ソルバーを設定
| ・ | ソルバーを有効にする |
|
ソルバーはデフォルトで有効になっていないため、有効にします。 ※ソルバーが有効になっている場合は、この手順をスキップ |
|
| a．上の「ファイル」タブをクリック | |
|
|
| b．左のメニューから「オプション」をクリック | |
|
|
| c．左のメニューの「アドイン」をクリック | |
|
|
| d．右下の「管理（A）：」で「Excelアドイン」を選択し、「設定（G）」をクリック | |
|
|
| e．左のメニューの「ソルバー アドイン」にチェックを入れ、「OK」をクリック | |
|
|
| ア. | ソルバーを起動する |
| 「データ」タブをクリックし、右端にある「ソルバー」をクリックします。 | |
|
|
| イ. | 「目標セルの設定：（T）」に、「余り」の欄を指定する |
|
|
| ウ. | 「目標値：」は、「指定値：（V）」を選び、「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」と表示してください。 －という感じです。 |
|
|
|
| オ. | ア．のセルを、すべての追加投資セルにコピー |
|
|
４．使用方法
| ア． | 「投資額」に、投資したい額を入力 |
|
|
| イ． | 「データ」タブをクリックし、右端にある「ソルバー」をクリック |
|
|
| ウ． | 「ソルバーのパラメータ」の下の方にある、「解決」をクリック |
|
|
使用時の注意点
| ア． | 1万円未満で表示された分は、投資できるところに配分すること |
| 投資信託は、1万円以上でないと投資できません。 1万円未満は、投資できるところに投資してください。 | |
|
|
| イ． | 投資額を確認したら、「ソルバーの結果」の「キャンセル」をクリックして計算前に戻すこと |
| 「キャンセル」をクリックするのは、「追加投資」の数値を0に戻すためです（0にしておかないと、次に使うときに正確に計算できません）。 | |
|
|
リバランス額計算用エクセルファイル（動作確認だけ）をダウンロード
実際の動作を確認できる、エクセルファイルをダウンロードできるようにしておきます。ただし、このエクセルファイルは、投資信託の基準価額が取得できません。ご了承ください。
＞＞ リバランス額計算用エクセルファイル（動作確認だけ）
投資信託で資産運用するなら、この証券会社が優れています。
＞＞ＳＢＩ証券
