記録することによって見えてくるもの

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

かわべ
WRITER
 
この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)
<スポンサーリンク>  
 

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

(以前の記事)

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

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

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

返済予定表_3

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

<スポンサーリンク>    
 

仮計算をする列の追加

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

返済予定表_11

 

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列の最大値を表示するようにいたしました。

 

返済予定表_12

 

返済予定表の加工

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列を加算して支払金額を算出しています。

 

返済予定表_13

 

2 D列

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

『=IF(B12=C$5,F11,C12E12)』

 

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

 

おまけ(PMT関数)

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

返済予定表_15

 

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

 

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

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

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

□ 将来価値・・・省略(※1)

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

 

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

「-35,035,52011」

 

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


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

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

 

Pocket
このエントリーを Google ブックマーク に追加
LINEで送る

この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)

報酬のお見積り

法人の顧問報酬と決算報酬を、前期の売上実績当期の売上予測でお見積もりすることができます。(※)

LINK 報酬お見積もり【法人用】 web

お名前やメールアドレスの入力は必要ありません。ウェブ上で簡単にお見積もりができます。

※ オプション料金は別途となります。

Copyright© J-musu-no-blog , 2014 All Rights Reserved.