【Excel】PPMT関数、IPMT関数を使って事業資金の返済予定表を作成する（追記）

！記事内に広告を含みます

この記事には広告を含む場合があります。

記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

前回に引き続き、PPMT関数とIPMT関数を使った返済予定表について記載いたします。

（以前の記事）

[aside type=”yellow”]※　金融機関等の作成する返済予定表は、日割り日数に基づいて計算していますが、私がPPMT関数、IPMT関数を利用して作成するもは、月割りで計算をしていますので、金融機関の返済予定表とは誤差が生じます。[/aside]

目次表示

仮計算をする列の追加

次のとおり仮計算をするためにH列からJ列を追加しました。

1　J列及びE列

前回の記事で記載した最終の返済回の次の列以降のエラーに対処するため、J列とE列にはIF関数を使って最終の返済回を超えた場合は、「0」を表示するように設定しました。

J列には、次のような式を入力しています。

『=IF(H12>I$5,0,ROUND((PPMT(I$4/12,H12,I$5,-I$6)),0))』

また、E列には、次のような式を入力しています。

『=IF(B12>C$5,0,ROUND((IPMT(C$4/12,B12,C$5,-C$6)),0))』

2　I列

前回までの返済予定表では、毎月の支払金額が一定にならないため、I列でE列とJ列の合計を計算し、仮の支払金額を算出し、I10セルにMAX関数を使って、I列の最大値を表示するようにいたしました。

1　C列

C列には次の式を入力しています。

『=IF(B12>C$5,0,IF(B12=C$5,D12+E12,I$10))』

C列では前回の記事で記載した支払金額が一定しないという事象に対処するため、I10セルの最大値を表示するようにしていますが、2つのIF関数を使って、次の設定をしています。

①　最初のIF関数

最終の返済回の次の列以降のエラーに対処するため、最終の返済回の次以降の列では、「0」を表示するように設定しています。

②　次のIF関数

最終の返済回については、返済元本が、最終の返済回の1個前の返済回の残高となるため、I10セルの最大値とは異なります。このため、最終の返済回では、D列とE列を加算して支払金額を算出しています。

2　D列

D列には次の式を入力しています。

『=IF(B12=C$5,F11,C12–E12)』

IF関数を使用して、最終の返済回では、最終の返済回の1個前の返済回の残高を記載するように設定しています。それ以外の返済回では、C列の支払金額からE列の利息を控除した金額を表示するようにしています。

PMT関数は、元利均等払いの1回の支払額を計算する関数です。

PMT関数は次のように設定します。

□　利率・・・年利を12で割った数値を設定します。

□　期間・・・返済期間を入力します。

□　現在価値・・・借入金額を入力します。

□　将来価値・・・省略（※1）

□　支払期日・・・期首払いか期末払いかを指定します。省略した場合は期末払いを設定したことになります。

年利2%、返済期間60回、借入金額200万円を設定すると結果は次の数値となります。

「-35,035,52011」

このPMT関数を利用して返済予定表を作成する方法も良いかと思います。

■□◆◇　編集後記　◇◆□■

昨日は、ふるさと納税でいただいた「のどぐろ」を夕食で食べました。かなり小ぶりの魚ですが、おいしかったです。最初の投稿で「のどくろ」と記載してしまいましたが、「のどぐろ」でした。