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

　こんにちは。

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

返済条件

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

借入金：10万円

返済期間：5期

1期の利率：20%

支払方法：期末返済（その期分の利息が付いた後に返済する）

期毎の返済額

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

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

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

= -33438

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

返済計画

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

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

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

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

期毎の返済額の内訳

　期毎の返済額のうち元金分は、エクセルの 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

です。

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

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

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

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

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

　指定した期間の返済額のうち元金分は、エクセルの 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

です。

まとめ

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

住宅ローンの返済方法

　こんにちは。

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

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

元利均等返済

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

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

元金均等返済

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

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

まとめ

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

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

　こんにちは。

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

72の法則

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

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

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

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

　「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関数についてまとめました。

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

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

　こんにちは。

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

　ですが、定期預金やローンなどは、期間にかかわらず、利率は年利で示されることが多いようです。例えば、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ヶ月運用

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

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

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

まとめ

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

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

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

　こんにちは。

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

試算条件

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

日本株式

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

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

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

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

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

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

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

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

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

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

先進国株式

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

新興国株式

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

日本REIT

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

先進国REIT

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

日本債券

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

先進国債券

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

新興国債権

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

まとめ

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

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

2021年4月基準価額振返り

　こんにちは。

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

連動する指数

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

• 日本株式：TOPIX
• 先進国株式：MSCIコクサイ
• 新興国株式：MSCIエマージング
• 日本REIT：東証REIT
• 先進国REIT：S&P先進国REIT
• 日本債券：NOMURA-BPI総合
• 先進国債券：FTSE世界国債
• 新興国債権：JPモルガンGBI-EM

4月の基準価額の動き

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

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

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

　次に、REITです。

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

　最後に、債券です。

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

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

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

　まずは、株式です。

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

　次にREITです。

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

　最後に、債券です。

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

まとめ

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

エクセルの財務関数－リボルビング払い

　こんにちは。

　前回は、住宅ローンの返済計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめました。今回は、リボルビング払いを例にして、エクセルの財務関数の具体的な使い方をまとめます。

期末払いのリボルビング払いの例

条件

　以下の条件のリボルビング払いを例に考えます。

• リボルビング払いの金利手数料 20%
• 支払いを3期行う
• 毎期の支払額は定額方式で10万円
• 最初に、30万円の買い物をする
• 3期支払った後の支払残高は15万4400円
• 支払日は期末（その期分の金利手数料が付いた後に支払う）

将来価値の推移

　支払日が期末の場合と期首の場合の違いを分かりやすくするために、一つの期における金利手数料分と支払分を分けてグラフにしてみます。

エクセルの財務関数の引数

　収支は、手持ちのお金が減るとマイナス、増えるとプラスと考えます。

　リボルビング払いで購入するとクレジットカード会社が支払いを行うので、クレジットカード会社からお金を借りて購入した形となり、手持ちのすぐに使えるお金が減ることはありません。したがって、リボルビング払いによる購入はプラスと考えます。

　クレジットカード会社に支払を行うと、手持ちのすぐに使えるお金が減るので、クレジットカード会社への支払（返済）はマイナスと考えます。

　したがって、今回の条件をエクセルの財務関数を使う場合に当てはめると以下の表になります。

エクセルの財務関数の計算式

　エクセルの財務関数を使って、今回の条件を計算してみます。

金利手数料

 リボルビング払いで30万円の品を購入し、期末払いで3期間10万円ずつ支払いを行って、支払残高が15万4400円になる場合の金利手数料はどれだけか？

　RATE = RATE(nper, pmt, pv, fv, type, guess)

　利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)

なので

　RATE(3,-100000,300000,-154400,0) = 20%

回数

　金利手数料20%のリボルビング払いで30万円の品を購入し、期末払いで10万円ずつ支払いを行って、支払残高が15万4400円になる場合の支払回数は何回か？

　Number of PERiods = NPER(rate,pmt,pv,fv,type)

　期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)

なので

　NPER(20%,-100000,300000,-154400,0) = 3

支払額

　金利手数料20%のリボルビング払いで30万円の品を購入し、期末払いで3期間支払いを行って、支払残高が15万4400円になる場合の毎月の支払額はいくらか？

　PayMenTs = PMT(rate, nper, pv, fv, type)

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

なので

　PMT(20%,3,300000,-154400,0)  = -100,000

最初の購入額

　金利手数料20%のリボルビング払いで、期末払いで3期間10万円ずつ支払いを行って、支払残高が15万4400円になる場合、いくらの品を購入できるか？

　Present Value = PV(rate, nper, pmt, fv, type)

　現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)

なので

　PV(20%,3,-100000,-154400,0) = 300,000

支払残高

　金利手数料20%のリボルビング払いで30万円の品を購入し、期末払いで3期間10万円ずつ支払いを行うと、支払残高はいくらになるか？

　Future Value = FV(rate,nper,pmt,pv,type)

　将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)

なので

　FV(20%,3,-100000,300000,0) = -154,400

期首払いのリボルビング払いの例

条件

　以下の条件のリボルビング払いを例に考えます。

• リボルビング払いの金利手数料 20%
• 支払いを3期行う
• 毎期の支払額は定額方式で10万円
• 最初に、30万円の買い物をする
• 3期支払った後の支払残高は8万1600円
• 支払日は期首（その期分を支払った後に金利手数料が付く）

将来価値の推移

　支払日が期末の場合と期首の場合の違いを分かりやすくするために、一つの期における金利手数料分と支払分を分けてグラフにしてみます。

エクセルの財務関数の引数

　今回の条件をエクセルの財務関数を使う場合に当てはめると下の表になります。

エクセルの財務関数の計算式

　エクセルの財務関数を使って、今回の条件を計算してみます。

金利手数料

 リボルビング払いで30万円の品を購入し、期首払いで3期間10万円ずつ支払いを行って、支払残高が8万1600円になる場合の金利手数料はどれだけか？

　RATE = RATE(nper, pmt, pv, fv, type, guess)

　利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)

なので

　RATE(3,-100000,300000,-81600,1) = 20%

回数

　金利手数料20%のリボルビング払いで30万円の品を購入し、期首払いで10万円ずつ支払いを行って、支払残高が8万1600円になる場合の支払回数は何回か？

　Number of PERiods = NPER(rate,pmt,pv,fv,type)

　期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)

なので

　NPER(20%,-100000,300000,-81600,1) = 3

支払額

　金利手数料20%のリボルビング払いで30万円の品を購入し、期首払いで3期間支払いを行って、支払残高が8万1600円になる場合の毎月の支払額はいくらか？

　PayMenTs = PMT(rate, nper, pv, fv, type)

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

なので

　PMT(20%,3,300000,-81600,1)  = -100,000

最初の購入額

　金利手数料20%のリボルビング払いで購入し、期首払いで3期間10万円ずつ支払いを行って、支払残高が8万1600円になる場合、いくらの品を購入できるか？

　Present Value = PV(rate, nper, pmt, fv, type)

　現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)

なので

　PV(20%,3,-100000,-81600,1) = 300,000

支払い残高

　金利手数料20%のリボルビング払いで30万円の品を購入し、期首払いで3期間10万円ずつ支払いを行うと、支払残高はいくらになるか？

　Future Value = FV(rate,nper,pmt,pv,type)

　将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)

なので

　FV(20%,3,-100000,300000,1) = -81,600

　今回は、リボルビング払いを例にして、エクセルの財務関数の具体的な使い方をまとめました。