資産運用を行うにあたり、そのパフォーマンスを評価し、ご自身が把握しておくことが重要です。
そこで、本記事ではEXCELのIRR関数、XIRR関数を使って収益率(年利回り)を計算する方法をご紹介します。
単純に毎月定額を積立てた場合から、月により積立額を変更した場合、さらに、途中一部を売却したケースの計算方法もご紹介します。
投資のパフォーマンス評価には金額加重収益率、時間加重収益率など複数の方法があります。ここではEXCELで簡単に計算でき、かつ、資金の出入り(キャッシュフロー)が大きい個人の資産運用評価に適していると思われる金額加重収益率(内部収益率)について解説します。
スポンサーリンク
見出し
Case-1 毎月、定額を積立てる場合の利回り ~EXCEL IRR関数、RATE関数~
先ずは、最も単純な毎月、定額を積立てる場合です。
セル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年ごとに入力すれば、年利が計算されます。
RATE関数でも計算できます。
尚、このように定額の積立であればEXCELのRATE関数でも計算できます。
=RATE(12, -10000, 0, 140000, 1) = 2.35% (月利)
と同じ結果になります。
IRR関数では期首(その月の1日)に積立てたとして計算しますので、この結果と合わせるにはRATE関数の最後を"1"として下さい。
Case-2 毎月の積立額が異なる、途中で一部を売却する場合の利回り ~EXCEL IRR関数~
ここからが本番です。
毎月の積立額が異なる場合や、途中で売却した場合もIRR関数で計算できます。
上の例のように、毎月の投資額が異なり、投資しない月や、売却した月もありますが、このようなケースでもIRR関数で利率を計算出来ます。
注意点として、Case-1での注意点に加え、
- 売却額はプラス
- 投資しなかった月は、空白ではなく"0"を入力。もし空白にすると、11カ月分として計算されます。
Case-3 年金積立、受取の利率・利回り
良く年金保険などで「毎年(毎月)○円拠出すると、毎年(月) △円の年金が受取れます」というのがあります。
この保険って、本当にお得なの、実際の利率、利回りは何%で運用しているの、と思う事はありませんか？
こういう場合でもIRR関数が使えます。
上の例は、毎年10万円、15年間積立てたら、以後、5年間にわたり毎年35万円、年金が受取れるというケースです。
積立(支払い)はマイナス、受取はプラスで入力します。
この場合は、1年ごとに入力しましたのでIRRで出てくる結果は年利になります。
EXCEL FV関数、PMT関数で検証
上記結果を、下記ページででご紹介した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 定額、定期積立の場合 ~EXCEL XIRR関数~
XIRR関数を使えば、毎月の積立額や積立日が異なっていても、年利まわりを計算する事ができます。
先ずは、XIRR関数を理解するために、Case-1と同じく、毎月1万円を1年間積立し、その資産が2016年1月1日に14万円になった場合で解説します。
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 不定期に金額の異なる積立を行った場合 ~EXCEL XIRR関数~
ここからがXIRR関数の本領発揮です。
実際の資産運用では、積立日も、積立額も異なる事が多いかと思いますが、こういうケースでもXIRR関数は使えます。
Case-4と同じく、2015年1月1日から積立を開始、その資産が2016年1月1日に14万円になった場合です。ただし、積立日は不定期、積立額も、その時により金額が異なります。
上表のように、積立日や積立額のどちらか、あるいは両方が異なっていますが、XIRR関数を使うと年利が計算できます。
基本的な使い方、注意点はIRR関数と同じです。
ただし、積立しなかった月もIRR関数では必ず"0"を入力する必要がありますが、XIRR関数では空白でも、またはその行自体が無くても構いません。
尚、このケースでは、XIRR(値、日付、[推定値])の"推定値"を省略すると、利率0%という結果になりました。(#VALUE!や#NUM!などのエラーでは無く、0%という結果がちゃんと出力されます。原因はわかりませんが。。。)
そこで、推定値に"-0.1"を入力してみると正確な値が出力されました。
新規口座開設者限定のスタートアップ円定期預金、3カ月 年1.2%(税引前)、1年 年0.65%(税引前)と好金利。
さらに新規口座開設で1,500円、各種条件を満たすと最大31,000円がもらえます(要エントリー)。
東京スター銀行 新規口座開設優遇プラン スターワン円定期預金
インターネット限定新規口座開設者優遇プラン スターワン円定期預金、1年 年0.60%(税引前)と好金利。
公式サイト東京スター銀行最後に
以上、IRR関数、XIRR関数を用い、EXCELで簡単に資産運用のパフォーマンスを計算する方法についてご説明しました。
投資信託などの運用成績を出すときに重宝する関数です。是非、ご自分で試してみて下さい。
尚、本計算は全てEXCEL2016で行い検証しています。