ローンおよび支払いモデリング
メニューを表示するにはスワイプしてください
高額な購入は、一括払いではなく分割払いで資金調達されることが多くあります。本章では、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_IDSales_Data[Order_ID]: 検索列Sales_Data[Revenue]: 返される注文金額
ステップ3 モデル入力の定義
次の入力構造を作成：
| Input | Value |
|---|---|
| Annual Interest Rate | 0.08 |
| Term (months) | 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. なぜ元利均等返済表において利息部分は時間とともに減少するのですか？
すべて明確でしたか？
フィードバックありがとうございます！
セクション 4. 章 3
AIに質問する
AIに質問する
何でも質問するか、提案された質問の1つを試してチャットを始めてください
セクション 4. 章 3
