銀行借入利息に関するエクセル関数の注意点

銀行から資金を調達するには、返済方法として「元利均等返済方式」と「元金均等返済方式」があります。

これらの返済元金や返済利息を求めるには、エクセル関数を使用すればすぐに求めることができます。

しかし、銀行からの借入パターンは、エクセル関数使用と少しずれている場合もありますので、気を付けるポイントを記述しておきます。

元利均等返済方式の場合

元利均等返済方式は毎回返済する額(返済元金+支払利息)が一定となります。返済する元金は最初の方が少なくなります。

知りたい期の支払利息を求めるには「IPMT」という関数を使います。
→ IPMT関数を使って指定した期の支払利息を計算する

知りたい期の返済元金を求めるには「PPMT」という関数を使います。
→    PPMT関数を使って指定した期の返済元金を計算する

元金均等返済方式の場合

元金均等返済方式は毎回返済する返済元金が一定となります。
返済額(返済元金+支払利息)は一定ではありません。

知りたい期の支払利息を求めるには「ISPMT」という関数を使用します。
→   ISPMTを使って指定した期の支払利息を計算する

知りたい期の返済元金を求めるにはエクセル関数はありませんが、
元金を均等に返済するので借入額を借入期間で割った金額が毎回の返済元金となります。

利息を算出する際の注意点

銀行から資金調達をする場合、
借り方について大きく次の4パターンに分かれます。

これら4パターンにつき、利息計算におけるエクセル関数使用の注意点を記してみます。

利息前払 利息後払
元利均等返済方式
元金均等返済方式

(返済方法は、月末約定弁済(毎月元金を返済していく)とします。)

①.「元利均等方式」ー「利息前払」

元金の返済:毎月返済をし、元金の返済は月末に行う。
利息の支払:利息は前払のため月初に支払う。
利息計算: 月初の借入元金残高×金利

利息計算エクセル関数:IPMT  (支払期日:0期末 )

②.「元利均等方式」ー「利息後払」

元金の返済:毎月返済をし、元金の返済は月末に行う。
利息の支払:利息は後払いのため、月末に支払う。
利息計算:月初の借入金元金残高×金利

利息計算エクセル関数:IPMT (支払期日:0期末

元利均等計算における注意点

利息は前払いであっても、エクセル関数計算において支払期日を期首「1」としないこと。

エクセル関数に入力する「支払期日」は、
元金を返済するタイミングが ”期首” か “期末” かの設定なので、

利息は前払いであっても元金は期末に返済するため期末の「0」を指定する。

③.「元金均等方式」ー「利息前払」

元金の返済:毎月返済をし、元金の返済は月末に行う。
利息の支払:利息は前払のため月初に支払う。
利息計算:月初の借入元金残高×金利

利息計算エクセル関数:ISPMT (期から1を引く

④.「元金均等方式」ー「利息後払」

元金の返済:毎月返済をし、元金の返済は月末に行う。
利息の支払:利息は後払のため月末に支払う。
利息計算:月初の借入元金残高×金利

利息計算エクセル関数: ISPMT (期から1を引く

元金均等計算における注意点

ISPMTは期首に元金を返済するものとして計算するため、元金を期末に返済する場合には求める期より1を引きます。

銀行からの借入の場合は、借入元金の返済は期末になります。
(期首に元金の返済があるとすれば、その分調達額が少なくなってしまう)

利息が前払いでも後払いでも、元金返済は期末(月末)払いのため、エクセル関数の支払期日は期末払いとして計算する必要があります。

銀行からの借入で利息を前払いする事態に納得がいきませんが、仕方がありません。。

エクセルFV関数利用の注意点

当社では、企業型確定拠出年金を行っており、社員が自分で退職金を運用しています。

毎月会社から拠出金が個人の口座(DC口座)へ振り込まれ、60歳まで自分で運用します。

社員には自分で投資シミュレーションができるように教育をしています。

 

積立金運用の計算(FV関数)

教育の中で大部分の者が間違えている箇所がありましたので記述しておきます。

問題1

毎年、拠出金額が会社から個人のDC口座へ50万円振り込まれます。
(実際は毎月振り込まれます)

2018年期末から10年間、金利2%で運用する場合、10年後いくらになっているでしょうか?
会社からの拠出金は年末に入金されるものとします。

 

FV関数は次の通りです。
=FV(利率、期間、定期支払額、現在価値、支払期日)

よって回答は
=FV(2%, 10, −500,000, 0 , 0 )
=5,474,860円となります。

しかし、これでは間違い。

答えは6,084,358円にならなければなりません。

間違いのポイントは「期間」です。

10年間運用するのだから「10」でよい気がしますが、ここは
「運用期間」ではなく「積立回数」を入力する必要があります。

よって
=FV(2%, 11, −500,000, 0 , 0 )
=6,084,358円となります。

 

ここで疑問が出てきます。

積立てを始める際、すでに保有している金額が100万円あった場合、どうすればよいのでしょうか?

FV関数の「現在価値」の箇所に「-1,000000」と入れればよいのでしょうか?

 

一時金運用の計算(FV関数)

検証のため、FV関数を使って一時金運用について見てみます。

 

問題2

2018年期末に100万円投資をします。金利2%で運用する場合、10年後いくらになっているでしょうか?

 

まずエクセルで計算すると次のようになり、1,218,994円となります。

FV関数を使用して計算してみると
=FV(2%, 10, 0, -1000000, 0 )
=1,218,994

FV関数の答えと表の答えが一致しているので、FV関数の使用方法は間違っていないようです。

 

一時金と積立ての両方がある場合の計算(FV関数)

 では、一時金と積立ての両方がある場合はFV関数はどう利用したらよいのでしょうか?

問題3

毎年、拠出金額が会社から個人のDC口座へ50万円振り込まれます。
2018年から10年間、金利2%で運用する場合、10年後いくらになっているでしょうか?
会社からの拠出金は年末に入金されるものとし、開始時において100万円保有しているものとします。

 

問題3は問題1と問題2を合わせたものなので、
答えは7,303,352円となります。

ここで、FV関数を使用するにおいて困ったことが起こります。

=FV(2%,「期間」,-500000, -1000000, 0 )

FV関数はこのように表されますが、

「期間」の箇所が

積立て運用の場合(問題1)は積立回数が「11」回でしたが、
一時金運用の場合(問題2)は運用期間が「10」年です。

期間の数値が異なるので一緒にはFV関数を使用できないようです。

 

一時金運用の場合の運用期間が「11」年であれば積立回数の「11」と同じになりFV関数を利用できそうです。

この場合、期首に積立てをする場合には
積立回数も「11」回となり、一時金運用も「11」年となり、FV関数の「期間」の数値が一致します。

よって、問題3において期首に積立てをする場合には

=FV (金利、期間、積立額、現在価値、支払期日)
=FV (2% , 11,  -500000,  -1000000,  1 )
=7,327,732

となります。
(ただし、現在価値は期首の金額を入力する必要があります)

 

ISPMTを使って指定した期の支払利息を計算する

質問

銀行より3,000万円借入れて20年間元金均等方式で毎月返済していく。第1期目の支払利息はいくらか?

 

借入金の返済方法には、元利均等返済方式と元金均等返済方式があり次のような違いがあります。

元利均等返済方式: 毎月の返済額(元金分+利息分)が一定 
元金均等返済方式:毎月返済する元金部分が一定(利息は変動)

ISPMT関数では、元金均等返済方式についての借入金返済額のうち支払利息部分を求めていきます。
(元利均等返済方式の利息分はIPMT関数を使用します → こちら

 

イメージ図

 

ISPMT関数の使い方

元金均等返済方式による借入れをした場合、ある指定した期の支払利息額を求めるにはエクセル関数のISPMT関数を使用します。

エクセルのセルに、次のように入力して下さい。

=ISPMT(0.015/12, 1, 20*12,  30000000)

それぞれの意味は次の通りです。

ISPMT(利率, , 期間 現在価値)

すると答えは

-37,344

となります。

 

回答

3,000万円を金利1.5%で借り入れして、20年間元金均等方式で毎月返済していく場合、第1回目の返済額のうちの支払利息額は37,344円となる。

 

それでは、それぞれの項目について説明していきます。

=ISPMT(利率, 期, 期間現在価値)

【利率】
借入れの利率を指定します。
求める金額は月額の返済額のうちの利息部分なので、利率も月利で指定します。
年利1.5%なので0.015/12と入力します。


  求めたい期(回数目)を指定します。
第1回目の返済利息額を求めたいので1を入力します。

期間
ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

現在価値
  借入れ金額を入力します。
3千万円借入をするので30000000と入力します。

 

引数指定は以上です。

IPMT(元利均等返済方式の利息計算)では「将来価値」と「支払期日」がありましたがISPMTではないのですね。

将来価値は、借入返済ですので返済後はゼロになるとしても、支払期日の指定がないのは困ります。

ISPMTでの支払期日は、期首払いとなっているようです。

 

 セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC6に、次のように該当する数値を入力して、C8で各セルを参照した式を入力しても計算できます。

 

各セルの数値を変更してシミュレーションしてみて下さい。

 

 ISPMTを期末払にする方法

ISPMTで期末払いとするには、第2引数(何回目の返済なのか)を指定する際に「-1」をして下さい。

たとえば、今回の例では2回目の支払利息を知りたいときは
=ISPMT(0.015/12, 2-1, 20*12,  30000000) となります。

 

求めたい期から -1 する理由

2回目の支払利息を知りたいとき、期首払い(青)であれば元金返済は2回終了していますが、期末払い(白)であれば元金返済は1回終了しています。

よって、期末払いの2回目の支払利息を知りたいときには1回返済した後の支払利息ですので引数1(1回支払済み)を指定したISPMTと同じになります。

 

PDURATION関数を使って目標貯蓄額は何歳で達成できるかを計算する

質問

現在30歳です。今1,000万円の投資をして将来3,000万円を目標に運用したいのですが、3%で運用するとしたら何歳で到達できるでしょうか?

 

イメージ図

 

利率が一定であると仮定した場合の、目標貯蓄に達するための投資に必要な期間を求めるにはエクセルのPDURATION関数を使います。

(積立による投資期間を求めるにはNPER関数を使用して下さい)→ こちら

エクセルのセルに、次のように入力して下さい。

= PDURATION ( 0.03 , 10000000 , 30000000 )

それぞれの項目の意味は次のとおりです。

=PDURATION (利率,  現在価値,将来価値)
 

すると、答えは

37.17年

となります。

よって、67歳2か月で貯蓄は達成できます。

回答

30歳で10,000万円投資をして平均運用利回り3%で運用した場合、67歳で目標貯蓄額3,000万円に到達できる

 

PDURATION関数の各項目の意味は次のとおりです。

PDURATION(利率,  現在価値,将来価値)

【利率】
投資運用利回りを設定します。
3%で運用するので0.03と入力します。

【現在価値】
投資額を指定します。
1,000万円を投資するので10000000と入力します。

【将来価値】
将来の目標貯蓄額を指定します。
60歳で30,000,000円を貯蓄したいので、30000000と入力します。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC5に、次のように該当する数値を入力して、C7で各セルを参照した式を入力しても計算できます。

各セルを変更してシミュレーションしてみて下さい。

 

RRI関数を使ってどれくらいの利率で運用すれば目標貯蓄額に達するのかを計算する

質問

現在30歳ですが、1,000万円の貯蓄があります。これを運用して60歳で3,000万円にしたいのですが、運用利回りとして何パーセントで運用したら目標に到達できるでしょうか?

 

イメージ図

 

将来の目標貯蓄まで、何%で運用したらよいかを計算するには、エクセル関数のRRI関数を使用します。

エクセルのセルに、次のように入力して下さい。

   = RRI ( 30, 10000000, 30000000) 

それぞれの項目は次の通りです。

    =RRI(期間,  現在価値, 将来価値)
 

すると答えは

3.73%となります.

 

回答

30歳で1,000万円を投資して運用し、60歳で3,000万円にするには3.73%で運用する必要がある。

 

それでは、それぞれの項目について説明していきます。

 

RRI関数の使い方

指定した現在価値と将来価値から複利の利率を求めるにはRRI関数を使用します。
(積立による利率を求めるにはRATE関数を使用します)こちら

=RRI(期間,  現在価値, 将来価値)

 

【期間】
投資の期間を指定します。
30年で目標貯蓄を達成したいので30と入力します。

【現在価値】
投資額を入力します。
10,000万円を投資するので10000000と入力します。

【将来価値】
投資期間終了後の目標貯蓄額を入力します。
60歳(将来)時点で3,000万円を目標としていますので30000000と入力します。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC5に、次のように該当する数値を入力して、C7で各セルを参照した式を入力しても計算できます。

各セルの数値を変えてシミュレーションしてみて下さい。

 

CUMPRINC関数により返済元金累計を計算する

質問

3,000万円を金利1.5%で借り入れて、元利均等返済方式で20年間毎月返済していきます。10年経過した時点で返済した元本金額は累計でいくらになりますか?

 

元利均等返済方式により返済をしていく場合、求めたい期間の返済額累計のうち、元金相当分の累計はいくらになるかを求めるには「CUMPRINC」関数を使います。

イメージ図

 

エクセルに次ぎのように入力して下さい。

= CUMPRINC  ( 0.015/12 , 20*12 , 30000000 , 1 , 120, 0)

それぞれの意味は次の通りです。

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

これにより、

-13,877,803

と答えが出ます。

 

回答

3,000万円を金利1.5%で借り入れて、元利均等返済方式で20年間毎月返済する場合、10年経過した時点で返済した元本累計は13,877,803円となる

 

CUMPRINC関数の各項目は次の通りです。

【利率】
返済額は月額のため、金利も月利で指定します。
年利1.5%ですので0.015/12と入力します。


【期間】
ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

【現在価値】
借入金額
を入力します。
3,000万円を借入たので30000000と入力します。

【開始期】
求めたい期間の開始期を入力します。
求めたい期間は1回目~120回目の元金累計なので開始期は1と指定します。

【終了期】
求めたい期間の終了期を入力します。
求めたい期間は1回目~120回目の元金累計なので終了期は120と指定します。

 【支払期日】
返済額が、期首に行われるか期末に行われるかを指定します。
返済は月末に行われるので0と入力します。

1 期首に支払
0 期末に支払

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC8に、次のように該当する数値を入力して、C10で各セルを参照した式を入力しても計算できます。

各セルの数値を変更してシミュレーションをしてみて下さい。

 

IPMT関数を使って指定した期の支払利息を計算する

質問

銀行より3,000万円借入れて20年間元利均等方式で毎月返済していく。第1期目の支払利息はいくらか?

 

3,000万円を銀行から元利均等にて、1.5%(固定)で借入れて20年間で返済する場合、毎月の返済額はPMT関数により144,764円とわかりました。
→ PMT関数を使って毎月の返済額を計算する

これは、元金の返済部分と支払利息で構成されています。
では、第1期目の支払額のうち支払利息部分はいくらになるのでしょうか?

イメージ図

 

 

返済方法には、元利均等返済と元金均等返済があります。
毎回の返済額には元本の返済分と利息分が含まれます。

元利均等返済は毎回の返済額(元金+利息)が一定であり、元金均等返済は毎回返済する元金が一定(返済額は変動)となります。

 

このうち、元利均等返済方式についての返済額のうち支払利息部分を求めていきます。
元利均等返済の場合、毎回の支払額は一定ですが、そのうちの支払利息および支払元金部分は一定ではありません。

支払利息は始めのうち多く、だんだん少なくなっていきます。(支払元金部分はその逆)

 

IPMT関数の使い方

借入れをした場合、ある指定した期の支払利息額を求めるにはエクセル関数のIPMT関数を使用します。

エクセルのセルに、次のように入力して下さい。

=IPMT(0.015/12, 1, 20*12,  30000000, 0, 0)

それぞれの意味は次の通りです。

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

すると答えは

-37,500

となります。

 

回答

3,000万円を金利1.5%で借り入れして、20年間元利均等方式で毎月返済していく場合、第1回目の返済額のうちの支払利息額は37,500円となる。

 

それでは、それぞれの項目について説明していきます。

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

【利率】
借入れの利率を指定します。
求める金額は月額の返済額のうちの利息部分なので、利率も月利で指定します。
年利1.5%なので0.015/12と入力します。


  求めたい期(回数目)を指定します。
第1回目の返済利息額を求めたいので1を入力します。

期間
ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

現在価値
  借入れ金額を入力します。
3千万円借入をするので30000000と入力します。


将来価値
  将来価値、つまり最後の返済を行った後に残る元本の残高を入力します。
  将来価値を省略すると、0 (ゼロ) を指定したと見なされます。

支払期日
  返済がいつ行われるかを、数値の 0 または 1 で指定します。
返済は月末に行われるので0を入力します。

1  各期の期首
0  各期の期末

支払期日を省略すると、0 を指定したと見なされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC8に、次のように該当する数値を入力して、C10で各セルを参照した式を入力しても計算できます。

各セルの数値を変更してシミュレーションしてみて下さい。

 

PPMT関数を使って指定した期の返済元金を計算する

質問

銀行より3,000万円借入れをして20年間元利均等方式で毎月返済していきます。第1回目の返済元金はいくらでしょうか?

 

3,000万円を銀行から元利均等方式にて、1.5%(固定)で借入れをして20年間で返済する場合、毎年の返済額はPMT関数により144,764円とわかります。
→ PMT関数を使って毎月の返済額を計算する

これは、元金の返済部分と支払利息で構成されています。
では、第1回目の支払額のうち返済元金部分はいくらになるのでしょうか?

イメージ図

 

 

返済方法には、元利均等返済と元金均等返済があります。
毎回の返済額には元本の返済分と利息分が含まれます。

元利均等返済は毎回の返済額(元金+利息)が一定であり、元金均等返済は毎回返済する元金が一定(返済額は変動)となります。

このうち、元利均等返済についての返済額のうち返済元金部分を求めていきます。
元利均等返済の場合、毎回の支払額は一定ですが、そのうちの支払利息および返済元金部分は一定ではありません。

返済元金は始めのうち少なく、だんだん多くなっていきます。(支払利息部分はその逆)

 

PPMT関数の使い方

借入れをした場合、ある指定期の返済元金額を求めるにはエクセル関数のPPMT関数を使用します。

エクセルのセルに、次のように入力して下さい。

=PPMT(0.015/12, 1, 20*12,  30000000, 0, 0)

それぞれの意味は次の通りです。

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

すると答えは

-107,264

となります。

 

回答

3,000万円を金利1.5%で借り入れして20年間元利均等方式で返済していく場合、第1回目の返済元金額は107,264円となる。

 

それでは、それぞれの項目について説明していきます

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

 

【利率】
借入れの利率を指定します。
求める金額は月額の返済額のうちの元金部分なので、利率も月利で指定します。
年利1.5%なので0.015/12と入力します。


  求めたい期(回数目)を指定します。
第1回目の返済元金額を求めたいので1を入力します。

期間
ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

現在価値
  借入れ金額を入力します。
3千万円借入をするので30000000と入力します。


将来価値
  将来価値、つまり最後の返済を行った後に残る元本の残高を入力します。
  将来価値を省略すると、0 (ゼロ) を指定したと見なされます。

支払期日
  返済がいつ行われるかを、数値の 0 または 1 で指定します。
返済は月末に行われるので0を入力します。

1  各期の期首
0  各期の期末

支払期日を省略すると、0 を指定したと見なされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC8に、次のように該当する数値を入力して、C10で各セルを参照した式を入力しても計算できます。

各セルの数値を変更してシミュレーションをしてみて下さい。

 

PMT関数を使って毎月の返済額を計算する

質問

3,000万円を元利均等方式により年利1.5%で借入れて、20年間で月額返済していきます。毎月の返済額はいくらになるでしょうか?

 

住宅購入を考えています。
3,000万円を銀行から元利均等方式にて、1.5%(固定)で借入れて20年間で返済する場合、毎月の返済額はいくらになるのでしょうか?

イメージ図

 

返済方法には、元利均等返済と元金均等返済があります。

毎回の返済額には元本の返済分と利息分が含まれます。

元利均等返済は毎回の返済額(元金+利息)が一定であり、元金均等返済は毎回返済する元金が一定(返済額は変動)となります。

 

このうち、元利均等返済(返済額一定)について毎回の返済額を求めていきます。

 

借入れをした場合、その毎回の返済額を求めるにはエクセル関数のPMT関数を使用します。

エクセルのセルに、次のように入力して下さい。

=PMT(0.015/12, 20*12,  30000000, 0, 0)

それぞれの意味は次の通りです。
=PMT(利率,期間,現在価値,将来価値,支払期日)

すると答えは

-144,764

となります。

 

回答

3,000万円を金利1.5%で借り入れして20年間で返済していくには、毎月144,764円づつ返済する必要がある。

 

それでは、それぞれの項目について説明していきます。

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

 

【利率】
借入れの利率を指定します。
求める金額は月の返済額なので、ここでも月利で指定します。
年利1.5%で借入をするので0.015/12と入力します。

期間
  ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

現在価値
  借入れ金額を入力します。
3千万円借入をするので30000000と入力します。

将来価値
  将来価値、つまり最後の返済を行った後に残る元本の残高を入力します。
  将来価値を省略すると、0 (ゼロ) を指定したと見なされます。

支払期日
  返済がいつ行われるかを、数値の 0 または 1 で指定します。
返済は月末に行われるので0を入力します。

1  各期の期首
0  各期の期末

支払期日を省略すると、0 を指定したと見なされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます。

各セルの数値を変更してシミュレーションしてみて下さい。

 

CUMIPMT関数を使って指定した期間の返済利息累計を計算する

質問

3,000万円を金利1.5%で借入れて、元利均等返済方式により20年間月額返済していきます。支払い1年目から10年目までの間に支払った金利の累計はいくらになるのでしょうか?

イメージ図

ある期間中で返済した金額のうち、利息の累計はいくらになるかを計算するには、エクセルの「CUMIPMT」関数を使います。

次のように入力して下さい。

  = CUMIPMT ( 0.015/12 , 20*12 , 30000000 , 1 , 10*12, 0)

すると答えは

-3,493,831

となります。(結果はマイナスで表示されます。)

回答

3,000万円を金利1.5%で借入れをして、元利均等返済方式により20年間月額返済する場合、支払1年目から10年目までの間に支払った金利の累計は3,493,831円となります。

CUMIPMTの使い方

定期的にローンの返済を行うとき、指定した開始・終了期の返済額のうち、金利相当分の累計がいくらになるかを求めるのがCUMIPMT関数です。
結果は通常マイナスで示されます。

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

【利率】
返済額は月額のため、金利も月利で指定します。
年利1.5%ですので0.015/12と入力します。

【期間】
ローン期間全体での支払回数の合計を指定します。
ここでは20年間で返済するので20*12と入力します。
(240ヶ月ですので240と入力してもOKです)

【現在価値】
借入金額
を入力します。
3,000万円を借入たので30000000と入力します。

【開始期】
求めたい期間の開始期を入力します。
求めたい期間は1回目~120回目の利息累計なので開始期は1と指定します。

【終了期】
求めたい期間の終了期を入力します。
求めたい期間は1回目~120回目の利息累計なので終了期は120と指定します。

 【支払期日】
返済額が、期首に行われるか期末に行われるかを指定します。
返済は月末に行われるので0と入力します。

1 期首に支払
0 期末に支払

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC8に、次のように該当する数値を入力して、C10で各セルを参照した式を入力しても計算できます。

各セルの数値を変更してシミュレーションしてみて下さい。
 

CUMPRINC関数を使って借入金一括返済額を計算する

質問

3000万円を金利1.5%で借り入れて、元利均等返済で20年間毎月返済していきます。10年経過した時点で一括返済したいのですが返済する金額はいくらになりますでしょうか?

 

どれくらいの返済元本が残っているかを算出するエクセル関数があればいいのですが見当たりません。

そこで、10年間で返済した元本累計を計算し、当初借り入れた借入金から控除することにより借入金元本残高を求めてみたいと思います。

元利均等返済方式による返済をしていく場合、求めたい期間の返済額累計のうち、元金相当分の累計がいくらになるかを求めるには「CUMPRINC」関数を使います。

こちらを参照してください  → CUMPRINC関数により返済元金累計を計算する

 

 

まずCUMPRINC関数により10年間の返済元本累計を求めると次のようになります。

エクセルに次ぎのように入力して下さい。

= CUMPRINC  ( 0.015/12 , 20*12 , 30000000 , 1 , 120, 0)

 

それぞれの意味は次の通りです。

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

これにより、

-13,877,803

と答えが出ます。

 

これは、どれくらい元金が減ったかを示したものであり、知りたいのはどれくらい元金が残るかです。

よって、借入金額3,000万円からこの金額を控除したものが、その時点での借入金残高になります。

30,000,000円-13,877,803円=16,122,197円

となり、一括返済する元本残高を求めることができます。

回答

3,000万円を金利1.5%で借り入れて、元利均等返済方式で20年間毎月返済する場合、10年経過した時点で一括返済するとしたら16,122,197円の返済額が必要となる

 

RATE関数を使って元本を何%で運用する必要があるかを計算する

質問

65歳現在3000万円の貯蓄があるとすると、90歳まで毎月15万円づつ取り崩すには何%で運用しなければならないのか?

 

イメージ図

 

 

必要な運用利率を求めるには、RATE関数を使います。

どこかのセルに次ぎのように入力して下さい。

= RATE ( 25*12, -150000, 30000000, 0, 1) 

RATE関数の各項目の意味は次のとおりです。
RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)

 

すると、答えは

0.29%

となります。

0.29%??  いやに低いですね。これなら余裕~なんて間違ってはいけません。

ここで求められたのは月の利率ですので、出た答えを12倍して年利に直す必要があります。

0.29%×12=3.52%

 

回答

65歳現在3000万円の貯蓄がある。90歳まで毎月15万円づつ取り崩すには年利3.52%で運用しなければならない。

 

RATE関数の各項目の意味は次のとおりです。

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

 

【期間】
支払額が月額なのか、年額なのかに注意します。
上記例では、取崩額は月額15万円なので、期間も月の回数(25×12)とします。
(300カ月なので300と入力してもOKです)

【定期支払額】
定期支払額(取崩額)はマイナスで入力します。
毎月15万円づつ取り崩すので、-150000とします。

【現在価値】
65歳現在の貯蓄額 30,000,000円 なので30000000と入力します。

【将来価値】
90歳時点での貯蓄残金0円を設定します。
(90歳で残したい遺産があればその額を入力します)

【支払期日】
期首に引き出すか、期末に引き出すかを指定します。
生活費として取り崩したいので月初に取り崩すため1を入力します。

1 期首に引き出す場合
0 期末に引き出す場合

【推定値】
利率の推定値を指定します。推定値を省略すると10%が指定されたものとみなされます。

 

ここで求まる利率は月利となります。
よって、求まった利率を×12として年利に直します。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます。

各セルの数値を変えてシミュレーションしてみて下さい。