Loan Repayment & EMI Model
Build a transparent loan model with EMI, interest/principal split, balance movement and scenario testing.
Advanced · 90–120 min
Objectives
- Calculate EMI with PMT
- Split interest and principal
- Build an amortization schedule
- Run rate/tenure scenarios
- Reconcile ending balance
Project dataset
| Row | A: Input | B: Value |
|---|---|---|
| 2 | Loan Amount | 1000000 |
| 3 | Annual Rate | 10% |
| 4 | Tenure Months | 60 |
| 5 | Start Date | 2026-10-01 |
Guided checkpoints
Build the EMI formula
Use PMT with monthly rate and monthly tenure.
Calculate a periodic loan payment.
Split period 1
Use IPMT and PPMT for the first payment period.
Separate interest from principal.
Build the balance schedule
Create period, opening balance, payment, interest, principal and closing balance columns.
Create an auditable amortization table.
Test a rate scenario
Increase the annual rate and confirm EMI changes automatically.
Use assumptions instead of hard-coded outputs.
Test a tenure scenario
Extend tenure and compare EMI and total interest.
Explain the payment vs cost tradeoff.
Reconcile the model
Confirm principal repaid approximates the original loan and ending balance approaches zero.
Perform financial-model QA.
Download sample project .xlsx · All portfolio projects
Interactive practice runs in your browser.