独自のローン償却電卓を作成する方法|

ä¸è¦å²ç¬æåçæ§

ほとんどの人は、 ローンを作成するためのブードゥー・マジック償却スケジュール。しかし、初心者でもMicrosoft Excelを使用して簡単に作成することはできません。

ローン償却表は、貸し手に支払う総額、利払いである部分、元金である部分、残高を表示します。貸出金が償却される場合、単に支払利息と元本支払いが1つの定期的な支払いに組み合わされ、各支払期間に支払額が等しいことを意味するだけである。

Excelは、独自の償却テーブルを計算するために非常に便利なツールです。償却されたローンの最も興味深い機能の1つは、支払ごとに利息の金額と元金が支払われることです。さまざまなインプットを試して、異なる金利、返済期間、元本ローン金額があなたが行うローン支払いにどのような影響を与えるかを見ることもできます。

そのプロセスを説明するための例を見てみましょう。

あなたは4年間で完全に返済されなければならない20,000ドルのローンを持つ車を購入したと仮定します。ローンの年間利率は3％です。ローンは償却されるので、毎月の支払いはすべて同じ金額になります。

最初のステップは、毎月の支払額を計算することです。これは、ExcelでPMT（ "支払い"の略）機能を使用して行うことができます。

新しいスプレッドシートを作成し、支払額計算に必要な次のデータを入力することから始めます。

列Aには、データに対して以下のラベルが含まれます：

A1 =元の元本（元本）

A2 =ローン期間（年）

A3 =年間利率

A4 =年間支払額

A5 =支払い額（当社が計算しているもの）

B1 = $ 20,000

B2 = 4

B3 = 3％

B4 = 12

B5 =探している値

ExcelのPMT機能は、財務機能メニューにあります。最初にセルB5を選択し、数式タブの財務関数メニューをクリックし、リストからPMT関数を選択します。

スプレッドシートからデータを入力する場所が表示されます。

ここで注意すべき重要な点がいくつかあります。まず、

月額

の支払いを計算するため、2つの重要な調整を行う必要があります。毎月の利率（レート）を得るために年率を12で割る必要があり、返済期間（Nper）の合計月数を得るには4×12を掛ける必要があります。

第2に、現在価値（PV）は、貸し手に負担するため、負の値として入力されます。

ボックスがいっぱいになると、「OK」を押します。

スプレッドシートは次のようになります。

償却テーブルの作成を開始します。

パート1で作成したスプレッドシートの下に行をスキップし、

A7 =月

B7 =支払い

C7 =利息

D7 =プリンシパル

E7 =残高

これは、列Aから列Eで終わるラベルです。償却テーブルの5つの見出し。

今、テーブルを埋める時間です。最初のステップは、ローンの生存期間の月数を入力することです。「月」見出しの下に、セルA8（ローンが発行された時刻）に「0」と入力します。番号48（セルA56に配置されます）を介して、順番に次のセルを入力します。 1つのトリック：0を入力したら、セルが選択されていることを確認し（A8）、セルの右下隅を56行目までドラッグします。次に右側に表示される小さなボックスから「fill series」を選択します最後のセル。これにより、48ヶ月間の数値シリーズが自動的に入力されます。

次に、毎月の支払い額がすでに決定されているため、すべての支払いが記入されます。月1から始まります（毎月の終わりに支払いが行われるため、0ヶ月に支払いがありません）。毎月1〜48日に$ 442.69の値を記入してください。その後、それぞれのExcel機能を使用して利息および元本金額を計算することができます。

Excel機能を使用して支払の金利および元本を計算するのは簡単で、償却テーブル全体を記入することができます。利払いを計算するには、セルC9を選択し、数式タブの財務関数メニューをクリックします。一覧からIPMT（利払い）機能を選択します。

注

：セルの文字と数字の間の "$"記号は、コピーするときに必要ですその数式は後で残りのセルにまで下がります。 "$"は参照先のセルをロックするので、残りのセルにコピーすると式は元のままになります。あなたはF4を打つことによってセルをロックすることができます（そして "$"を参照してください）。

次に、支払いの元本額を計算することができます。 IPMTの計算と同様に、セルD9を選択し、数式タブの財務関数メニューをクリックします。リストからPPMT（元本支払い）機能を選択します。スプレッドシートのデータをこのイメージのように入力するボックスが表示されます：

注

：プリンシパルは同様に、PPMT機能を使用してプリンシパルを最初に計算し、その合計額からその値を差し引くと、利子を計算することができますが、この機能を使用して計算することもできます両方の数式を使用する方法を知っているので、以下に示すように残りのテーブルをより効率的に記入することもできます。

最後の列（列E）は残りの残高列です。毎月の利息と元本を決定したので、今月は0で、まだ支払いは行われていないので、残高は2万ドルですが、Excelに各期間の残高を自動的に計算するように指示します。マヌ「$ 20,000」と入力し、上記の支払い計算の表を参照して「= B1」と入力します。 1ヶ月目は、前月の元本払いで前月の残高を差し引いて計算されるため、E9に "= E8-D9"と入力します。残高は自動的に計算されます。償却テーブルは今1ヶ月目に完了し、次のようになります：

残りの月のすべてのデータは、セルB9-E9を選択して選択をコピーし、セルに選択を貼り付けるだけで入力できますB10-E10。それぞれのセルの数式を入力し、すべての計算で使用されるセルをロックしているので、金利、元本および残高の値が自動的に計算されます。その2月の作業を見ると、B-Eから48ヶ月までの列を選択し、既にコピーされた情報を貼り付けるだけで、テーブル全体を完成させることができます。 Excelは、入力した数式を使用して償却テーブルのすべての値を計算しました。完成したテーブルは、次のようになります。

ローンの最初の年：