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

Excelでローンの償還スケジュールを作成することは、ローンの返済を効果的に視覚化し管理できるため、非常に価値のあるスキルです。償還スケジュールは、通常住宅ローンや自動車ローンなどの元本均等返済型ローンにおける各期ごとの支払いを詳細に示した表です。この表では、各支払いが利息と元本にどのように分かれるのかを分解し、各支払い後の残高も表示されます。では、Excelでこのようなスケジュールを作成するステップバイステップガイドに入りましょう。

償還スケジュールとは何ですか？

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

可変期間に対応する償還スケジュールを作成する

追加返済を含む償還スケジュールを作成する

Excelテンプレートを使用して（追加返済を含む）償還スケジュールを作成する

償還スケジュールとは何ですか？

償還スケジュールは、ローンの返済プロセスを時間とともに表示する詳細な表であり、ローン計算に使用されます。償還スケジュールは、住宅ローン、自動車ローン、個人ローンなどの固定金利ローンで一般的に使用され、支払額はローン期間中一定ですが、利息と元本への支払い比率は時間とともに変化します。

Excelでローンの償還スケジュールを作成する際、組み込み関数であるPMT、PPMT、IPMTは確かに重要です。それぞれの関数が何をするのか理解しましょう：

• PMT関数: この関数は、一定の支払いと一定の利率に基づいて、ローンの1期あたりの総支払い額を計算するために使用されます。
• IPMT関数: この関数は、特定の期間の支払いにおける利息部分を計算します。
• PPMT関数: この関数は、特定の期間の支払いにおける元本部分を計算するために使用されます。

これらの関数をExcelで使用することで、各支払いの利息と元本の内訳および各支払い後のローンの残高を示す詳細な償還スケジュールを作成できます。

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

このセクションでは、Excelで償還スケジュールを作成する2つの異なる方法を紹介します。これらの方法は、ユーザーの好みやスキルレベルに応じて異なり、Excelの習熟度に関係なく誰でも詳細で正確なローンの償還スケジュールを構築できるようになります。

数式を使うことで、基礎となる計算について深い理解を得られ、特定の要件に合わせてスケジュールをカスタマイズする柔軟性も得られます。このアプローチは、各支払いが元本と利息にどのように分解されるかを明確に把握したい方にとって理想的です。では、Excelで償還スケジュールを作成する手順を段階的に説明しましょう：

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

1. 次のスクリーンショットに示すように、年利率、ローン期間（年）、年間の支払い回数、ローン金額などの関連情報をセルに入力します：
   
2. 次に、A7:E7のセルに「期間」、「支払い」、「利息」、「元本」、「残高」というラベルを指定してExcelで償還表を作成します。
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. 次に、A7:E7のセルに「期間」、「支払い」、「利息」、「元本」、「残高」というラベルを指定してExcelで償還表を作成します。
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. 次に、計画された支払いを計算します。
   入力セルに加えて、後続の計算のためにもう1つの事前定義されたセルが必要です – 計画された支払額です。これは、追加支払いがない場合のローンの通常の支払額です。次の数式をセルB6に適用してください：

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

   
   
3. 次に、Excelで償還表を作成します：
   • A8:G8のセルに「期間」、「計画支払い」、「追加支払い」、「総支払い」、「利息」、「元本」、「残高」という指定されたラベルを設定します；
   • 「期間」列には、考えられる最大の支払い回数を入力します。例えば、0から360までの数字を入力します。これにより、月々の支払いを行う場合、標準的な30年ローンをカバーできます；
   • 期間0（当ケースでは9行目）では、初期ローン金額に対応する残高値を取得するためにこの数式 =B4 を使用します。この行の他のすべてのセルは空白のままにしておきます。
   • 

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

エラー処理を強化するために、今後のすべての数式と同様に、IFERROR関数内にこれらの数式を配置します。このアプローチは、入力セルが空白または誤った値を持っている場合に発生する可能性のある複数の潜在的なエラーを回避するのに役立ちます。対応するセルに次の数式を1つずつ入力します。

● 計画された支払いを計算する：

次の数式をセル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には、各支払いの利息、元本、残高を自動的に計算するビルトインテンプレートが用意されています。以下は、Excelテンプレートを使用して償還スケジュールを作成する方法です：

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