Excel でローン返済スケジュールを作成することは、ローン返済を効果的に視覚化して管理できるようにするための貴重なスキルです。償却スケジュールは、償却ローン (通常は住宅ローンや自動車ローン) の各定期支払いの詳細を記載した表です。各支払いを利息と元金に分けて、各支払い後の残高を示します。 Excel でそのようなスケジュールを作成するためのステップバイステップのガイドを見てみましょう。

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

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

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

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

Excel テンプレートを使用して償却スケジュール (追加支払あり) を作成する

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

償却スケジュールは、ローンの計算に使用される詳細な表であり、ローンの返済プロセスを長期にわたって表示します。償却スケジュールは、住宅ローン、自動車ローン、個人ローンなどの固定金利ローンによく使用されます。この場合、返済額はローン期間を通じて一定ですが、元本に対する利息の支払いの割合は時間の経過とともに変化します。

確かに、Excel でローン返済スケジュールを作成するには、組み込み関数 PMT、PPMT、および IPMT が重要です。各関数の役割を理解しましょう。

• PMT機能: この関数は、一定の支払いと一定の金利に基づいて、ローンの期間ごとの総支払額を計算するために使用されます。
• 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. 8 番目以降のすべての期間については、前期間の残高から現在の期間の元金支払いを差し引いて残高を計算します。次の数式をセル EXNUMX に適用してください。

   =E7-D8

    Note: 残高セルへの参照は相対的である必要があるため、数式を下にドラッグすると更新されます。
   
3. 次に、塗りつぶしハンドルを列まで下にドラッグします。ご覧のとおり、各セルは更新された元金の支払いに基づいて残高を計算するために自動的に調整されます。
   

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

詳細な償却スケジュールを設定した後、ローンの概要を作成すると、ローンの主要な側面の概要を簡単に把握できます。この概要には通常、ローンの総費用、支払われた利息の総額が含まれます。

● 支払総額を計算するには:

=SUM(B7:B30)

● 合計利息を計算するには:

=SUM(C7:C30)

⭐️結果:

これで、シンプルかつ包括的なローン返済スケジュールが正常に作成されました。スクリーンショットを参照してください:

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

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

ただし、さまざまな期間のローンに繰り返し使用できる柔軟な償却スケジュールを作成し、さまざまなローン シナリオに応じて支払い回数を変更できるようにしたい場合は、より詳細な方法に従う必要があります。

⭐️ ステップ 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

● 8 番目のバランスセル:(EXNUMX)

=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 には、各支払いの利息、元金、残高を自動的に計算する組み込みのテンプレートが用意されています。 Excel テンプレートを使用して償却スケジュールを作成する方法は次のとおりです。

1. File > 新作、検索ボックスに「 償却スケジュール、プレス 入力します 鍵。次に、ニーズに最適なテンプレートをクリックして選択します。たとえば、ここではシンプルなローン計算テンプレートを選択します。スクリーンショットを参照してください:
   
2. テンプレートを選択したら、 創造する ボタンをクリックして、新しいワークブックとして開きます。
3. 次に、独自のローンの詳細を入力すると、テンプレートが入力に基づいてスケジュールを自動的に計算し、入力します。
4. 最後に、新しい償却スケジュール ワークブックを保存します。