投資家必見!EXCELの便利関数①

投資関数 マネートーク
Pocket

投資家向けエクセル関数

投資をする際は

  • 利回り(運用成績)
  • 金額
  • 期間
  • リスク

を元にシミュレーションを行うかと思います。

この皮算用が一番たのしかったり・・・

 

でも積み立て投資の場合シミュレーションが複雑だったりしませんか?

今回はシミュレーションをサポートする便利な関数をご紹介したいと思います。

 

この記事を読むと

年利と月利の換算方法

投資プラン設計に便利なFV・PMT関数の使用方法

が分かります!

 

エクセルで普段ポートフォリオや資産の管理を行ってる人、ぜひご覧ください。

スポンサーリンク

はじめに 利回りの換算

積み立て投資&複利運用を行う上で、月利を算出することは非常に重要です。

ただ、年利成績は見かけても、月利ってあんまり見かけない気がします。

(私だけ?)

ここでは月利と年利の換算方法をご紹介します。

あとから登場する関数にも必要なので、ぜひマスターしてください。

月利→年利

年利なんて月利を12倍したらいいんじゃないの?

 

釈迦に説法かもしれませんがあのアインシュタインが「人類最大の発明」と評した「複利」

 

これがあるため月利を12倍しても年利にはなりません。

(単利運用の人は12倍でOKですが・・・)

月利から年利を算出するには

年利=(1+月利)12-1

 

月利2%の場合

年利=(1+0.02)12-1

=0.268…

つまり約26.8%です。

 

年利→月利

こっちの方が積み立て投資では使用するかもしれませんね。

月利=(1+年利)0.08333-1

 

年利27%の場合

月利=(1+0.27)0.0833333-1

=0.02011

つまり約2%ですね。

※それぞれどの桁まで計算に入れるかで若干数値はブレます。

 

さて、以後この月利を用いてFV・PMTという関数を紹介していきます。

FV(フューチャーバリュー)

FVとは?

名前の通り将来の価値を計算する関数です。

単純に積み立てを行わない場合の複利計算は簡単で

 

投資元本×(1+利回り)^運用年数

 

でいけます。

 

毎月一定の金額積み立てを続けた場合の投資成果の計算はちょっと面倒です。

 

積み立てなしの場合

 

緑の縦部分が資産の増え方のイメージです。

 

積み立てあり

このように縦方向だけでなく、毎月積み立てる資産も複利で増えるため2次元的な増え方になります。

スポンサーリンク

実際このようにピラミッドのように数値を計算したあと、一番下段の行を合計すれば良いのですが、地味に手間なんです・・・。

FV(フューチャーバリュー)はこれを簡単に計算できる関数です。

今は超便利なサイトがあるので。別にこの関数を覚える必要はないのですが、自分で複数の投資を比較したい際は便利です。

複利計算
元金、利率、期間から複利計算を行います。複利計算とは、元金により生じた利子を次期の元金に組み入れ、元金だけでなく利子にも次期の利子が付く雪だるま式に増えていく計算のことです。株、FX、定期預金、積立など投資や資産運用のシミュレーションとしてご活用下さい。

 

そんなに複雑な式でもないのでぜひ習得しましょう。

FVの使用方法

FVで資産をシミュレーションする上で必要となるのは・・・

毎月積立の場合

  • 月利
  • 期間(月数)
  • 定期積立額
  • 初期積立額
  • 月のうち月初or月末どちらに積立するか

以上です。

=FV(月利,期間,定期積立額,初期積立額,積立日)

という式です。

正直見てもピンとこないと思うので、具体例に当てはめてみようと思います。

 

楽天証券のeMAXIS Slim 先進国株式インデックスに毎月20,000円の積立投資を始めたとします。初期費用は0円毎月月初に購入すると設定しました。

仮に月利0.1%が継続した時に20年後の資産が何円になっているのでしょうか?

 

アンダーバーの部分が計算に必要な条件になります。

  • 月利 0.1%
  • 期間(月数)20*12=240か月
  • 定期積立額 20000円(式では頭にマイナスをつけてください。)
  • 初期積立額0円
  • 積立日 月初(FVでは0が月末,1が月初になります。)

 

 =FV(0.001,240,-20000,0,1)

 =5,427,356 円

つまり、20年後に5,427,356円になるという計算です。

もちろんこれは運用が毎月0.1%から少しもぶれなかったという前提なので100%ありえませんが。

 

例2

楽天証券のeMAXIS Slim 先進国株式インデックスに毎月20,000円の積立投資を始めたとします。

初期費用は50,000円毎月月初に購入すると設定しました。

仮に年利5%が継続した時に10年後の資産が何円になっているのでしょうか?

さて、少し応用編です。

まず、冒頭で登場した年利から月利を算出しましょう。

月利=(1+年利)0.08333-1

=(1+0.05) 0.08333-1

=0.407%

 

では必要情報を整理しますと

  • 月利 0.407%
  • 期間(月数)10*12=120か月
  • 定期積立額 20000円
  • 初期積立額 50000円
  • 積立日 月初

 

 =FV(0.00407,120,-20000,-50000,1)

 =3,180,414 円

 

となります。

PMT

PMTとは?

PMTはPayment関数とも言われ、将来欲しい金額が決まっているときに、自分は毎月どれだけ積み立てを行っていけばいいかを算出する関数です。

例えば○○歳で○○〇〇万円貯めて、セミリタイヤして不労所得だけで生活したい!

など野心家タイプの人はこちらの関数の方が使用するかもしれませんね。

PMTの使い方

要領はFV関数と同じです。

必要な情報

毎月積立の場合

  • 月利
  • 期間(月数)
  • 初期積立額
  • 目標金額
  • 月のうち月初or月末どちらに積立する

=PMT(月利,期間,初期積立額,目標金額,積立日)

 

となります。

「毎月の積立額」と「目標金額」が入れ替わっただけです。

(数値を打ち込む部分は違いますが。)

スポンサーリンク

例3

楽天証券のeMAXIS Slim 先進国株式インデックスに毎月積立投資を始めようと思います。初期費用は0円毎月月初に購入すると設定しました。

仮に月利0.1%が継続した時に20年後1000万円欲しい場合毎月何万円積立れば良いでしょうか。

 

  • 月利 0.1%
  • 期間(月数)10*12=240か月
  • 目標金額 10,000,000円
  • 初期積立額 0円
  • 積立日 月初

 =PMT(0.001,240,0,-10000000,1)

 =36,850 円

となります。

計算の際の注意点

  • 20%の場合は0.2と入力する
  • 積立額の頭にマイナスをつける
  • 月初積立と月末積立を間違えると1か月分利率が変わる

 

この辺りは注意しましょう。

資産皮算用タイム

積み立てNISA

積み立てNISAフル活用の資産をシミュレーションしてみましょう。

積み立てNISAのお話はぷくろーさん(@pukuro7)の記事がわかりやすいのでぜひご覧ください。

「NISA」と「つみたてNISA」どっちがいい?仕組みと収益の違いを徹底比較!
「NISA」と「つみたてNISA」の違いが気になっている方やどちらを選べばよいか迷っている方向けに、NISAとつみたてNISAの違いと収益シミュレーションを解説した上で、パターン別でどちらを選択すべきかまとめています。

積み立てNISAの上限は年間40万円ですので、月33,330円積み立てるとします。

積み立て期間は非課税期間と合わせて20年間

年利4%で成長してくれると願望を込めて高めに設定した場合・・・

  • 月利 0.3274%
  • 期間(月数)20*12=240か月
  • 定期積立額 33,330円
  • 初期積立額 0円
  • 積立日 月初

 

=FV(0.003274,240,-33330,0,1)

=12,166,974 円

 

20年後には1200万円となります。

夢が膨らむね!

億万長者セミリタイヤ

  • 俺は豪遊したい!!
  • 20年後に1億円ほしいんだ!!
  • 投資も年利8%くらいいけるはず!!

なんて人。

  • 月利 0.6434%
  • 期間(月数)20*12=240か月
  • 目標金額 100,000,000円
  • 初期積立額 0円
  • 積立日 月初

 

=PMT(0.006434,240,0,-100000000,1)

=174,624 円

毎月17万円以上の積み立てが必要です!

 

不可能ではないですけど

根性はいりますね。

 

さあ、みなさん自身のPFが将来どのような運用成績なのかを踏まえてシミュレーションしてみましょう!

投資とエクセルのまとめ

月利→年利

 年利=(1+月利)12-1

年利→月利

 月利=(1+年利)0.08333-1

FV(未来の資産額を算出)

 =FV(月利,期間,定期積立額,初期積立額,積立日)

PMT(未来の資産額到達のために必要な積立額を算出)

 =PMT(月利,期間,初期積立額,目標金額,積立日)

コメント

タイトルとURLをコピーしました