Excel でローン償却スケジュールを作成する – ステップバイステップチュートリアル

Excel でローンの償却スケジュールを作成するスキルは非常に実用的で、ローン返済の状況を視覚的に把握・管理するのに役立ちます。償却スケジュールとは、住宅ローンや自動車ローンなどの償却ローンについて、各返済期間ごとの内訳を示した表です。この表では、毎回の返済額を利息と元本に分け、それぞれの返済後の残高を明確に表示します。それでは、Excel でこのようなスケジュールをステップバイステップで作成する方法をご紹介します。

償却スケジュールとは？

Excel で償却スケジュールを作成する

可変期間の償却スケジュールを作成する

追加支払いを含む償却スケジュールを作成する

Excel テンプレートを使用して償却スケジュール（追加支払い付き）を作成する

償却スケジュールとは？

償却スケジュールとは、ローン返済の経過を時系列でわかりやすく示す詳細な表です。住宅ローン、自動車ローン、個人ローンなど、返済額がローン期間を通じて一定となる固定金利ローンでよく用いられます。ただし、返済額に占める利息と元本の割合は、時間の経過とともに変化していきます。

Excel でローンの償却スケジュールを作成するには、組み込み関数である PMT、PPMT、IPMT が欠かせません。それぞれの関数がどのような役割を果たすのか、しっかり確認しましょう。

• PMT 関数：この関数は、一定の金利と一定の支払額に基づき、ローンの各期間における合計支払額を計算する際に使用されます。
• IPMT 関数：この関数を使えば、指定した期間の支払額のうち、利息に該当する金額を簡単に計算できます。
• PPMT 関数：この関数を使えば、特定の期間における支払額のうち、元本にあたる部分を正確に計算できます。

これらの関数をExcel で活用すれば、各返済における利息と元本の内訳、および各返済後のローン残高を示す詳細な償却スケジュールを簡単に作成できます。

Excel で償却スケジュールを作成する

このセクションでは、Excel で償却スケジュールを作成する2 つの異なる方法をご紹介します。これらの方法は、ユーザーの好みやスキルレベルに合わせて設計されており、Excel の習熟度を問わず、誰でも正確で詳細なローン償却スケジュールを簡単に作成できます。

数式を使うと、背後にある計算ロジックを深く理解でき、特定の要件に応じてスケジュールを柔軟にカスタマイズできます。このアプローチは、実際に手を動かしながら各返済が利息と元本にどう分割されるかを明確に把握したい方におすすめです。それでは、Excel で償却スケジュールを作成する手順を段階的に説明します。

⭐️ ステップ1：ローン情報を設定し、償却テーブルを作成する

1. 年利、ローン期間（年単位）、年間支払回数、ローン金額などの関連ローン情報を、以下スクリーンショットのようにセルに入力してください。
   
2. 次に、Excel で「期間」「支払額」「利息」「元本」「残高」などのラベルを含む償却テーブルを作成します。具体的には、セル A7～E7 にそれぞれ対応するラベルを入力してください。
3. 「期間」列には期間番号を入力してください。この例では、支払総回数が24 か月（2 年）のため、「期間」列に1 から24 までの数字を順に入力します。スクリーンショットをご参照ください。
   
4. ラベルと期間番号を設定すれば、ローンの詳細情報に基づき、「支払額」「利息」「元本」「残高」の各列に数式と値を自動入力できます。

⭐️ ステップ2：PMT 関数を使用して合計支払額を計算する

PMT 関数の構文は次のとおりです。

セル B7 に次の数式を入力し、フィルハンドルを下にドラッグして他のセルにも適用すると、すべての期間で一定の支払額が表示されます。スクリーンショットをご確認ください。

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ ステップ3：IPMT 関数を使用して利息を計算する

このステップでは、Excel の IPMT 関数を使って各支払期間にかかる利息を計算します。

セル C7 に次の数式を入力し、フィルハンドルを列の下までドラッグして、各期間の利息を計算してください。

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ ステップ4：PPMT 関数を使用して元本を計算する

各期間の利息を計算した後、償却スケジュール作成の次のステップは、各返済の元本部分を計算することです。これには PPMT 関数を使用します。この関数は、一定の支払額と一定の金利に基づき、特定の期間における返済額のうち元本に該当する部分を正確に算出するよう設計されています。

IPMT 関数の構文は次のとおりです。

PPMT 関数の構文およびパラメーターは、前述の IPMT 関数で使用したものと同じです。

セル D7 に次の数式を入力し、フィルハンドルを列の下までドラッグして、各期間の元本を計算してください。スクリーンショットをご参照ください。

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ ステップ5：残高を計算する

各返済における利息と元本を計算した後、償却スケジュールの次のステップは、各返済後のローン残高を算出することです。これはスケジュールの中核をなす要素であり、ローン残高が時間の経過とともにどのように減少していくかを明確に示します。

1. 残高列の最初のセル（E7）には、次の数式を入力します。これにより、残高は元のローン金額から初回支払いの元本部分を差し引いた金額になります。

   =B4-D7

   
2. 2 回目以降の期間については、前回の残高から当該期間の元本返済額を差し引いて残高を計算します。セル E8 に次の数式を適用してください。

   =E7-D8

   注：残高セルへの参照を相対参照にしておくと、数式を下にドラッグした際に自動的に更新されます。
   
3. その後、フィルハンドルを列の下までドラッグすると、各セルが自動的に調整され、最新の元本返済額に基づいて残高が計算されます。
   

⭐️ ステップ6：ローン概要を作成する

詳細な償却スケジュールを設定した後は、ローンの主要項目を素早く把握できるよう、ローン概要を作成するのが便利です。この概要には通常、ローンの総費用や支払総利息が含まれます。

● 合計支払額を計算する場合：

=SUM(B7:B30)

● 支払総利息を計算する場合：

=SUM(C7:C30)

⭐️ 結果：

これで、シンプルかつ包括的なローン償却スケジュールが正常に作成されました。スクリーンショットをご確認ください。

KUTOOLS AI でExcel の魔法を解き放ちましょう

• スマート実行：セル操作、データ分析、チャート作成を、シンプルなコマンド一つで一括処理します。
• カスタム数式：ワークフローの効率化に役立つカスタム数式を自動生成します。
• VBA コーディング：VBA コードを簡単に記述・実装できます。
• 数式の解釈：複雑な数式も、かんたんに理解できます。
• テキスト翻訳：スプレッドシート内の言語の壁を打ち破ります。

AI 搭載ツールでExcel スキルをさらに強化しましょう。今すぐダウンロードして、これまでにない効率を体験してください！

可変期間の償却スケジュールを作成する

前の例では、固定の支払回数に基づいたローン返済スケジュールを作成しました。このアプローチは、条件が変更されない特定のローンや住宅ローンの処理に最適です。

ただし、さまざまな期間のローンに繰り返し対応でき、ローン条件に応じて支払回数を柔軟に調整可能な償却スケジュールを作成したい場合は、より詳細な手順に従う必要があります。

⭐️ ステップ 1：ローン情報と償却表を設定する

1. 年利、ローン期間（年単位）、年間支払回数、ローン金額など、関連するローン情報を、以下のスクリーンショットのようにセルに入力してください。
   
2. 次に、Excel で「期間」「支払額」「利息」「元本」「残高」などのラベルを含む償却テーブルを作成します。具体的には、セル A7～E7 にそれぞれ対応するラベルを入力してください。
3. 「期間」列には、あらゆるローンで想定される最大の支払回数を入力してください。たとえば、1 から360 までの数値を指定すれば、毎月返済する標準的な30 年ローンにも対応できます。
   

⭐️ ステップ 2：IF 関数を使って支払額、利息、元金の計算式を修正する

対応するセルに以下の数式を入力し、フィルハンドルをドラッグして、設定した最大支払期間まで下方にコピーしてください。

● 支払額の計算式：

通常、支払額の計算には PMT 関数を使用します。IF 文を組み込む場合、その構文は次のとおりです。

したがって、数式は次のとおりです。

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● 利息の計算式：

構文は次のとおりです。

したがって、数式は次のとおりです。

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● 元金の計算式：

構文は次のとおりです。

したがって、数式は次のとおりです。

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ ステップ 3：残高の計算式を調整する

残高は通常、前回の残高から元金を差し引いて算出します。これを IF 文を使って次のように修正します。

● 最初の残高セル：(E7)

=B4-D7

● 2 番目の残高セル：(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ ステップ 4：ローン概要を作成する

修正済みの数式で償却スケジュールを設定したら、次はローン概要を作成しましょう。

● 合計支払額を計算する場合：

=SUM(B7:B366)

● 支払総利息を計算する場合：

=SUM(C7:C366)

⭐️ 結果：

これで、詳細なローン概要を含んだ包括的かつ動的なExcel 償却スケジュールが完成です。支払期間を調整すれば、償却スケジュール全体が自動的に更新されます。下記のデモをご覧ください。

追加支払いを含む償却スケジュールを作成する

予定された支払いに加えて追加支払いを行うことで、ローンをより早く完済できます。Excel で作成する追加支払い対応の償却スケジュールを使えば、こうした追加支払いがローンの完済をどれほど早め、総利息をどれだけ削減できるかを明確に示すことができます。以下にその設定方法をご紹介します。

⭐️ ステップ 1：ローン情報と償却表を設定する

1. 年利、ローン期間（年単位）、年間支払回数、ローン金額、追加返済額など、関連するローン情報を、以下スクリーンショットのとおりセルに入力してください。
   
2. 次に、「予定支払額」を計算します。
   入力セルに加えて、今後の計算に必要なもう一つの事前定義セルがあります。それが「予定支払額」です。これは、追加返済を行わない場合の通常のローン返済額を示します。セル B6 に次の数式を入力してください。

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. 次に、Excel で減価償却テーブルを作成します。
   • セル A8:G8 に、「期間」「スケジュール支払い」「追加支払い」「合計支払い」「利息」「元本」「残高」などのラベルを入力してください。
   • 「期間」列には、対象となるローンについて検討可能な最大支払回数を入力します。たとえば、0～360 の範囲で数値を指定すれば、月払いの標準的な30 年ローンに対応できます。
   • 期間0（この例では行9）の残高値は、次の数式で取得します。=B4。これは元本額に相当します。この行の他のすべてのセルは空白のままにしてください。
   • 

⭐️ ステップ 2：追加支払いを含む償却スケジュールの数式を作成する

対応するセルに、以下の数式を1 つずつ入力してください。エラー処理を強化するため、この数式および以降のすべての数式を IFERROR 関数で囲んでください。これにより、入力セルが空白であったり、不正な値が含まれていたりした場合に発生する可能性のあるさまざまなエラーを回避できます。

● 予定支払額を計算する：

セル B10 に次の数式を入力してください。

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● 追加支払額を計算する：

セル C10 に次の数式を入力してください。

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● 合計支払額を計算する：

セル D10 に次の数式を入力してください。

=IFERROR(B10+C10, "")

● 元金を計算する：

セル E10 に次の数式を入力してください。

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● 利息を計算する：

セル F10 に次の数式を入力してください。

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● 残高を計算する

セル G10 に次の数式を入力してください。

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

すべての数式を入力したら、セル範囲 B10:G10 を選択し、フィルハンドルをドラッグして支払期間全体にわたってこれらの数式を下方にコピーしてください。使用されない期間のセルには 0 が表示されます。スクリーンショットをご確認ください。

⭐️ ステップ 3：ローン概要を作成する

● 予定支払回数を取得する：

=B2:B3

● 実際の支払回数を取得する：

=COUNTIF(D10:D369,">"&0)

● 追加支払総額を取得する：

=SUM(C10:C369)

● 総利息を取得する：

=SUM(F10:F369)

⭐️ 結果：

これらの手順に従えば、追加支払いを考慮した動的なExcel 償却スケジュールを簡単に作成できます。

Excel テンプレートを使用して償却スケジュールを作成する

Excel のテンプレートを使えば、償却スケジュールを簡単に、しかも時短で作成できます。Excel には、各支払いの利息・元金・残高を自動計算してくれるビルトインテンプレートが用意されています。以下では、そのテンプレートを使った償却スケジュールの作成手順をご紹介します。

1. ファイル > 新規作成をクリックし、検索ボックスに償却スケジュールと入力してEnterキーを押してください。その後、ニーズに最も合うテンプレートをクリックして選択します。たとえば、ここでは「シンプルなローン計算機」テンプレートを選択します。スクリーンショットをご参照ください。
   
2. テンプレートを選択したら、作成ボタンをクリックして、新しいワークブックで開きます。
3. その後、ご自身のローン詳細を入力すると、テンプレートが自動的にスケジュールを計算・表示します。
4. 最後に、新しい償却スケジュールのブックを保存しましょう。