ローンおよび支払いモデリング
メニューを表示するにはスワイプしてください
大きな購入は、一括払いではなく分割払いで資金調達されることが一般的です。本章では、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. 元利均等返済表において、なぜ利息部分は時間とともに減少しますか？
すべて明確でしたか？
フィードバックありがとうございます！
セクション 1. 章 20
AIに質問する
AIに質問する
何でも質問するか、提案された質問の1つを試してチャットを始めてください
セクション 1. 章 20
