ゆるく気長に投資生活

投資信託をコアに、ゆるく気長に資産形成を目指すブログ

エクセル財務関数の使い方 - 元利均等返済期首支払い

 こんにちは。

 前回、「元利均等返済」の「期末支払い」を例に、エクセル財務関数の使い方をまとめました。今回は、「元利均等返済」の「期首支払い」を例に、エクセル財務関数の使い方をまとめます。

 

返済条件

 以下の条件で返済するものとします。この条件は、支払方法が「期首」か「期末」かが違うことを除いて、前回の条件と全く同じです。

借入金:10万円

返済期間:5期

1期の利率:20%

支払方法:期首返済(その期分の返済を行った後に利息が付く

 

期毎の返済額

 期毎の返済額は、エクセルの PMT関数で求めることができます。PMTは、PayMenT(支払金額)です。

  PMT(利率,期間,現在価値,将来価値,支払期日)

= PMT(20%,5,100000,0,1)

= -27865

 PMTで求める支払額は、手持ちのお金が増える場合はプラス、手持ちのお金が減る場合はマイナスで表します。ローンを返済すると手持ちのお金が減るので、返済額はマイナスになります。

 

返済計画

 今回の条件での返済計画を表で示します。

f:id:iGoMtwalk:20210511022120p:plain

 元利均等返済なので、毎期の返済額が同じ額になっています。

 今回の条件での返済計画を、グラフで示します。

f:id:iGoMtwalk:20210511022134p:plain

 期首支払いなので、まず返済を行って、その後に利息が付きます

 

期毎の返済額の内訳

 期毎の返済額のうち元金分は、エクセルの PPMT関数で求めることができます。PPMTは、Principal PayMenT(元金支払額)です。

 例えば、第2期の支払額は

  PPMT(利率,期,期間,現在価値,将来価値,支払期日)

 = PPMT(20%,2,5,100000,0,1)

 = -13438

です。

 期毎の返済額のうち利息分は、エクセルの IPMT関数で求めることができます。IPMTは、Interest PayMenT(利息支払い額)です。

 例えば、第4期の支払額は

  IPMT(利率,期,期間,現在価値,将来価値,支払期日)

 = PPMT(20%,4,5,100000,0,1)

 = -8514

です。

 返済額の元金分と利息分を表で示します。

f:id:iGoMtwalk:20210511022154p:plain

 返済額の元金分と利息分をグラフで示します。

f:id:iGoMtwalk:20210511022208p:plain

 元利均等返済なので、元金分と利息分を合わせた返済額は、毎期、同じ額になっています。返済期間の始めのうちは利息分の割合が大きく、返済が進むにつれて元金分の割合が増えていきます。

 また、期首返済なので、利息が付く前に返済を行います。そのため、第1期はまだ利息が全く付いていない状態で返済を行うことになるので、第1期の返済額は丸ごと元金分の返済になります。

 

指定した期間の返済額の内訳

 指定した期間の返済額の内訳を求める関数も用意されています。

 指定した期間の返済額のうち元金分は、エクセルの CUMPRINC関数で求めることができます。CUMPRINCは、CUMulative PRINCipal(累計元金(支払額))です。

 例えば、第1期から第3期までの支払額は

  CUMPRINC(利率,期間,現在価値,開始期,終了期,支払期日)

 = CUMPRINC(20%,5,100000,1,3,1)

 = -57429

です。

 指定した期間の返済額のうち利息分は、エクセルの CUMIPMT関数で求めることができます。CUMIPMTは、CUMulative Interest PayMenT(累計利息支払額)です。

 例えば、第3期から第5期までの支払額は

  CUMIPMT(利率,期間,現在価値,開始期,終了期,支払期日)

 = CUMIPMT(20%,5,100000,3,5,1)

 = -24898

です。

 

まとめ

 今回は、「元利均等返済」の「期首支払い」を例に、エクセル財務関数の使い方をまとめました。次回は、「元金均等返済」の「期末支払い」を例に、エクセル財務関数の使い方をまとめます。

エクセル財務関数の使い方 - 元利均等返済期末支払い

 こんにちは。

 前回、ローン返済における「元利均等返済」と「元金均等返済」の特徴をまとめました。今回は、もっとも一般的な「元利均等返済」の「期末支払い」を例に、エクセル財務関数の使い方をまとめます。

 

返済条件

 以下の条件で返済するものとします。

借入金:10万円

返済期間:5期

1期の利率:20%

支払方法:期末返済(その期分の利息が付いた後に返済する)

 

期毎の返済額

 期毎の返済額は、エクセルの PMT関数で求めることができます。PMTは、PayMenT(支払金額)です。

  PMT(利率,期間,現在価値,将来価値,支払期日)

= PMT(20%,5,100000,0,0)

= -33438

 PMTで求める支払額は、手持ちのお金が増える場合はプラス、手持ちのお金が減る場合はマイナスで表します。ローンを返済すると手持ちのお金が減るので、返済額はマイナスになります。

 

返済計画

 今回の条件での返済計画を表で示します。

f:id:iGoMtwalk:20210510081433p:plain

 元利均等返済なので、毎期の返済額が同じ額になっています。

 今回の条件での返済計画を、グラフで示します。

f:id:iGoMtwalk:20210510081445p:plain

 期末支払いなので、まず利息が付いて、その後に返済します。

 

期毎の返済額の内訳

 期毎の返済額のうち元金分は、エクセルの PPMT関数で求めることができます。PPMTは、Principal PayMenT(元金支払額)です。

 例えば、第2期の支払額は

  PPMT(利率,期,期間,現在価値,将来価値,支払期日)

 = PPMT(20%,2,5,100000,0,0)

 = -16126

です。

 期毎の返済額のうち利息分は、エクセルの IPMT関数で求めることができます。IPMTは、Interest PayMenT(利息支払い額)です。

 例えば、第4期の支払額は

  IPMT(利率,期,期間,現在価値,将来価値,支払期日)

 = PPMT(20%,4,5,100000,0,0)

 = -10217

です。

 返済額の元金分と利息分を表で示します。

f:id:iGoMtwalk:20210510081502p:plain

 返済額の元金分と利息分をグラフで示します。

f:id:iGoMtwalk:20210510081520p:plain

 元利均等返済なので、元金分と利息分を合わせた返済額は、毎期、同じ額になっています。返済期間の始めのうちは利息分の割合が大きく、返済が進むにつれて元金分の割合が増えていきます。

 

指定した期間の返済額の内訳

 指定した期間の返済額の内訳を求める関数も用意されています。

 指定した期間の返済額のうち元金分は、エクセルの CUMPRINC関数で求めることができます。CUMPRINCは、CUMulative PRINCipal(累計元金(支払額))です。

 例えば、第1期から第3期までの支払額は

  CUMPRINC(利率,期間,現在価値,開始期,終了期,支払期日)

 = CUMPRINC(20%,5,100000,1,3,0)

 = -48914

です。

 指定した期間の返済額のうち利息分は、エクセルの CUMIPMT関数で求めることができます。CUMIPMTは、CUMulative Interest PayMenT(累計利息支払額)です。

 例えば、第3期から第5期までの支払額は

  CUMIPMT(利率,期間,現在価値,開始期,終了期,支払期日)

 = CUMIPMT(20%,5,100000,3,5,0)

 = -29877

です。

 

まとめ

 今回は、「元利均等返済」の「期末支払い」を例に、エクセル財務関数の使い方をまとめました。次回は、「元利均等返済」の「期首支払い」を例に、エクセル財務関数の使い方をまとめます。

住宅ローンの返済方法

 こんにちは。

 以前、住宅ローンの返済を例に、エクセルの財務関数の使い方をまとめました。その際は、毎回一定額を返済する「元利均等返済」の例でした。 ローンを返済する方法には、毎回一定額を返済する「元利均等返済」だけではなく、毎回元金を一定額返済する「元金均等返済」もあります。

 今回は、ローンの「元利均等返済」と「元金均等返済」の特徴についてまとめます。

 

元利均等返済

 毎回、元金と利息を合わせたトータルの返済額が一定(均等)となるように返済する方法です。

f:id:iGoMtwalk:20210509091447p:plain

 「元利均等返済」の場合、返済期間の始めのうちは利息の割合が多くなります。返済が進むにつれて、元金の割合が増えていきます。

 

元金均等返済

 毎回の返済額のうち、元金の返済額が一定(均等)となるように返済する方法です。

f:id:iGoMtwalk:20210509091523p:plain

 「元金均等返済」の場合は、返済期間の始めのうちは返済額が大きくなります。返済が進むにつれて、返済額が減っていきます。

 

まとめ

 今回は、ローン返済における「元利均等返済」と「元金均等返済」の特徴についてまとめました。次回から、返済方法の詳細と、返済額を求めるエクセルの関数についてまとめていきます。

エクセルの財務関数 - 目標額に達するまでに必要な期間

 こんにちは。

 以前、投資したお金が複利効果で2倍になるまでの期間を簡単に求めることができる「72の法則」についてまとめました。ですが、エクセルの関数を使えば、2倍だけでなく、どのような額になるまでの時間でも簡単に求めることができます。今回は、投資したお金が期待する額になるまでの期間を求めることができる関数についてまとめます。

 

72の法則

 「72の法則」で、投資したお金が2倍になるまでの期間を簡単に計算できます。

 例えば、100万円を年利6% で運用できるとすると、72 ÷ 6 = 12なので、約12年で倍の200万円になります。

f:id:iGoMtwalk:20210506143459p:plain

 逆に、100万円を9年で倍の200万円にするには、72 ÷ 9 = 8なので、年利8%で運用する必要があります。

f:id:iGoMtwalk:20210506143516p:plain

 

投資したお金が目標額になるまで期間

 「72の法則」で投資したお金が2倍になるまでの期間を計算できるのですが、エクセルの PDURATION関数(Period DURATION)を使えば、投資したお金が任意の目標額になるまでの期間を計算することができます。引数は、利率、最初の投資額(元本)、目標額、です。

 例えば、100万円を年利6% で運用できるとすると、200万円になるのは、

  PDURATION(6%,1000000,2000000) = 11.90年

です。

 また、100万円を年利6% で運用できるとすると、150万円になるのは、

  PDURATION(6%,1000000,1500000) = 6.96年

です。

 さらに、PDURATION関数で計算できるのは一括投資の場合だけですが、NPER関数を使えば、つみたて投資の場合や、一括投資とつみたて投資を組み合わせた場合も計算できます。NPER関数の詳細は、エクセルの財務関数の概要や、つみたて投資の計画立案を参照してください。

 

投資したお金を目標額にするための利率

 「72の法則」で投資したお金が2倍になるまでの利率を計算できるのですが、エクセルの RRI関数(Relevant Rate of Interest)で、投資したお金を任意の目標額にするための利率を計算することができます。引数は、期間、最初の投資額(元本)、目標額、です。

 例えば、100万円を9年で200万円にするために必要な利率は、

  RRI(9,1000000,2000000) = 8.01%

です。

 また、100万円を9年で150万円にするために必要な利率は、

  RRI(9,1000000,1500000) = 4.61%

です。

 さらに、RRI関数で計算できるのは一括投資の場合だけですが、RATE関数を使えば、つみたて投資の場合や、一括投資とつみたて投資を組み合わせた場合も計算できます。RATE関数の詳細は、エクセルの財務関数の概要や、つみたて投資の計画立案を参照してください。

 

まとめ

 今回は、一括投資したお金が目標額に達するまでの期間や必要な利率を、「72の法則」よりもフレキシブルな条件で計算できる PDURATION関数と RRI関数についてまとめました。

f:id:iGoMtwalk:20210506143717p:plain

 ですが、エクセルの財務関数の概要つみたて投資の計画立案で紹介した RATE関数や NPER関数を使えば、一括投資だけでなくつみたて投資の場合でも計算できるので、RATE関数や NPER関数を使う方がより良いと思います。

f:id:iGoMtwalk:20210506143730p:plain

 

エクセルの財務関数 - 年利から適用利率を求める方法

 こんにちは。

 以前、エクセルの財務関数についてまとめました。財務関数は、金利が付く周期の利率と回数を用いて計算します。

 ですが、定期預金やローンなどは、期間にかかわらず、利率は年利で示されることが多いようです。例えば、3ヶ月ものや6ヶ月ものの定期預金でも適用金利は年利で示されています。

 今回は、複利が付く期間(周期)が半年や四半期(3ヶ月)、1ヶ月、日割りなどの場合に、年利から適用利率を求める方法についてまとめます。

 

利率を求める2つの方法

① 年利を1年間の支払回数で割る

 マイクロソフトのOfficeサポートページにも記されている方法で、年利を複利が付く期間の回数で割ります。例えば、年利 12% の 4 年ローンを月払いで返済する場合、利率には

  12% ÷ 12(ヶ月) = 1%

を、支払回数には

  12(ヶ月) × 4(年) = 48

を指定します。

② 1年間の支払回数から複利の利率を求める

 エクセルのRRI関数を使います。RRI関数は、引数に、期間と元金、満期の額を指定します。例えば、年利12% の 4年ローンを月払いで返済する場合を考えます。100円を年利12%で運用すると、12ヶ月で100円が112円になるので、月払いの利率は

  RRI(12,100,112) = 0.949%

を指定します。支払回数は

  12(ヶ月) × 4(年) = 48

を指定します。

 

2つの方法の使い分け

 まとまったお金を一括で投資する場合は、RRI関数を使えば正確に計算できます。例えば、100万円を年利6%相当で運用できるとする場合、年利6%なら12ヶ月で100円が106円になるので、1ヶ月の利率として

  RRI(12,100,106) = 0.487%

を指定します。すると、

  FV(0.487%,12,0,-1000000,1) = 1060031

と、1年(12ヶ月)でほぼ106万円になります。誤差があるのは、利率を0.487%に丸めたためで、より正確に0.48675506にすればピッタリ106万円になります。今回の例で、年利を複利が付く期間の回数で割る方法で利率を求めて

  6% ÷ 12(ヶ月) = 0.5%

を指定すると、

  FV(0.5%,12,0,-1000000,1) = 1061678

と、誤差が大きくなります。

 ローンを毎月返済する場合は、どちらの方法を使っても正確に計算することは難しいと思います。毎月返済していく中、どの時点で金利をつけるか、によるためです。

 

具体的な例

 年利を複利が付く期間の回数で割る方法と、RRI関数を使う方法の差を、具体例で見てみます。

年利6%、元金500円、期首に毎月200円つみたて、10ヶ月運用

f:id:iGoMtwalk:20210505085332p:plain

 

年利12%、元金0円、期末に毎月1000円つみたて、12ヶ月運用

f:id:iGoMtwalk:20210505085348p:plain

 

年利11%、元金0円、期首に毎月2000円つみたて、35ヶ月運用

f:id:iGoMtwalk:20210505085404p:plain

 

年利6%、元金1000円、期首に毎月1000円つみたて、12ヶ月運用

f:id:iGoMtwalk:20210505085414p:plain

 

まとめ

 定期預金やローン返済の場合は、銀行が金利の付き方を詳細に決めているため、財務関数を使ったシミュレーションは参考にするだけで、最終的には銀行の規約を確認するしかありません。

 一方、投資信託などにつみたて投資を行う場合は、金利の付き方にルールはなく、長期的に見て結果的にこれくらいの平均利率を期待できると考えるので、却って、財務関数によるシミュレーションが使いやすくなります。投資のアセットアロケーションを考える際は、エクセルの財務関数を活用しようと考えています。

4月振返り - つみたて投資をしていた場合

 こんにちは。

 前回は、4月の基準価額の動きを振り返りました。今回は、今年、つみたて投資を始めた場合を例に、つみたて投資の損益についてまとめます。

 

試算条件

  • 2021年1月1日から、市場開場日は毎日、つみたて投資を行っている。1月1日から4月30日までの市場開場日は、81日でした。
  • 対象は、日本株式、先進国株式、新興国株式、日本REIT、先進国REIT、日本債券、先進国債券、新興国債権、計8種。
  • 1月1日の基準価額を100に正規化
  • 基準価額が100の時に、100口購入できる額分を、(市場開場日は)毎日購入

 

日本株

 日本株式の、基準価額と1日あたりの購入口数を、グラフにします。

f:id:iGoMtwalk:20210503025129p:plain

 左側の軸が基準価額、右側の軸が1日あたりの購入口数です。基準価額が上がるにつれて、1日あたりの購入口数は減少していきます。

 

 購入した口数の累積は、下のグラフになります。

f:id:iGoMtwalk:20210503025152p:plain

 基準価額が100のまま動かなければ、市場開場日は81日だったので、100✕81日=8100口購入になるのですが、基準価額が上昇している分、累積購入口数は減少しています。

 

 毎日の評価額は、下のグラフになります。

f:id:iGoMtwalk:20210503025209p:plain

 評価額 = 基準価額 ✕ 累積購入口数 です。

 

 損益率は、下のグラフです。

f:id:iGoMtwalk:20210503025227p:plain

 損益率 = (評価額 ÷ 購入額) - 1 です。評価額と購入額が等しい場合が0%で、評価額が購入額を上回るとプラス、評価額が購入額を下回るとマイナスです。

 3月中旬まで概ね基準価額が高かったため購入口数が相対的に少なくなっている中、3月下旬から基準価額が下落に転じました。その結果、4月末日の基準価額が1月1日の基準価額より6%上昇しているにもかかわらず、4月末日の損益率は±0%でした。高い基準価額をキープしていた後、最後に基準価額が下がると、少ない購入口数 ✕ (相対的に)低い基準価額となって評価額が下がってしまう、つみたて投資の弱点がもろに出た値動きでした。

 

 以降は、基準価額と損益率のグラフのみ提示します。

 

先進国株式

 先進国株式の基準価額は、下落局面が4回ありましたが、全体的に右肩上がりを続けており、4月末日の損益率は +9% でした。

f:id:iGoMtwalk:20210503025249p:plain

 

新興国株式

 新興国株式の基準価額は、2月中旬まで急速に上がった後、一進一退の状況です。3月下旬には -2% になることもありましたが、4月末日の損益率は +3% でした。

f:id:iGoMtwalk:20210503025306p:plain

 

日本REIT

 日本REITの基準価額は、3月上旬に下落局面がありましたが、全体的に右肩上がりが続いており、4月末日の損益率は +8% でした。

f:id:iGoMtwalk:20210503025321p:plain

 

先進国REIT

 先進国REITは、日本REITよりさらに好調で、4月末日の損益率は +11% でした。

f:id:iGoMtwalk:20210503025336p:plain

 

日本債券

 債券の基準価額は、株式やREITの基準価額と比べて、極めて安定しています。特に、日本債券の基準価額はほとんど変動しておらず、損益率はほぼ ±0% です。

f:id:iGoMtwalk:20210503025352p:plain

 

先進国債

 先進国債券の基準価額は、3月中旬以降若干上がったため、4月末日の損益率は +1% でした。

f:id:iGoMtwalk:20210503025547p:plain

 

新興国債権

 新興国債権の基準価額も、一進一退を繰り返しているのですが、4月下旬が若干上昇傾向となったたため、最終的な損益率は +1% でした。

f:id:iGoMtwalk:20210503025601p:plain

 

まとめ

 4月末日の基準価額と、損益率を表にまとめます。

f:id:iGoMtwalk:20210503025616p:plain

 日本株式は、基準価額が +6.12% 上昇したのに、損益率が -0.06% とマイナスになっています。一方、日本債券は、基準価額が -0.24% と下落したのに、損益率が +0.10% とプラスになっています。このように、基準価額の動きが損益に影響するところが、つみたて投資の面白いところだと思います。

2021年4月基準価額振返り

 こんにちは。

  今回は、4月の、基準価額の動きを振り返ります。

 

連動する指数

 各基準価額が連動する指数は、以下の通りです。

 

4月の基準価額の動き

 2021年4月1日の基準価額を100に正規化しています。

 まずは、株式の値動きです。

f:id:iGoMtwalk:20210502035646p:plain

 3指数とも4月19日の週に急落しましたが、先進国株式と新興国株式は、その後持ち直しています。日本株式は、日本株式は4月全体を通して下落傾向でした。

 

 次に、REITです。

f:id:iGoMtwalk:20210502035704p:plain

 日本REIT、先進国REITとも好調で、右肩上がりが続いています。

 

 最後に、債券です。

f:id:iGoMtwalk:20210502035728p:plain

 先進国債券が若干下がり気味です。一方、日本債券は若干上昇傾向にあります。

 

コロナショックからの基準価額の動き

 コロナショックで大きく下がる直前2020年2月1日の基準価額を100にして、コロナショックからの回復状況を見てみます。

 

 まずは、株式です。

f:id:iGoMtwalk:20210502035759p:plain

 昨年秋にコロナショック以前の額に戻り、その後、順調に右肩上がりを続けています。ただし、日本株式は先月右肩下がりでした。この傾向が今後も続くのか、反発するのか、ゴールデンウィーク明けに注目したいと思います。

 

 次にREITです。

f:id:iGoMtwalk:20210502035823p:plain

 日本REITと先進国REITは似たような値動きになっており、コロナショック以前の額にあと一歩のところまで戻してきました。秋までに、コロナショック以前の額を超えそうです。

 

 最後に、債券です。

f:id:iGoMtwalk:20210502035853p:plain

 日本債券と先進国債券は、期待に違わずコロナの影響はほとんど受けていません。ただし、日本債券は、コロナ以前から右肩下がり傾向が続いています。新興国債権はコロナで2割近く下げたのですが、その後回復傾向にあり、コロナ以前の額に戻すまであと少しです。今年中にコロナ以前の額に戻しそうです。

 

まとめ

 全体的に、好調を維持していると思うのですが、唯一、日本株が3月下旬から下げ基調にあるのが気がかりです。日本でワクチン接種が進むまでは、力強い伸びは難しいかもしれません。

プライバシーポリシー
Google Analytics について
当ブログでは Google Analytics を利用してアクセス解析を行うために
cookie を使用しております。
Google Analytics で集計したデータは、当ブログのアクセス解析や改良、
改善のために使用させていただくものとします。
なお、cookie は個人を特定する情報を含まずに集計しております。
Google によるデータの使用に関しては「 ポリシーと規約」をご覧ください。