Excelで借入金返済計画表を作成する方法

借入金返済計画表とは、固定金利の借入にかかる利息と、返済によって元金がどのように減っていくかを示したものです。また、借入金返済計画表にはすべての返済の綿密な計画が示されているので、どれだけの金額が元金に充てられ、どれだけの金額が利息に充てられているのかがわかります。この記事では、Microsoft Excelを使用して自分だけの借入金返済計画表を作成する方法を紹介します。　

方法 1

方法 1 の 2:

借入金返済計画表を手動で作成する

PDF形式でダウンロード

Microsoft Excelで空白のブックを開く
2
A列にラベルを作成する　整理するために1列目にデータのラベルを作成します。各セルに入力すべきものは以下の通りです。
- A1：借入金額
- A2：金利
- A3：返済月数
- A4：毎月返済額
3
B列に借入に関する情報を入力する　B1～B3セルに借入に関する情報を入力します。B4（「毎月返済額」ラベルの右側のセル）は空白のままにしておきましょう。
- 「返済月数」の値は、返済期間の合計月数を入力しましょう。例えば、2年ローンの場合は、24と入力します。
- 「金利」の値は、パーセンテージを入力しましょう（例：8.2％）。
4
B4セルで返済額を計算する　返済額を計算するには、B4セルをクリックし、シート上部の数式（fx）バーに=ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)という数式を入力して、↵ Enterキーまたは⏎ Returnキーを押します。
- 数式の中のドルマークは絶対参照で、ワークシートの他の場所にコピーしても、数式が常に特定のセルを参照するようになっています。
- 借入金利は、毎月計算される年率なので、12で割る必要があります。
- 例えば、1500万円を6％の金利で30年（360ヶ月）借入した場合、毎月返済額は8万9933円と計算されます。
5
7行目に列の見出しを作成する　シートにデータを追加するので、2つ目の表が必要になります。セルに以下のラベルを入力しましょう。
- A7：返済年月
- B7：期首残高
- C7：返済額
- D7：元金
- E7：利息
- F7：元金総額
- G7：利息総額
- H7：期末残高
6
「返済年月」列に値を入力する　この列には、返済日を入力します。手順は以下の通りです。
- A8セルに初回返済年月を入力します。ただし、月と年を正しく表示するには、列の書式設定を行う必要があるかもしれません。
- セルを1回クリックして選択します。
- 選択したセルの中心から下に向かってドラッグし、A367セルまでのすべてのセルを適用します。これでもすべてのセルに月々の返済日が正しく反映されない場合は、一番下のセルの右下にある稲妻の付いた小さなアイコンをクリックして、先月というオプションが選択されていることを確認しましょう。
7
B8～H8セルにその他の項目を入力する
- B8セルに借入期首残高を入力します。
- C8セルに=$B$4と入力して、EnterキーまたはReturnキーを押します。
- E8セルに、その期間の期首残高に対する利息額を計算する数式を作成します。数式は=ROUND($B8*($B$2/12), 2)のようになります。なお、ドルマークは相対参照を表すものです。この数式で、B列から該当するセルを検索します。
- D8セルで、C8セルの返済総額からE8セルの利息額を差し引きます。このセルが正しくコピーされるように相対参照を使用しましょう。数式は=$C8-$E8のようになります。
- H8セルに、その期間の期首残高から返済額の元金部分を差し引く数式を作成します。数式は=$B8-$D8のようになります。
8
B9セルからH9セルまでの項目を入力して、借入金返済計画表を作成していく
- B9セルでは、前期の期末残高を相対参照で指定する必要があります。B9セルに=$H8と入力して、EnterキーまたはReturnキーを押します。
- C8セル、D8セル、E8セルをコピーして、C9セル、D9セル、E9セルに（それぞれ）貼り付けます。
- H8をコピーしてH9に貼り付けます。ここで相対参照が役立ちます。
- F9セルに、返済済み元金総額を集計する数式を作成します。数式は=$D9+$F8のようになります。
- 利息総額の計算式をG9セルに=$E9+$G8のように入力します。
選択した部分の右下にマウスカーソルを合わせると、カーソルが十字カーソルになります。
10
十字カーソルを367行目までドラッグする　これで、367行目までのすべてのセルに借入金返済計画表が反映されます。
- うまく反映されない場合は、一番下のセルの右下にあるスプレッドシートのような小さなアイコンをクリックして、セルのコピーを選択しましょう。
広告

方法 2

方法 2 の 2:

Excelテンプレートを使用する

PDF形式でダウンロード

ローン計画書テンプレートのダウンロードページにアクセスする　これは、無料でダウンロードできる借入金返済計画表のテンプレートで、利息総額と返済総額を簡単に計算することができます。このテンプレートには、繰上げ返済を追加するオプションもあります。^{[1]X出典文献}
をクリックする　Excelのテンプレート形式（xltx）でパソコンに保存されます。
3
ダウンロードしたファイルをダブルクリックする　ファイル名はtf03986974.xltxで、通常は「ダウンロード」フォルダに入っています。これをダブルクリックすると、Microsoft Excelでテンプレートが開きます。
- テンプレート内のデータは例として用意されているだけで、自分のデータを入力することができます。
- ダイアログボックスが表示された場合は、編集を有効にするをクリックすると、ワークブックに変更を加えることができるようになります。
4
借入金額を「借入金額」セルに入力する　シートの左上の「値を入力」という項目にあります。既存の値（￥5,000.00）をクリックして、自分の金額を入力しましょう。
- ⏎ Returnキーまたは↵ Enterキーを押す（または他のセルをクリックする）と、シートの他の部分の金額が再計算されます。これは、この項目の値を変更するたびに行われます。
これは「年間利子率」セルに入力します。
これは「返済年数」セルに入力します。
例えば、月に1回返済を行う場合は、「1年あたりの支払回数」セルに12と入力しましょう。
これは「借入日」セルに入力します。
各返済日に最低返済額以上の返済を行っている場合は、その繰上げ返済額をこのセルに入力しましょう。そうでない場合は、デフォルト値を「0」に変更します。
「融資元名」セルのデフォルト値は「Woodgrove Bank」となっています。参照のため、これを自分の銀行名に変更しましょう。
11
ワークシートを新しいExcelファイルとして保存する　方法は以下の通りです。
- 左上のファイルメニューをクリックし、名前を付けて保存を選択します。
- 借入金返済計画表を保存する場所を、パソコンまたはクラウド上で指定します。
- ファイル名を入力します。「ファイルの種類」が「Excelブック（*.xlsx）」に設定されていない場合は、ドロップダウンメニュー（「ファイル名」の下）から選択しましょう。
- 保存をクリックします。
広告

ポイント

最終的な期末残高が0円にならない場合は、上記の説明どおりに絶対参照と相対参照を使用し、セルが正しくコピーされていることを確認しましょう。
これが完成すると、貸付期間内の任意の期間にスクロールして、返済のうち元金に充てられる金額、利息に充てられる金額、これまでに返済した元金と利息の金額を確認できるようになります。