Excelを使用してローン返済額を計算できることをご存知ですか？この記事では、必要なすべての手順を順を追って説明します。 （ 住宅ローン電卓：彼らの仕組み 。）

Excelを使用すると、3つの簡単なステップで住宅ローンをよりよく理解することができます。最初のステップは、毎月の支払いを決定することです。 2番目は金利を発見すること、3番目は貸出スケジュールを見つけることです。これを行うには、Excelでテーブルを作成します。このテーブルには、以下の情報が表示されます。その期間のローン計算。ローンの分解、月額賃料の償却や計算などが含まれます。

<！ - 1 - >

月額賃貸料の貸方計算

まず、モーゲージの月額支払いの計算方法を見てみましょう。言い換えれば、毎年の利率、元本、期間を使用して、毎月返済される金額を決定することができます。

上のスクリーンショットに示すように、この式は次のように書かれています。

<！式が負の数を返すため、PMTの前のマイナス記号が必要です。最初の3つの引数は、ローンの金利、ローンの期間（期間の数）、および借りた元本です。最後の2つの引数はオプションで、残りの値はデフォルトで0に設定され、事前に（1の場合）または最後に（0の場合）、オプションです。

<！ （1 + B2）^（1/12）-1; B4 * 12; B3）* 999は、ローンの月額支払いを計算するために使用されるExcel式です。解説：レートの期間、すなわち月次レートを使用したレートについては、計算します（たとえば、期間の数（12ヶ月を掛けた10年の120ヶ月の月）と最後に、借りた元本を示します。私たちの毎月の支払いは$ 1、161. 88年になるでしょう。

金利のモーゲージ計算

モーゲージの月額支払いの計算方法を設定しました。しかし、私たちはそれを返済しなければならない年数を表示する余裕がある最大毎月支払額を設定したいかもしれません。そのため、対応する年間利率を知りたい。

貸出金利の計算

上のスクリーンショットに示すように、まず期間レート（この場合は月次）を計算し、次に年間レートを計算します。

= RATE（Nper; pmt; present_value; [future_value]; [type]）

最初の3つの引数は、次のように書かれています。ローン（期間数）、および借りた元本返済のための月額支払い。最後の3つの引数は省略可能であり、残りの値はデフォルトで0に設定されています。事前に成熟度を管理するためのterm引数（1の場合）または終わり（0の場合）もオプションです。率の初期評価を与える。

貸出金利の計算に使用されるExcelの計算式は次のとおりです。

= RATE（12 * B4; -B2; B3）= RATE（12 * 13; -960; 120000）毎月の支払いに負の符号を付ける必要があります。これは、式の前にマイナス記号がある理由です。当社のレート期間は0.24％です。

我々のローンの年利を3にするために、式999 =（1 + B5）= 12-1 ^ =（1 + 0.294％）^ 12-1 999を使用します。58 ％。言い換えれば、毎月960ドルを支払うために13年以上120,000ドルを借りるには、年間3. 58％の最高料金で融資を交渉すべきです。

ローンの長さに対するモーゲージ計算

今年は、年利、借りた元本、返済される月額支払いを知ったときにローンの長さを取得する方法を見ていきます。言い換えれば、どのくらいの期間、私たちは120,000ドルの抵当権を3％の率で、10％の月々の支払いを1ドル100で返済する必要がありますか？

融資の返済回数

上のスクリーンショットに示すように、使用する公式はNPERです。以下のように書かれています。

= NPER（レート; pmt;現在の値; [将来の値]; [タイプ]）

最初の3つの議論は、ローンの年利、ローンを返済するために必要な毎月の支払い、および借りられた元本です。最後の2つの引数はオプションで、残りの値はデフォルトで0になります。事前に（1の場合）または最後の（0の場合）支払可能な引数もオプションです。 NPER（（1 + 3、10％）^（1/12）-1; -1100; 120000）= NPER（（1 + B2）^（1/12）-1; -B4; B3） 注：毎月の支払いに対応するデータにはマイナス記号を付ける必要があります。これは、式の前にマイナス記号がある理由です。払い戻しの期間は127. 97期間（私たちの場合は月）です。 ローン返済を完了する年数は、式= B5 / 12 = 127. 97/12を使用します。言い換えれば、年間金利3.10％で120,000ドルを借り、毎月100ドルを支払うには、128ヶ月または10年8ヶ月間満期を返済しなければなりません。

融資の解約

融資の支払は、元金と利子の2つから成ります。利息は各期間に計算されます。例えば、10年間の毎月の払い戻しは、120期間を与えます。

上のスクリーンショットは、PPMTとIPMT式を使用したローンの内訳（合計期間は120）を示しています。 2つの式の引数は同じであり、以下のように分解されます。

= - PPMT（レート; num_period;長さ;プリンシパル; [残余]; [terme]）

= - INTPER（レート;引数は、最初の部分で見られるPMT式と同じですが、num_periodは、ローンを分割する期間を示すために追加されます。それに対する元本および利息。例を見てみましょう：PPMT（（1 + B2）^（1/12）-1; 1; B4 * 12; B3）= PPMT（（1 + 3、10％）^ （1 + B2）^（1/12）-1; 1; B4 * 12; B3）= INTPER（（1 + 3,10）-1; 1; 10 * 12; 120000）結果は分析された期間のスクリーンショット「Loan Decomposition」に示されているものであり、これは「1」であるため、第1の期間（「1」） 、または最初の1か月。これについては、1161ドルを支払う。 88ドル856ドル、プリンシパル20ドル305ドル。 68関心。

Excelローン計算

最初の12ヶ月または最初の15ヶ月など、いくつかの期間の元利返済額を計算することもできます。 （999）= - CUMIPMT（レート;長さ;元;開始日;終了日;タイプ）

最初の部分ですでに公式PMTで見た、元と期間（必須）です。しかしここではstart_dateとend_dateの引数も必要です。最初のものは分析される期間の開始を示し、2番目のものは終了を示します。例を見てみましょう：CUMPRINC（（1 + B2）^（1/12）-1; B4 * 12; B3; 1; 12; 0）999 = - CUMPRINC（（1 + 3、 B4 * 12; B3 *（1 + B2）^（1/12）-1; B4 * 12; B3 * ; 1; 12; 0）; - CUMIPMT（（1 + 3、10％）^（1/12）-1; 10 * 12; 120000; 1; 12; 0）スクリーンショット「Cumul 1st year」に示されているように、分析された期間は、第1期間（第1月）から第12期間（第12月）の1から12までの範囲である。 1年をかけて、私たちは10ドル419ドル、プリンシパル55ドル、ドル3 522ドル99を支払うでしょう。

融資の償却

以前の公式では、期間別のスケジュール期間、元本および利息で毎月支払う金額、支払う残高の額を作成することができます。

Excelで融資スケジュールを作成する

融資スケジュールを作成するには、上で説明したさまざまな公式を使用し、期間数に応じて拡張します。

最初の期間の列には、最初の期間として「1」と入力し、セルをドラッグします。私たちの場合、10年ローンの支払いに12ヶ月= 120を掛けてから120期間が必要です。

2番目の列は、毎月支払う必要がある毎月の金額で、ローンスケジュール全体で一定です。

= - PMT（TP-1; B4 * 12; B3）= -PMT（（1 + 3、10％）^（1/12 ）-1; 10 * 12; 120000）

第3列は、毎月返済される元本です。たとえば、第40期については、945ドルを返済します。原則として、毎月の総額$ 1,161で51を使用します。88.償還された元本額を計算するには、以下の式を使用しています：

= - PPMT（TP; A18; $ B $ 4 * 12; $ B $ 3）=第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 + 3,10％）^（1/12）; 10 * 12; 120000; 1; 1; 0）の組み合わせを使用する。借り手の元本を含むセルの先にある元本元本。この期間は、セルをコピーしてドラッグすると変化します。下のスクリーンショットは、120期の終わりにローンが返済されることを示しています。