目次
- あなたの住宅ローンを理解する
- 毎月の支払いを計算する
- 年利を計算する
- ローンの長さの決定
- ローンの分解
- Excelでのローン計算
- ローンの償却
- ローンスケジュールの作成
ローンの返済は、通常は元本と利息を含む一連の定期的な支払いを通じて、以前に貸し手から借りたお金を返済する行為です。 ソフトウェアプログラムExcelを使用してローンの返済を計算できることをご存知ですか？
この記事は、ローン計算を設定するための段階的なガイドです。
重要なポイント
- Excelを使用して、毎月の支払い、利率、ローンスケジュールを決定することで、住宅ローンを処理します。特定のニーズに合わせて微調整できる各ステップで利用可能な計算があります。ステップごとにローンを分解して調べることで、返済プロセスの負担を軽減し、管理しやすくすることができます。
あなたの住宅ローンを理解する
Excelを使用すると、3つの簡単な手順で住宅ローンの理解を深めることができます。 最初のステップは、毎月の支払いを決定します。 2番目のステップは金利を計算し、3番目のステップはローンスケジュールを決定します。
Excelで、金利、ローン期間のローン計算、ローンの分解、償却、毎月の支払いを示すテーブルを作成できます。
毎月の支払いを計算する
まず、住宅ローンの毎月の支払額を計算する方法です。 年利、元本、期間を使用して、毎月返済する金額を決定できます。
上記のスクリーンショットに示すように、式は次のように記述されます。
= -PMT（rate; length; present_value;;）
式が負の数を返すため、PMTの前にマイナス記号が必要です。 最初の3つの引数は、ローンの利率、ローンの期間（期間の数）、および借りた元本です。 最後の2つの引数はオプションであり、残余値のデフォルトはゼロです。 前払い（1回）または最後（0回）の支払いもオプションです。
ローンの毎月の支払いを計算するために使用されるExcel式は次のとおりです。
= -PMT（（1 + B2）^（1/12）-1; B4 * 12; B3）= PMT（（1 + 3, 10％）^（1/12）-1; 10 * 12; 120000）
説明：レートについては、月間レート（レート期間）を使用し、期間数（10年間で120倍に12ヶ月を掛けた値）を計算し、最後に借り入れた元本を示します。 私たちの毎月の支払いは10年間で1, 161.88ドルになります。
年利を計算する
住宅ローンの毎月の支払いの計算を設定する方法を見てきました。 ただし、ローンの返済が必要となる年数も表示する余裕のある最大月額支払いを設定することもできます。 そのため、対応する年利を知りたいと思います。
上記のスクリーンショットに示すように、まず期間料金（この場合は月次）を計算し、次に年次料金を計算します。 上記のスクリーンショットに示すように、使用される式はRATEです。 次のように書かれています。
= RATE（Nper; pmt; present_value;;）
最初の3つの引数は、ローンの長さ（期間の数）、ローンを返済するための毎月の支払い、および借りた元本です。 最後の3つの引数はオプションであり、残差値はデフォルトでゼロになります。 事前に（1つ）または最後に（ゼロ）成熟度を管理するための用語引数もオプションです。 最後に、推定引数はオプションですが、レートの初期推定を提供できます。
貸出率の計算に使用されるExcel式は次のとおりです。
= RATE（12 * B4; -B2; B3）= RATE（12 * 13; -960; 120000）
注：毎月の支払いの対応するデータには、マイナス記号を付ける必要があります。 これが、式の前にマイナス記号がある理由です。 レート期間は0.294％です。
式 =（1 + B5）は12-1 ^ =（1 + 0.294％）^ 12-1 を使用して、ローンの年利3.58％を取得します。 つまり、13年間で120, 000ドルを借りて毎月960ドルを支払うには、年間3.58％の最大金利でローンを交渉する必要があります。
Excelを使用することは、借りているものを追跡し、最終的に支払うべき料金を最小限に抑える返済のスケジュールを立てる素晴らしい方法です。
ローンの長さの決定
年間利率、借りた元本、返済する月額の支払いがわかっている場合に、ローンの長さを決定する方法について説明します。 言い換えると、3.10％のレートで毎月1, 100ドルの支払いで120, 000ドルの住宅ローンを返済するのにどれくらいの時間が必要でしょうか？
上記のスクリーンショットに示すように、使用する式はNPERであり、次のように記述されています。
= NPER（rate; pmt; present_value;;）
最初の3つの引数は、ローンの年間利率、ローンの返済に必要な毎月の支払い、および借りた元本です。 最後の2つの引数はオプションであり、残差値はデフォルトでゼロになります。 前払い（1回）または最後（0回）に支払うべき引数引数もオプションです。
= NPER（（1 + B2）^（1/12）-1; -B4; B3）= NPER（（1 + 3, 10％）^（1/12）-1; -1100; 120000）
注：毎月の支払いの対応するデータには、マイナス記号を付ける必要があります。 これが、式の前にマイナス記号がある理由です。 払い戻しの長さは127.97期間（この場合は月）です。
ローンの返済を完了するには、式= B5 / 12 = 127.97 / 12を年数に使用します。 言い換えれば、年率3.10％で120, 000ドルを借り、月額1, 100ドルを支払うには、128か月または10年8か月の満期を返済する必要があります。
ローンの分解
ローンの支払いは、元本と利息で構成されます。 利息は期間ごとに計算されます。たとえば、10年にわたる月ごとの返済額は120期間になります。
上記の表は、PPMTおよびIPMTの式を使用したローンの内訳（合計期間が120に等しい）を示しています。 2つの式の引数は同じであり、次のように分類されます。
= -PPMT（rate; num_period; length; principal;;）
引数は、「num_period」を除いて、すでに見たPMT式と同じです。「num_period」は、元本と利子が与えられた場合にローンを分割する期間を示すために追加されます。 次に例を示します。
= -PPMT（（1 + B2）^（1/12）-1; 1; B4 * 12; B3）= PPMT（（1 + 3, 10％）^（1/12）-1; 1; 10 * 12; 120000）
結果は、分析された期間の「ローン分解」の上のスクリーンショットに示されています。これは「1」です。 つまり、最初の期間または最初の月です。 1, 161.88ドルを元金856.20ドルと利子305.68ドルに分けて支払います。
Excelでのローン計算
また、最初の12か月や最初の15か月など、いくつかの期間の元利金の返済を計算することもできます。
= -CUMPRINC（レート;長さ;プリンシパル;開始日;終了日;タイプ）
式PMTの最初の部分ですでに見た引数、レート、長さ、プリンシパル、および用語（必須）を見つけます。 ただし、ここでは、「start_date」および「end_date」引数も必要です。 「start_date」は分析する期間の開始を示し、「end_date」は分析する期間の終了を示します。
次に例を示します。
= -CUMPRINC（（1 + B2）^（1/12）-1; B4 * 12; B3; 1; 12; 0）
結果はスクリーンショット「Cumul 1st year」に示されているため、分析される期間は、最初の期間（最初の月）の1〜12から12番目（12番目の月）の範囲です。 1年かけて、元本で10, 419.55ドル、利息で3, 522.99ドルを支払います。
ローンの償却
前の公式により、期間ごとにスケジュールを作成し、元本と利息について月々支払う金額を知ることができ、支払うべき残額を知ることができます。
ローンスケジュールの作成
ローンスケジュールを作成するには、上記で説明したさまざまな式を使用し、期間の数に合わせて展開します。
最初の期間列で、最初の期間として「1」を入力し、セルを下にドラッグします。 この場合、10年のローン支払いに12か月を掛けたものが120に等しいため、120期間が必要です。
2番目の列は、毎月支払う必要がある月額です。これは、ローンスケジュール全体にわたって一定です。 金額を計算するには、最初の期間のセルに次の数式を挿入します。
= -PMT（TP-1; B4 * 12; B3）= -PMT（（1 + 3, 10％）^（1/12）-1; 10 * 12; 120000）
3番目の列は、毎月返済されるプリンシパルです。 たとえば、第40期については、月額1, 161.88ドルの元本で945.51ドルを返済します。
償還された元本を計算するには、次の式を使用します。
= -PPMT（TP; A18; $ B $ 4 * 12; $ B $ 3）= -PPMT（（1 + 3, 10％）^（1/12）; 1; 10 * 12; 120000）
4番目の列は利息です。これに対して、式を使用して月額の元本返済額を計算し、支払われる利息の量を見つけます。
= -INTPER（TP; A18; $ B $ 4 * 12; $ B $ 3）= -INTPER（（1 + 3, 10％）^（1/12）; 1; 10 * 12; 120000）
5列目には、支払うべき残額が含まれています。 たとえば、40回目の支払いの後、120, 000ドルで83, 994.69ドルを支払う必要があります。
式は次のとおりです。
= $ B $ 3 + CUMPRINC（TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0）
数式は、借りた元本を含むセルの前の期間の元本の組み合わせを使用します。 この期間は、セルをコピーして下にドラッグすると変化し始めます。 次の表は、120期間の終了時にローンが返済されることを示しています。
