Loan and Payment Modeling
Swipe to show menu
Large purchases are often financed through installments instead of single payments. In this chapter, build a dynamic loan payment system using Excel financial functions.
PMT Structure
=PMT(rate, nper, pv, [fv], [type])
rate: interest rate per period;nper: total number of periods;pv: present value or loan amount;[fv]: optional future value;[type]: payment timing.
Formula used in this chapter:
=PMT(B3/12,B4,-B2)
B3/12: monthly interest rate;B4: number of months;-B2: loan amount.
The negative value follows Excel cash flow conventions and returns a positive payment result.
IPMT Structure
=IPMT(rate, per, nper, pv, [fv], [type])
rate: interest rate per period;per: evaluated payment period;nper: total number of periods;pv: loan amount.
IPMT returns the interest portion of a specific payment period.
Create a new worksheet named:
Payment_Model
Inside the Order Value cell, type:
=XLOOKUP(B1,Sales_Data[Order_ID],Sales_Data[Revenue],"Not found")
B1: selected Order_ID;Sales_Data[Order_ID]: lookup column;Sales_Data[Revenue]: returned order value.
Create the following input structure:
| Input | Value |
|---|---|
| Annual Interest Rate | 0.08 |
| Term (months) | 12 |
| Order ID | 1001 |
Inside the Monthly Payment cell, type:
=PMT(B3/12,B4,-B2)
The formula returns the fixed monthly installment amount.
Create a Period column from:
1 → 12
Each row now represents one payment period.
Inside the Payment column, type:
=PMT($B$3/12,$B$4,-$B$2)
The fixed payment repeats across every period.
Inside the Interest column, type:
=IPMT($B$3/12,D2,$B$4,-$B$2)
D2: current payment period;IPMT(...): calculates the interest portion for that month.
Fill the formula down the schedule.
Inside the Principal column, type:
=E2-F2
E2: total payment;F2: interest portion.
The result represents the principal repayment for the period.
Type:
=SUM(F2:F13)
This returns the total financing cost across the loan term.
Change:
- Interest rate;
- Loan term;
- Order value.
Confirm that:
- Monthly payments recalculate automatically;
- Interest allocation changes dynamically;
- Total financing cost updates automatically.
1. Why is the present value entered as a negative number in the PMT function?
2. What does IPMT calculate?
3. Why does the interest portion decrease over time in an amortization schedule?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
