投資信託ガイドTop > 投資に役立つエクセルファイル > 資産運用のバックテストが簡単にできるエクセルファイルの作り方

« 2022年6月の投資信託0.3%取り崩し。今月は3,608円取り崩せました。 | メイン | 2022年7月の投資信託0.3%取り崩し。今月は3,473円取り崩せました。 »

資産運用のバックテストが簡単にできるエクセルファイルの作り方

 年金の運用機関が公開しているデータや、さまざまな書籍のデータなどで、投資資産からの期待リターンはだいたいわかっている方は多いかもしれません。

 ただ、実際に過去に運用していたらどうなっていたのか、知りたい方もおられるかもしれません。では、過去に運用していたらどうなっていたかを知るには、どうすればいいのでしょうか?

 資産運用のバックテストができる、エクセルファイルをつくりましょう。資産運用のバックテスト用のエクセルファイルの作り方は、以下です。


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

投資額を入力すると、結果を自動で計算

30年前に月5万円の積み立てをはじめた場合の結果を自動で計算

最大・最低・中央値リターンの期間を自動で計算


エクセルファイルをダウンロード

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

・ダウンロードリンク(全世界株式のバックテスト用)
>> toshin_backtest_zensekai_01_01.xlsx

・ダウンロードリンク(米国株式のバックテスト用)
>> toshin_backtest_america_01_01.xlsx


自分で入力する欄を作成

1.取り崩し率を入れる欄を作成

 A1に、毎年取り崩す率を入力します。例では、年に4.5%取り崩すとして、A1に、「4.5%」と入力します。

取り崩し率を入力

 なお、取り崩しをおこなわない「積み立てのみ」のバックテストをしたい場合は、0と入力してください。

2.初期投資額を入力する欄を作成

 C2に、最初に投資する額を入力する欄をつくります。例では、4,000万円を最初に用意した場合のバックテストをしますので、C2に「4,000」と入力します。

初期投資額を入力

3.積立額を入力する欄を作成

 積み立て投資のバックテストをする際に使用する、毎年の積立額を入力する欄をつくります。例では、取り崩しを想定していますので、H1に、「0」と入力しています。

毎年の積立額を入力

4.年次リターンのデータを入力する欄を作成

ア.まず、計算したいインデックスの、年次リターンデータ(=1年ごとのリターンデータ)を入力します。

 過去のバックテストをするには、インデックスの過去の年次リターンデータが必要になります。そのため、まず、バックテストしたいインデックスの年次リターンデータを手にいれなければなりません。

 今回は、「myINDEX」というサイトの「ACWI(=全世界株式のインデックス)」の、円換算された年次リターンデータを使わせていただきます。使用したデータがある場所は、以下です。

a.「myINDEX」の「MSCI オール・カントリー・ワールド・インデックス (ACWI) (円) 」のページにアクセスします。

myINDEXのサイト画像

 なお、「MSCI オール・カントリー・ワールド・インデックス (ACWI) (円) 」は、先進国(日本を含む)と新興国との株式指標を、円換算したものです。

MSCIオール・カントリー・ワールド・インデックス(MSCI ACWI)は、MSCI指数の一つで、米国のMSCI Inc.が算出・公表する、世界の株式を対象とした株価指数をいいます(ACWIは、「All Country World Index」の略)。これは、世界の先進国(23カ国)と新興国(24カ国)の株式の総合投資収益を各市場の時価総額比率で加重平均して指数化したもので、世界の株式の時価総額(浮動株調整後)の約85%をカバーしています。

iFinanceより引用

b.アクセスしたページの下の方にある、「グラフ」という項目の「年次リターン」タブをクリックします。

年次リターンタブをクリック

c.グラフになっていますが、マウスカーソルをあわせると数値がでます。

マウスカーソルをあわせてリターン表示

 これが各年の世界株式の利回りです。画像では1988年の利回りが28.1%だとわかります。

わき道

他のインデックスでバックテストしたいときの探し方
 myINDEXには、他のインデックス(米国株式のインデックス:S&P 500 (配当込み) (円) 、国内債券のインデックス:NOMURA-BPI 総合)の年次リターンも公開されていますので、他のもので計算したい場合は、それらの年次リターンを入力してください。

 ここでは例として、米国株式インデックスを探します。

ア.まず、「myINDEX」の「インデックスの検索ページ」にアクセス

myINDEXのインデックス検索ページ
※上はスマホでの表示なので、PCでの表示とすこし異なります。

イ.入力欄に、探したいインデックス名を入力
 今回は米国株式のインデックスを探すので、「S&P500」と入力して、検索ボタンを押します。

S&Pと入力

ウ.「インデックスのみ表示」をクリック
 最初はETFが一覧で表示されますので、インデックスのみの表示にします。

インデックスのみ表示をクリック

エ.S&P 500 (配当込み) (円)をクリック
 使いたい米国株式インデックスの年次リターンは、「S&P 500 (配当込み) (円) 」なので、これをクリックします。

S&P 500 (配当込み) (円) をクリック

 なお、このデータは、S&P 500の配当収益をふくんだ数値を、円換算したものになります。

イ.つぎに、ア.で手にいれたリターンデータの最初の年を入力します。

 今回のデータでは、1988年が最初の年なので、A2に「1988」と入力します。

リターンデータの最初の年を入力


補足説明

先頭行を固定する
 先頭行を上に固定して、ずっと表示されるようにしておくとより便利です。先頭行を固定する場合は、以下の操作をしてください。

ア.エクセルのメニューの表示をクリックして、リボンから「ウィンドウ枠の固定」をクリックします。

ウィンドウ枠の固定をクリック

イ.表示された一覧から、「先頭行の固定(R)」をクリックします。

先頭行の固定をクリック


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

1.年数データ(2年目以降)が計算される欄を作成
 運用年数が自動で計算されるように、前の年に1を足した数が表示されるようにします。例では、A3に、「=A2+1」と入力しています。

2年目以降の年数データの計算式を入力

 A3のセルを、データの最後の年までコピーします(今回のデータでは、2021年です)。

年数の計算式を最後の年までコピー

2.取り崩し額が計算される欄を作成
a.まず、「年初資産額」に「取り崩し率」を掛けて、取り崩し額が自動で計算されるようにします。

 例では、D2に、「=C2*$A$1」と入力しています。ちなみに、式の中の$マークは、セルをコピーしても、同じところを指定する記号です。この記号により、コピーしてもA1を指定しつづけることができます($マークをつけない場合、コピーするとA2、A3のように指定場所がずれていきます)。

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

 E2のセルをコピーして、データの最後の年までコピーします。

取り崩し額の計算式を、最後の年までコピー

b.つぎに、取り崩し額を1ヶ月あたりで表示する欄をつくります。

 取り崩し額が年額のままだとすこしイメージがしにくいので、取り崩しの月額も表示するようにします。

 G2に、取り崩し額を12ヶ月で割った数字を計算します。例では、G2に、「=ROUNDDOWN(D2/12,1)」と入力しています(ROUNDDOWN関数で、小数点2位以下は切り捨てています)。

取り崩し月額を計算

 G2のセルをコピーして、データの最後の年までコピーします。

取り崩し月額の計算式を、を最後の年までコピー

3.取り崩し後の資産額が計算される欄を作成
 年初資産額から、取り崩し額をひいた後の数値が自動で計算される欄をつくります。例では、E2に、「=C2-D2」と入力しています。

取り崩し後の資産額を計算

 E2のセルをコピーして、データの最後の年までコピーします。

取り崩し後の資産額の計算式を、最後の年までコピー

4.年末の資産額が計算される欄を作成
 取り崩し後の資産額に、年次リターンをくわえ、さらに年間の積立額をたした額が自動で計算される欄をつくります。例では、F2に、「=E2*(1+B2)+$H$1」と入力しています。

年末の資産額を計算

 F2のセルをコピーして、データの最後の年までコピーします。

年末の資産額の計算式を、最後の年までコピー

5.2行目以降の年初資産額に、年末資産額が転記される欄を作成
 2行目以降の年初資産額には、前年の年末資産額が転記されるようにします。例では、C3に、「=F2」と入力しています。

年末の資産額を転記

 C3のセルをコピーして、データの最後の年までコピーします。

年末の資産額を転記する式を、最後の年までコピー


実際の使用例

リーマンショックの年に、セミリタイアを開始していたら今どうなっていたのか?
 エクセルファイルをつかって、この34年間で最悪の下落幅(-52.9%)である、リーマンショックのときにセミリタイアを開始していたらどうなったかを調べてみます。取り崩し条件:投資対象 全世界株式、取り崩し率 4.5%、初期投資額 4,000万円、積立額 0円

 まず、取り崩し条件にあわせて、「取り崩し率」に4.5%、「積立額」に0と入力します。

取り崩し率と積立額を入力

 リーマンショックは、2008年なので、2008年の「年初資産額」に、4,000と入力します。

2008年の年初資産額に4000を入力

 すると、2021年の年末資産額が、「5,836」と表示されます。

2021年の年末資産額

 この条件の場合、年4.5%を取り崩したとしても、2021年には資産額が5,836万円に増えていたようです。

 このままだと、つぎに使うときに正確に計算されないので、元に戻しておきます。元に戻す方法は、エクセルの一番上にある「左にまがった矢印」をクリックするか、「Ctrlキーを押しながらZキー」を押します。

元に戻すには左に曲がった矢印をクリック

今から30年前に、年60万円の積み立てを開始していたら、今どうなっていたのか?
 エクセルファイルをつかって、今(=2021年)から30年前に、全世界株式に月5万円(=年60万円)ずつ積み立てていた場合、どうなっていたのかを調べてみます。なお、初期投資額は、200万円とします。

積み立て条件:投資対象 全世界株式、取り崩し率 0%、初期投資額 200万円、積立額 60万円

 まず、積み立て条件にあわせて、「取り崩し率」に0、「積立額」に60と入力します。

取り崩し率と積立額を入力

 つぎに、今から30年前は1992年なので、1992年の「年初資産額」に、200と入力します。
※開始年は1992年です。開始年も運用をしますので、2021年-30年=1991年としないようにしてください。

1992年の年初資産額に200を入力

 すると、2021年の年末資産額が、「9,834」と表示されます。

2021年の年末資産額

 この条件の場合、年60万円(=月5万円)を積み立てるだけで、2021年には約1億円の資産ができていたようです。

 このままだと、つぎに使うときに正しく計算されないので、もとに戻しておきます。なお、複数箇所を変更したので、完全に元に戻るまで、複数回もとに戻す操作をしてください。

 もとに戻す方法は、エクセルの一番上にある「左にまがった矢印」をクリックするか、「Ctrlキーを押しながらZキー」を押します。

元に戻すには左に曲がった矢印をクリック

1992年から運用をはじめ、2021年に8,000万円をのこしたい場合、最初に投資すべき額はいくらか?
 エクセルファイルをつかって、今(=2021年)に8,000万円をのこすためには、30年前(=1992年)にいくら投資していればよかったのかを調べます。なお、積み立てはしないものとします。条件:投資対象 全世界株式、取り崩し率 0%、積立額 0万円

 まず、積み立て条件にあわせて、「取り崩し率」に0、「積立額」に0と入力します。

取り崩し率と積立額を入力

 つぎに、ゴールシークで、年初資産額を逆算します。

ア.1992年の「年初資産額」に、0と入力します(数式のままだとゴールシークが使えないため)。

1992年の年初資産額に0を入力

イ.ゴールシークを起動します。

ゴールシークを起動

ウ.ゴールシークの設定をします。
ゴールシークの設定は、以下です。
・数式入力セル(E):に「F35」に指定
・目標値(V):を「8000」に指定
・変化させるセル(C):に「C$6$」を指定

ゴールシークの設定

 ゴールシークで計算すると、1992年の年初資産額が、「797」と表示されます。

1992年の年初資産額

 この条件の場合、1992年に797万円を投資していれば、2021年の年末には8,000万円になっていたようです。

 このままだと、つぎに使うときに正しく計算されないので、もとに戻しておきます。なお、複数箇所を変更したので、完全に元に戻るまで、複数回もとに戻す操作をしてください。

 もとに戻す方法は、エクセルの一番上にある「左にまがった矢印」をクリックするか、「Ctrlキーを押しながらZキー」を押します。

元に戻すには左に曲がった矢印をクリック


(応用)過去に○年運用した場合に、もっとも収益がよかったとき・悪かったとき、ちょうどまん中のときに、結果がどうなっていたのかを計算できるようにする方法

 過去にもっとも収益がよかったとき、わるかったとき、またはちょうどまん中だったときはどの期間だったか知っておくと、その期間に運用していたらどうなったかを調べやすくなります。

 たとえば、30年運用したときにもっとも収益がわるかった期間でバックテストすれば、それ以下になる確率はひくいだろうと予測できます。また、ちょうどまん中の収益の期間でバックテストすれば、有利でも不利でもない条件でバックテストができます。

 そこで、エクセルファイルを拡張して、そういった期間を自動で調べられるようにしましょう。そのためには、以下のようにします。

もっとも収益がよかったとき・わるかったとき、ちょうどまん中のときの期間を確認する

1.年次リターンを○倍に変換する欄をつくる
まず、年次リターンの数値に1をたして、「○%のリターン」から、「○倍」に変換する欄をつくります。たとえば、I2には、「=1+B2」と入力しています。

年次リターンを○倍に変換する欄

 例では、「28.1%リターン」から、「1.28倍」に変換されました。

 つぎに、I2をコピーして、すべての年次リターンを○倍に変換します。

2.複数年運用したときの結果を計算する

ア.複数年運用したときに、資産が何倍になったか計算する

まず、10年分を掛けあわせて、10年運用したときのリターンを計算する

a.PRODUCT関数をつかって、10年分の倍数を掛けあわせます。すると、指定した期間に運用したときに、資産が何倍になったか計算できます。

 たとえば、J11には、「=PRODUCT(I2:I11) 」と入力しています。このように入力すると、1988年から1997年の10年に運用したときに、資産が何倍になったかがわかります。

10年間運用時の資産倍率の計算式

b.J11をコピーして、すべての期間で10年運用したときの数値を計算します。

全ての期間で10年運用時の資産倍率を計算

つぎに、20年分を掛けあわせて、20年運用したときのリターンを計算する

a.PRODUCT関数をつかって、20年分の倍数を掛けあわせます。すると、指定した期間に運用したときに、資産が何倍になったか計算できます。

 たとえば、M21には、「=PRODUCT(I2:I21)) 」と入力しています。このように入力すると、1988年から2007年の20年に運用したときに、資産が何倍になったかがわかります。

20年間運用時の資産倍率の計算式

b.M21をコピーして、すべての期間で20年運用したときの数値を計算します。

全ての期間で20年運用時の資産倍率を計算

そして、30年分を掛けあわせて、30年運用したときのリターンを計算する。

a.PRODUCT関数をつかって、30年分の倍数を掛けあわせます。すると、指定した期間に運用したときに、資産が何倍になったか計算できます。

 たとえば、P31には、「=PRODUCT(I2:I31)」と入力しています。このように入力すると、1988年から2017年の30年に運用したときに、資産が何倍になったかがわかります。

30年間運用時の資産倍率の計算式

b.P31をコピーして、すべての期間で30年運用したときの数値を計算します。

全ての期間で30年運用時の資産倍率を計算

イ.運用終了年のデータを追加する

 リターンを計算したセルの2つ右のセルに、運用終了年を転記します。たとえば、1988~1997年の運用結果が計算された、J11の場合、2つ右のL11に、運用終了年(=1997年)が記載されたA11を転記します(「=A11」と入力しています)。

運用終了年を転記

 つぎに、L11をコピーして、すべての期間で、運用終了年が転記されるようにします。

全ての期間で運用終了年を転記

 なお、これは10年運用だけでなく、20年運用・30年運用の欄でもおこないます。


わき道

数字に"年"をつけて表示するには?
 運用開始年・運用終了年の数値に"年"とつけておくと、わかりやすくなります。"年"をつける方法は、以下です。

a.セルの書式を変更する

 "年"をつけたいセルを右クリックして、「セルの書式設定(F)」をクリックします。

セルの書式変更方法

 セルの書式設定メニューが表示されるので、分類:から、「ユーザー定義」をクリックします。種類(T):に「G/標準」と表示されているので、"年"を追記して、「G/標準"年"」に変更して「OK」を押します。

ユーザー定義で年を追記

b.他のセルに書式をコピーする

 先ほど年を表示するように変更したセルを右クリックして、コピー(C)をクリックします。

セルをコピー

 「年」を追加したいセルを全て選びます。例では、L12にカーソルをあわせた後、SHIFTキーを押しながらL35をクリックして、運用終了年が表示されるすべてのセルを選んでいます。

複数のセルを選択

 そのまま右クリックして、貼り付けオプションの右から2番目の、書式設定(R)のアイコンをクリックします。

全てのセルにコピー

ウ.運用開始年のデータを追加する

 運用終了のセルの左に、運用開始年を計算する欄をつくります。

 運用開始年の計算式は、10年運用の場合、運用終了の年から10を引いて、1をたします。1をたすのは、運用開始の年も運用をするからです。たとえば、10年運用の場合、1997年の左の欄には、「=L11-10+1」と入力しています。

運用開始年を計算

 つぎに、K11をコピーして、すべての期間で、運用開始年が計算されるようにします。

セルをコピー

 なお、これも10年運用だけでなく、20年運用・30年運用でもおこないます。ただし、20年運用の場合は、引く数字を10→20に変更し、30年運用の場合は、引く数字を10→30に変更します。そのまま-10で計算しないように注意してください。

20年運用・30年運用での入力の注意点

3.2.のデータから、もっとも運用成績がよかった期間・わるかった期間・ちょうどまん中の期間をさがす

ア.運用結果のなかで、もっとも運用成績がよかった期間をさがす

 運用成績がもっともよかった期間をさがすには、まず、指定した範囲の中でもっとも高い数値をさがす、MAX関数を使います。

 例では、10年運用をしたときのもっとも高いリターンを探すため、J37に、「=MAX(J11:J35)」と入力しています。この式で、もっとも高い運用成績が4.85倍だとわかりました。

max関数で最大リターンを探す

 つぎに、INDEX関数をつかって、最大の運用成績だったときの運用期間が、何年から何年のときだったかを調べます。

a.最大リターンだったときの運用開始年を調べる運用開始年を調べるには、INDEX関数を使います。例では、K37に、「=INDEX(J11:L35,MATCH(J37,J11:J35,0),2)」と入力しています。

index関数で最大リターン時の運用開始年を表示

 INDEX関数は少しややこしいので、解説をいれます。
INDEX関数の式の意味は、
「=INDEX(」 INDEX関数(データの中から、指定した行・列にある値を表示する関数)を使います。
↓データの範囲を指定
「J11:L35,」 この範囲のデータから指定の数値を探してください。
↓行を指定
「MATCH(」 MATCH関数(指定した数値が何行目にあるか探す関数)を使います。
「J37,」 この数値と同じ数値を探してください(この例では、最大リターンの数値:4.85を探しています)。
「J11:J35,」 この範囲(10年運用の運用結果)から探してください。
「0), 」 探す数値と完全に一致するものを探してください。
↓列を指定
「2) 」 データの2列目(運用開始年の列)を指定します。
―という感じです。

 この例では、10年運用したときにもっとも大きかったリターン:4.85倍が得られたときの、開始年数:2012年 が表示されました。

最大リターン時の運用開始年

b.最大リターンだったときの運用終了年を調べる

 運用終了年を調べる際も、INDEX関数を使います。例では、L37に、「=INDEX(J11:L35,MATCH(J37,J11:J35,0),3) 」と入力しています。

index関数で最大リターン時の運用終了年を表示

 運用開始年を調べたときとほとんど同じ式ですが、式の最後の数値が2→3(運用終了年の列)に変わっていますので注意してください。

 この例では、10年運用したときにもっとも大きかったリターン:4.85倍のリターンが得られたときの、終了年数:2021年が表示されました。

最大リターン時の運用終了年

イ.運用結果のなかで、もっとも運用成績がわるかった期間をさがす

 運用成績がわるかった期間を探すには、まず、指定した範囲の中でもっとも低い数値を探す、MIN関数を使います。

 例では、J38に、「=MIN(J11:J35)」と入力しています。この式で、もっとも低いリターンだったのが0.78倍だとわかりました。

min関数で最低リターンを探す

 つぎに、INDEX関数をつかって、最低リターンだったときの運用機関が、何年から何年のときだったかを調べます。

a.最低リターンだったときの運用開始年を調べる

 運用開始年を調べるには、INDEX関数を使います。例では、K38に、「=INDEX(J11:L35,MATCH(J38,J11:J35,0),2)」と入力しています。

index関数で最低リターン時の運用開始年を表示

 この例では、10年運用したときにもっとも小さかったリターン:0.78倍が得られたときの、開始年数:1999年 が表示されました。

最低リターン時の運用開始年

b.最低リターンだったときの運用終了年を調べる

 運用終了年を調べる際も、INDEX関数を使います。例では、L38に、「=INDEX(J11:L35,MATCH(J38,J11:J35,0),3) 」と入力しています。

index関数で最低リターン時の運用終了年を表示

 運用開始年を調べたときとほとんど同じ式ですが、式の最後の数値が2→3に変わっていますので注意してください。

 この例では、10年運用したときにもっとも小さかったリターン:0.78倍のリターンが得られたときの、終了年数:2008年が表示されました。

最低リターン時の運用終了年

ウ.運用結果のなかで、ちょうどまん中の運用成績だった期間をさがす

 運用成績がちょうどまん中だった期間を探すには、まず、指定した範囲の中のちょうどまん中の数値を探す、MEDIAN関数を使います。

 例では、J39に、「=MEDIAN(J11:J35)」と入力しています。この式で、ちょうどまん中のリターンだったのが2.15倍だとわかりました。

median関数でちょうどまん中のリターンを探す

 つぎに、INDEX関数をつかって、ちょうどまん中のリターンだったときの運用機関が、何年から何年のときだったかを調べます。

a.ちょうどまん中のリターンだったときの運用開始年を調べる

 運用開始年を調べるには、INDEX関数を使います。例では、K39に、「=INDEX(J11:L35,MATCH(J39,J11:J35,0),2)」と入力しています。

index関数でちょうどまん中のリターン時の運用開始年を表示

 この例では、10年運用したときのちょうどまん中のリターン:2.15倍が得られたときの、開始年数:1990年 が表示されました。

ちょうどまん中のリターン時の運用開始年

b.ちょうどまん中のリターンだったときの運用終了年を調べる

 運用終了年を調べる際も、INDEX関数を使います。例では、L39に、「=INDEX(J11:L35,MATCH(J39,J11:J35,0),3) 」と入力しています。

index関数でちょうどまん中のリターン時の運用終了年を表示

 運用開始年を調べたときとほとんど同じ式ですが、式の最後の数値が2→3に変わっていますので注意してください。

 この例では、10年運用したときのちょうどまん中のリターン:2.15倍のリターンが得られたときの、終了年数:1999年が表示されました。

ちょうどまん中のリターン時の運用終了年


補足説明

データの数が偶数の場合は、データの範囲を奇数にする
 ちょうどまん中の運用成績の運用期間は、データの数が奇数でないと探せません(中央値は、データの数が偶数だった場合は、まん中の2つの数値が平均化されてしまうため)。

 データの数が偶数の場合は、データの範囲を1つ減らして、データの数を奇数にしてください。

a.データの数が偶数の場合、下のようにエラーがでて、運用開始年・運用終了年が「#N/A」という表示になってしまいます。

データの数が偶数の場合のエラー表示

b.この場合は、中央値のリターンを計算したセル(例ではJ39)で「F2キー」を押すと表示される範囲を1つすくなくして、データの数を奇数にしてください。

データの数を奇数に変更

c.すると、中央値が平均化されなくなるため、エラーが出なくなります。

エラーが解消

4.3.のデータを、年利に変換する

ア.3.で計算した○倍のデータを、年○%のリターンに変換します。

 変換する式は、○倍の(1/運用年数)乗を計算して、1を引きます。たとえば、10年運用したときの最大リターンを年利に変換しているJ41は、「=J37^(1/10)-1」と入力しています。

○倍を○%のリターンに変換

 10年運用したときは、最大で年17.1%のリターンの期間があったようです。

イ.同じように、最低リターン・中央値リターンも変換します。

最低リターン・中央値リターンの変換

5.20年運用・30年運用の下の欄に、これまでの期間の計算式をコピーする
 20年運用・30年運用でも、これまでの計算が自動でできるように、計算式をコピーします。

期間の計算式をコピー

 なお、10年運用の計算式をコピーしているので、20年運用・30年運用ですこし修正する必要があります。

ア.20年運用での修正
 最大リターン・最低リターン・中央値リターンの関数の範囲を、20年運用の欄にあわせます。

関数の範囲を20年運用のものに修正

 運用開始年・運用開始年のINDEX関数の範囲を、20年運用の欄にあわせます。

a.運用開始年の修正

index関数の範囲を20年運用のものに変更

b.運用終了年の修正

index関数の範囲を20年運用のものに変更

年利の計算式を修正します。
 運用期間が20年なので、計算式の「1/10」の部分を「1/20」に書き換えます。

年利の計算式を修正

イ.30年運用での修正
 最大リターン・最低リターン・中央値リターンの関数の範囲を、30年運用の欄にあわせます。

関数の範囲を30年運用のものに修正

 運用開始年・運用開始年のINDEX関数の範囲を、30年運用の欄にあわせます。

a.運用開始年の修正

index関数の範囲を30年運用のものに変更

b.運用終了年の修正

index関数の範囲を30年運用のものに変更

年利の計算式を修正します。
 運用期間が30年なので、計算式の「1/10」の部分を「1/30」に書き換えます。

年利の計算式を修正


実際の使用例

もっとも収益がわるかった期間に、年に4.5%ずつ取り崩しながら30年運用したらどうなったか?
 これで、さまざまな運用年数や運用期間でのリターンがわかりましたので、自分が知りたい期間でバックテストができるようになりました。

 ここでは、過去34年間でもっとも収益がわるかった期間に、全世界株式で、年に4.5%ずつ取り崩しながら4,000万円を30年運用したときにどうなったかを計算します。

取り崩し条件:投資対象 全世界株式、取り崩し率 4.5%、初期投資額 4,000万円、積立額 0円

1.取り崩し条件にあわせて、「取り崩し率」に4.5%、「積立額」に0と入力する。

取り崩し率と積立額を入力

2.調べたい年数の「年初資産額」に、初期投資額を入力する
 いままでの手順で、過去34年間で、30年運用したときにもっとも収益がわるかったのは、資産が5.95倍にしかならなかった「1990~2019年」の期間だとわかっています。

30年運用時にもっとも収益がわるかった期間

 ですので、1990年の「年初資産額」に4,000と入力します。

1990年の年初資産額に4000を入力

3.運用終了年の「年末資産額」を確認する
 そして、運用終了年の、2019年の「年末資産額」を確認すると、「5,982」となっていることがわかります。

2019年の年末資産額を確認

 この条件の場合、年4.5%取り崩しても、30年運用後には4,000万円→5,982万円に資産が増えていたようです。

4.変更したところを元に戻しておく
 変更したところを元に戻しておかないと、つぎに使うときに正しく計算されないので、元に戻しておきます。

 元に戻す方法は、エクセルの一番上にある「左にまがった矢印」をクリックするか、「Ctrlキーを押しながらZキー」を押します。

元に戻すには左に曲がった矢印をクリック


エクセルファイルをダウンロード

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

・ダウンロードリンク(全世界株式のバックテスト用)
>> toshin_backtest_zensekai_01_01.xlsx

・ダウンロードリンク(米国株式のバックテスト用)
>> toshin_backtest_america_01_01.xlsx