ローンおよび支払いモデリング
メニューを表示するにはスワイプしてください
大きな購入は、通常一括払いではなく分割払いで資金調達されます。本章では、Excelの財務関数を使って動的なローン支払いシステムを構築します。
PMT 構造
=PMT(rate, nper, pv, [fv], [type])
rate: 各期間の利率；nper: 総支払期間数；pv: 現在価値またはローン金額；[fv]: オプションの将来価値；[type]: 支払いタイミング。
本章で使用する数式：
=PMT(B3/12,B4,-B2)
B3/12: 月利率；B4: 月数；-B2: ローン金額。
負の値はExcelのキャッシュフロー規則に従い、正の支払額を返します。
IPMT 構造
=IPMT(rate, per, nper, pv, [fv], [type])
rate: 各期間の利率；per: 評価する支払期間；nper: 総支払期間数；pv: ローン金額。
IPMTは特定の支払期間における利息部分を返します。
ステップ1 支払いモデルシートの作成
新しいワークシートを作成し、次の名前を付けます：
Payment_Model
ステップ2 注文金額のリンク
Order Valueセルに次の数式を入力します：
=XLOOKUP(B1,Sales_Data[Order_ID],Sales_Data[Revenue],"Not found")
B1: 選択したOrder_ID；Sales_Data[Order_ID]: 検索列；Sales_Data[Revenue]: 返される注文金額。
ステップ3 モデル入力の定義
次の入力構造を作成します：
| 入力項目 | 値 |
|---|---|
| 年間利率 | 0.08 |
| 期間（月） | 12 |
| Order ID | 1001 |
ステップ4 月々の支払い額の計算
Monthly Paymentセルに次の数式を入力します：
=PMT(B3/12,B4,-B2)
この数式は毎月の固定分割支払額を返します。
ステップ5 元利均等返済表の作成
Period列を次のように作成します：
1 → 12
各行が1つの支払期間を表します。
ステップ6 支払い数式の繰り返し
Payment列に次の数式を入力します：
=PMT($B$3/12,$B$4,-$B$2)
固定支払い額が各期間で繰り返されます。
ステップ7 各期間の利息計算
Interest列に次の数式を入力します：
=IPMT($B$3/12,D2,$B$4,-$B$2)
D2: 現在の支払期間；IPMT(...): その月の利息部分を計算。
この数式を表全体にコピーします。
ステップ8 元金返済額の計算
Principal列に次の数式を入力します：
=E2-F2
E2: 総支払額；F2: 利息部分。
この結果がその期間の元金返済額となります。
ステップ9 総利息の計算
次の数式を入力します：
=SUM(F2:F13)
これによりローン期間全体の総融資コストが算出されます。
ステップ10 モデル感度のテスト
次の項目を変更します：
- 利率；
- ローン期間；
- 注文金額。
以下を確認します：
- 月々の支払いが自動で再計算されること；
- 利息配分が動的に変化すること；
- 総融資コストが自動で更新されること。
1. なぜPMT関数で現在価値を負の数値で入力するのですか？
2. IPMT は何を計算しますか？
3. なぜ元利均等返済表において利息部分は時間とともに減少するのですか？
すべて明確でしたか？
フィードバックありがとうございます！
セクション 1. 章 3
AIに質問する
AIに質問する
何でも質問するか、提案された質問の1つを試してチャットを始めてください
セクション 1. 章 3
