ExcelのPMT、IPMT、およびIFの式を使用して、債務スケジュールを作成できます。まず、いくつかの債務の仮定を入力してモデルを設定する必要があります。この例では、負債を5,000,000ドル、支払い期間を5年、金利を想定しています。金利金利とは、一般に次のように表される、与えられたあらゆる形式の負債に対して貸し手が借り手に請求する金額を指します。元本のパーセンテージ。4.5％になります。
1.債務スケジュールの開始残高は500万ドルのローン金額に等しいため、セルE29に= B25と入力して、仮定の入力にリンクします。次に、PMT式を使用して、最初の期間の合計支払い額= PMT（$ B $ 27、$ B $ 26、$ B $ 25）を計算できます。この式は、仮定のセクションに記載されているローン金額、期間、および金利を使用して支払い金額を計算します。
2.セルE28に、現在の期間（1）を入力します。セルE29に、= E28 + 1と入力し、右の数式を入力します。次に、IPMT式を使用して、最初の期間の利息支払い= IPMT（$ B $ 27、E28、$ B $ 26、$ B $ 25）を見つけます。
3.元金は、合計支払いと利息支払いの差であり、= E30-E31です。期末残高は、期首残高に元本の支払いを加えたもので、= E29 + E32です。期間2の期首残高は、期間1の期末残高= E33です。
4.セルE29からE33までのすべての数式を次の列にコピーしてから、すべてを右側にコピーします。期間5 = 0の決算残高をチェックして、正しい数式と数値が使用されていることを確認します。
5.期首残高が0であるため、期間6から始まるエラーメッセージがいくつかあることに注意してください。ここでは、IF関数を使用してエラーをクリーンアップできます。セルE30に、= IF（E29> 0、PMT（$ B $ 27、$ B $ 26、$ B $ 25）、0）と入力します。この式では、期首残高が0未満の場合、支払い総額は0として表示されます。
6.セル31に、= IF（E29> 0、IPMT（$ B $ 27、E28、$ B $ 26、$ B $ 25）、0）と入力します。この式は前の式と似ており、期首残高が0未満の場合、利息の支払いは0として表示されます。
7.セルE30とE31をコピーし、Shift +右矢印を押してから、CTRL + Rを押して右に塗りつぶします。すべてのエラーメッセージが0として表示されていることがわかります。
XNPVDATEおよびIF関数を使用したXIRR
Excel関数XNPVおよびXIRRとDATEおよびIF関数を使用して、特定の日付に基づいてNPVおよびIRRを計算できます。
8.セルE6に移動し、= DATE（E5,12,31）と入力して、日付を表示します。右にコピーします。#VALUEが表示されます！2021年以降のメッセージ。IFERROR関数= IFERROR（DATE（E5,12,31）、””）を使用してこれを修正できます。
9.これで、NPVとIRRの計算を開始できます。まず、フリーキャッシュフローの金額を入力する必要があります。期間1から5までのFCF金額は-1,000、500、600、700、900であると想定しています。セルC37には、15％の割引率を入力します。セルB37で、XNPV数式= XNPV（C37、E35：I35、E6：I6）を使用してNPVを計算します。
10.セルB38で、XIRR数式= XIRR（E35：I35、E6：I6）を使用してIRRを計算します。
XNPVおよびXIRRへのOFFSETの追加
XNPV式とXIRR式に変更して、OFFSET関数を使用してより動的な式を作成できます。
11.セルB42で、数式を= XNPV（C42、E40：OFFSET（E40,0、$ F $ 3-1）、E6：I6）に変更します。期間の数が増えると、フリーキャッシュフローの期間も増えるため、式はより動的になります。予測期間が長い場合は、NPV式を変更する必要はありません。IRR関数の場合は、= XIRR（E40：OFFSET（E40,0、$ F $ 3-1）、E6：I6）に変更します。
12.期間数の数式を調整した後、日付をオフセットする必要があります。セルB42で、数式を= XNPV（C42、E40：OFFSET（E40,0、$ F $ 3-1）、E6：OFFSET（E6,0、$ F $ 3-1））に変更します。これにより、NPVおよびIRRの式は、期間数の変化に応じて適切な数のフリーキャッシュフローを取得できます。
主要な債務スケジュール式の要約
- 債務返済額を計算するためのPMT式：= PMT（利率、期間数、現在価値）
- 利払いを計算するためのIPMT式：= IPMT（利率、期間、期間数、現在価値）
- 正味現在価値を見つけるためのXNPV式：= XNPV（割引率、フリーキャッシュフロー、日付）
- 内部収益率を求めるためのXIRR式：= XIRR（フリーキャッシュフロー、日付）
- 動的NPVを計算するためのOFFSET式：= XNPV（割引率、1番目のFCF：OFFSET（1番目のFCF、0、＃期間– 1）、1番目の日付：OFFSET（最初の日付、0、＃期間– 1））
- 動的IRRを計算するためのOFFSET式：= XIRR（1st FCF：OFFSET（1st FCF、0、＃periods – 1）、1st date：OFFSET（1st date、0、＃periods – 1））
その他のリソース
PMT、IPMT、IFの公式を使用した債務スケジュールに関する財務ガイドをお読みいただきありがとうございます。あなたのキャリアを学び、前進させ続けるために、以下の財務リソースが役に立ちます：
- 基本的なExcelの数式基本的なExcelの数式基本的なExcelの数式を習得することは、初心者が財務分析に習熟するために重要です。Microsoft Excelは、データ分析における業界標準のソフトウェアと見なされています。マイクロソフトのスプレッドシートプログラムも、投資銀行家が最も好むソフトウェアの1つです。
- 財務モデリングのベストプラクティス財務モデリングのベストプラクティスこの記事は、財務モデリングのベストプラクティスに関する情報と、財務モデルを構築するための簡単なステップバイステップガイドを読者に提供することを目的としています。
- Excel関数関数のリスト金融アナリストにとって最も重要なExcel関数のリスト。このチートシートは、Excelアナリストとして知っておくべき何百もの機能をカバーしています。
- Excelショートカットの概要Excelショートカットの概要Excelショートカットは、Excel内の生産性と速度を向上させるための見過ごされている方法です。Excelショートカットは、金融アナリストに強力なツールを提供します。これらのショートカットは多くの機能を実行できます。スプレッドシート内のナビゲーションから数式の入力やデータのグループ化まで簡単です。