資産運用に役立つ計算をしよう(3) ~毎月、異なる金額の投資を行った場合の利率~

スポンサーリンク

スポンサーリンク

シェアする

前回、EXCELのRATE関数を使って、毎月、定額の積立を行った場合の利率、利回りを求める方法をご紹介しました。しかし、実際には、毎月きちっと定額という訳にはなかなかいかないですよね。ボーナスをもらったから増額するとか、場合によっては、一部解約という事もあります。また、積立する日も、毎月同じ日とは限りません。

今回は、そんな時にも対応できる便利なEXCEL(エクセル)のIRR関数とXIRR関数の使い方をご紹介します。

先日の記事、「日経平均を毎月バリュー平均法で積立していたらどうなっていたか、過去のデータで検証しました。」でも、バリュー平均法では毎月の積立額が変わりますので、このIRR関数を使用して利回りを計算しました。

Case-1 毎月、定額を積立てる場合

先ずは、毎月、定額を積立てる場合です。

EXCEL IRR関数 case1

セルB2からB13に毎月の積立額を、セルB14に翌月の資産総額(時価)を入力します。そして、セルB15に”=IRR(B2:B14)”と入力するだけで、月利が計算できます。

このケースでは、毎月1万円ずつ12カ月積立し、その翌月に資産総額が14万円になっている場合、月利2.35%という結果になります。年利になおすには、”=(1+月利)12-1″で32.14%となります。

注意点として、

  • 一定の間隔毎に入力。この場合は1カ月ごと。
  • 日付が古い順に入力。
  • 投資した場合はマイナスをつける。
  • 資産総額はプラス。
  • 計算結果がエラーになったり、おかしな値になった時はIRR(B2:B14,xxx)と”xxx”に、だいたいの推測される利率を入力。例えば、-10%ぐらいだとIRR(B2:B14,-0.1)
    この”xxx”を省略した場合は”0.1″(10%)がデフォルトになります。
    尚、前回の日経平均、バリュー平均法での積立の場合、デフォルトではエラーが多発したのですが、”-0.1″を入力する事で全条件計算できました。

IRR関数で使用するのはB列だけで、積立した日付を入力したA列は使用していません。IRR関数では、一定の間隔毎に入力したデータ(積立額、資産額)に対して、その間隔に対する利率を計算してくれます。この場合は、1カ月ごとに入力したので、IRRで出てくる結果は月利となりますが、例えば、1年ごとに入力すれば、年利が計算されます。

尚、このように定額の積立であればEXCELのRATE関数でも計算できますね。
=RATE(12, -10000, 0, 140000, 1) = 2.35%
と同じ結果になります。ただし、RATE関数の最後は”1″として下さい。IRR関数では、期首(その月の1日)に積立てたとして計算します。

Case-2 毎月の積立額が異なる場合

ここからが本番です。

毎月の積立額が異なる場合や、途中で売却した場合もIRR関数で計算できます。

EXCEL IRR関数 case2

上の例のように、毎月の投資額が異なり、投資しない月や、売却した月もあります。これでも、ちゃんと利率は計算されます。

注意点として、Case-1での注意点に加え、

  • 売却額はプラス
  • 投資しなかった月は、空白ではなく”0″を入力。もし空白にすると、11カ月分として計算されます。

Case-3 年金積立、受取の利率

良く、年金保険などで毎年(毎月)○円拠出すると、毎年(月) △円の年金が受取れます、というのがありますよね。この保険って、本当にお得なの、実際の利率、利回りは何%で運用しているの、と思う事はありませんか?

こういう場合でもIRR関数が使えます。

EXCEL IRR関数 case3

上の例は、毎年10万円、15年間積立てたら、以後、5年間にわたり毎年35万円、年金が受取れますというケースです。積立(支払い)はマイナス、受取はプラスで入力します。

この場合は、1年ごとに入力しましたので、IRRで出てくる結果は年利になります。

この結果を、前の記事でご紹介したFV関数PMT関数を使って検証してみましょう。

先ず、1.53%の年利で、15年間、10万円積立てた場合の総資産額は

FV(1.53/100, 15, -100000, 0, 1)=1,697,919円となります。

これを、1.53%の利率で運用しつつ、5年間にわたって受取る場合の年金額は、

PMT(1.53/100, 5, -1697919, 0, 1) = 350,000円

表の結果に一致しました。

Case-4  XIRR関数 定額、定期積立の場合

XIRR関数を使えば、毎月の積立額や積立日が異なっていても、利率を計算する事ができます。

先ずは、Case-1と同じく、毎月1万円を1年間積立し、その資産が2016年1月1日に14万円になった場合です。

EXCEL XIRR関数 case4

XIRR関数では、IRR関数と異なり、A列の日付も使用しますので、ちゃんと入力して下さい。

XIRR(値、日付、[推定値])となります。この場合はXIRR(B列、A列)です。

推定値は、Case-1で説明したIRR関数と同じです、省略すると”0.1″(10%)となります。

IRR関数と違う点は、上表のように月毎に入力しても、出てくる結果は必ず年利となります。

ところで、Case-1と同じ条件にも関わらず、出てきた結果がちょっとだけ違います。Case-1のIRR関数では月利2.35%でしたが、今回のXIRR関数では2.34%となっています。

大きな違いではありませんが、しんたろうが検証した結果では、IRR関数は、単に12回積立し、その1回あたりの利率を計算しているのに対し、XIRR関数は、ちゃんと1月の日数まで考慮しているようです。例えば、1月1日から2月1日は31日、2月1日から3月1日までは28日として計算しています。これにより、IRR, XIRR関数の結果に若干の違いが生じます。

Case-5 不定期に金額の異なる積立を行った場合

実際の資産運用では、積立日も、積立額も異なる事が多いかと思います。こういう場合に、XIRR関数を使う事が出来ます。

Case-4と同じく、2015年1月1日から積立を開始、その資産が2016年1月1日に14万円になった場合です。ただし、積立日は不定期、積立額も、その時により金額が異なります。

EXCEL XIRR関数 case5

上の表のように、積立日や積立額が、あるいは両方が異なったりしていますが、XIRR関数を使って年利が計算できます。

基本的な使い方、注意点はIRR関数と同じです。

ただし、積立しなかった月は、IRR関数では必ず”0″を入力する必要がありますが、XIRR関数では、空白でも、またはその行自体が無くても構いません。

尚、このケースでは、XIRR(値、日付、[推定値])の”推定値”を省略すると、利率0%という結果になりました。(#VALUE!や#NUM!などのエラーでは無く、0%という結果がちゃんと出力されます。原因はわかりませんが。。。)

そこで、推定値に”-0.1″を入力してみると、正確な値が出力されました。

最後に

以上、IRR関数、XIRR関数についてご説明しました。

投資信託などの運用成績を出すときに重宝する関数です。是非、ご自分で試してみて下さい。

尚、本計算は全て、EXCEL2016で行いました。

本シリーズは3回に分けて記事にしてあります。

第1回資産運用に役立つ計算をしよう(1) ~いくら積立てたら良いの?~

第2回資産運用に役立つ計算をしよう(2) ~いくら受取れる?~

第3回(本記事)資産運用に役立つ計算をしよう(3) ~毎月、異なる金額の投資を行った場合の利率~

スポンサーリンク
アドセンス336
アドセンス336

応援お願いします。
にほんブログ村 その他生活ブログ 資産運用へ にほんブログ村 株ブログ 投資信託へ にほんブログ村 その他生活ブログ 家計管理・貯蓄へ

シェアする

フォローする