エクセルで償却スケジュールを作成する方法

償却スケジュールは、固定金利ローンに適用される利子と支払いによって元本がどのように減少するかを示しています。また、すべての支払いの詳細なスケジュールが表示されますので、どのくらいの元本に使用され、どのくらいの金利手数料に支払われているかを確認することができます。このWikiHowでは、Microsoft Excelで独自の償却スケジュールを作成する方法を説明します。

方法1

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

1. Microsoft Excelで新しいスプレッドシートを開きます。
2. 最初の列にデータのラベルを作成し、整理します。各セルに記入する内容は以下の通りです。

   • A1: ローン額
   • A2: 金利
   • A3: 月数
   • A4: 支払額
3. B列にローンに関する情報を入力します。セルB1～B3にローンに関する情報を記入します。B4（「Payments」ラベルの隣のセル）は空欄のままにしてください。

   • Months "の値は、ローン期間の合計月数とします。例えば、2年ローンの場合は24と入力します。
   • Interest Rate "の値はパーセンテージ（例：8.2%）にしてください。
4. セルB4で支払額を計算します。これを行うには、セル B4 をクリックし、シート上部の数式 (fx) バーに次の数式を入力し、ȕ Enter または ⏎ Return を押します: =ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)。

   • 数式内のドル記号は絶対参照で、ワークシートの他の場所にコピーされても、数式が常に特定のセルを参照するようにします。
   • ローン金利は毎月計算される年率なので、12で割る必要があります。
   • 例えば、$150,000 のローンを 6 パーセントの金利で 30 年（360 ヶ月）借りた場合、ローンの支払いは $899.33 となります。
5. 7行目に列見出しを作成します。シートにデータを追加するので、2つ目のグラフ領域が必要です。以下のラベルをセルに入力する：

   • A7: 期間
   • B7: 期首残高
   • C7:支払額
   • D7：元金
   • E7: 利息
   • F7: 元金累計
   • G7: 累積利息
   • H7: 期末残高
6. Period 列を入力します。この列には支払日が含まれます。以下のようにします：

   • セル A8 に最初のローン支払日の月と年を入力します。月と年を正しく表示するために、列をフォーマットする必要があるかもしれません。
   • セルを一度クリックして選択します。
   • 選択したセルの中央から下へドラッグして、A367 までのすべてのセルをカバーします。これですべてのセルに毎月の支払日が正しく反映されない場合は、一番下のセルの右下にある稲妻のついた小さなアイコンをクリックし、「先月」オプションが選択されていることを確認します。
7. セルB8からH8にその他の項目を入力します。

   • セルB8にローンの開始残高を入力します。
   • セル C8 に =$B$4 と入力し、Enter または Return キーを押します。
   • セル E8 に、その期間の期首残高に対するローン利息額を計算する数式を作成します。数式は =ROUND($B8*($B$2/12), 2) のようになります。ドル記号 1 つで相対参照が作成されます。数式は、B 列の適切なセルを探します。
   • セル D8 で、セル C8 の支払総額からセル E8 のローン利息額を引きます。このセルが正しくコピーされるように、相対参照を使用します。数式は =$C8-$E8 のようになります。
   • セル H8 で、その期間の期首残高から支払いの元金部分を引く数式を作成します。数式は =$B8-$D8 のようになります。
8. B9 から H9 に項目を作成して、スケジュールを続けます。

   • セル B9 には、前期の期末残高を相対参照します。B9に=$H8と入力し、EnterまたはReturnを押す。
   • セルC8、D8、E8をコピーし、それぞれC9、D9、E9に貼り付ける。
   • H8をコピーしてH9に貼り付ける。ここで相対参照が役に立つ。
   • セル F9 に、累計支払元本を集計する数式を作成する。計算式は次のようになります： =$D9+$F8.
   • 累積利息の計算式を G9 に次のように入力する： =$E9+$G8.
9. セル B9 から H9 をハイライトする。マウス・カーソルを強調表示部分の右下に置くと、カーソルが十字に変わる。
10. 十字線を367行目までドラッグする。これで367行目までのすべてのセルに償却スケジュールが入力される。

    • これがおかしい場合は、最後のセルの右下にある小さなスプレッドシートのようなアイコンをクリックし、「セルをコピー」を選択します。

方法2

エクセルのテンプレートを使う

1. にアクセスしてください。 これは無料でダウンロードできる償却スケジュールのテンプレートで、総利息と総支払額を簡単に計算できます。余分な支払いを追加するオプションも含まれています。
2. ダウンロードをクリックする。テンプレートがExcelテンプレート形式（XLTX）でコンピュータに保存されます。
3. ダウンロードしたファイルをダブルクリックします。ファイル名はtf03986974.xltxで、通常はダウンロードフォルダにあります。Microsoft Excelでテンプレートが開きます。

   • テンプレート内のデータは一例であり、あなた自身のデータを追加することができます。
   • プロンプトが表示されたら、編集を有効にするをクリックし、ワークブックに変更を加えることができるようにします。
4. ローン金額」のセルにローン金額を入力します。シートの左上隅にある "ENTER VALUES "セクションにあります。入力するには、既存の値（5000ドル）をクリックし、自分の金額を入力します。

   • Return ⏎ または Enter ↵ を押すと (または別のセルをクリックすると)、シートの残りの部分の金額が再計算されます。これは、このセクションの値を変更するたびに発生します。
5. 年利率を入力します。年利率」のセルに入力します。
6. ローンの期間（年）を入力します。これは「ローン期間（年）」セルに入ります。
7. 年間の支払い回数を入力します。例えば、毎月1回支払う場合は、「年間の支払い回数」のセルに12回と入力します。
8. ローン開始日を入力します。これは「ローン開始日」のセルに入力します。
9. Optional extra payments "の値を入力します。各支払期日にローンの最低支払額を超えて支払う場合は、その超過額をこのセルに入力します。そうでない場合は、デフォルト値を0（ゼロ）に変更してください。
10. ローン発行会社名を入力します。LENDER NAME "のデフォルト値は "Woodgrove Bank "です。参照用に銀行名に変更してください。
11. ワークシートを新しいエクセル・ファイルとして保存します。方法は以下の通りです：

    • 左上の「ファイル」メニューをクリックし、「名前を付けて保存」を選択します。
    • スケジュールを保存するコンピュータまたはクラウドの場所を選択します。
    • ファイル名を入力します。ファイルタイプがまだ "Excel Workbook (*.xlsx) "に設定されていない場合は、ドロップダウンメニュー（ファイル名の下）からそのオプションを選択します。
    • 保存をクリックする。

この記事は、CC BY-NC-SAの下で公開されている「 How to Prepare Amortization Schedule in Excel 」を改変して作成しました。特に断りのない限り、CC BY-NC-SAの下で利用可能です。