目標額と年数から「必要な利回り」をエクセルで求める計算

結構あるので、複利計算の改めて復習です。

 

ウォーミング・アップ:資産が毎年7%で増えるとすると10年後に幾らになっているか?(複利計算)

これは電卓でも直ぐできますし、Excelだと配列に記載して計算することも多いですね。

べき乗の式なので数式は下記になります。

 

 

ExcelやSpreadsheetは「べき乗は ”^”(ハット)もしくは(キャレット)」を使います。Excelでは、

 

=100*1.07^10

 

と書きます。

計算すると、10年後には約1.97倍の196万7151円になっています。理解しやすいですね。

 

 

悩んだことがあるのが、現在~目標金額から、必要な利回りを求める場合です。

 

例題2) 現在30歳で500万円あるが60歳で2000万円にするのに必要な平均年利回りはいくらでしょうか?

 

この場合、少し単純にして、「30年(60歳-30歳)で、資産を4倍(2000万 / 500万)」と考えると、

 

 

となります。懐かしい数Ⅱですが、両辺を30乗根すれば、

 

 

となります。30年後からリターンを毎年の必要利回りをべき乗根していくイメージですね。単純にExcel上だと下記表現になります。

 

=4^(1/30) 

 

結果は1.04729で、必要なリターンは4.729%となります。

 

たまに使うケースでの例題です。

 

例題3)日経の10年(2008~2018年)の平均年リターン(配当抜き)はいくつか?

日経平均は、

2008年の終値 8,859.56円

2018年の終値 20,014.77円

です。この10年間の平均リターンはいくつでしょうか?

 

Excelでの式は

= (20014.77/8859.56)^(1/10) 

 

で、1.084910332となり、平均のリターンは8.49%になります。

 

例題4)  VISAは5年前の10月17日に株価は51.25$でした。現在は177.06ドルです。この5年の配当を除いた年平均上昇率はいくつでしょうか?

 

同じ様に

=(177.08/51.25)^(1/5)

で、1.281で、平均28.1%のリターン(配当含まず)と出てきました。スゴイですね。

 

慣れれば、サッとでるのですが、始め時間がかかったので記事にしておきました。

 

ついでですが、「利回り」と「目標リターン」から、必要な年数を求めるを計算してみたいと思います。

例題5)Microsoftの株価を300ドルで購入しました。1000ドルになったら売りたいと思います。Microsoftの年間成長率を15%とし、PERが一定の場合には何年で目標株価に達するでしょうか?

 

こちらですが、1.15倍でn年かけたら3倍になるので、式は下記になります。

1.15^n = ( 10 ÷ 3 ) 倍

n = Log1.15^(10/3 ) 

n = Log ( 10/3 )  / Log1.15 

で計算すると8.61となります。