Bank Loan Model
Banking Financial Model (Free Excel Download)
Analyze loan repayment schedules, interest expense, debt service coverage, refinancing needs, covenant headroom, and lender cash flow across the facility life.
professionals from Deloitte
Used by professionals from
About this model
A bank loan analysis model evaluates a commercial borrower's ability to service debt by projecting cash flows, calculating debt service coverage ratios (DSCR), monitoring covenant compliance (leverage, interest coverage, liquidity), and stress testing downside scenarios on revenue and profitability. The model answers whether the loan officer should approve the credit request and what covenant thresholds and pricing adjustments are justified.
The revenue and EBITDA forecast is built from the borrower's historical financials and growth assumptions, then debt service capacity is calculated as EBITDA less maintenance capex, taxes, and working capital changes. DSCR = distributable cash / total debt service (interest + principal). Covenants are typically financial (maximum leverage ratio, minimum interest coverage) and operational (minimum cash balances, asset sales restrictions). For secured loans, loan-to-value (LTV) ratios are calculated against collateral appraisals. The model includes waterfall analysis showing the priority of debt service (senior secured first, then junior), and multiple stress scenarios (base, downside revenue/EBITDA, macroeconomic stress) to confirm DSCR never falls below lender minimum (typically 1.25–1.50x for commercial loans).
Commercial lenders, credit committees, and loan servicers use underwriting models to size facility structures, set pricing (spread above base rate) proportional to risk, establish early warning thresholds on covenant ratios, and determine required collateral and guarantees.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Bank Loan Model
- Loan sizing and amortisation schedule
- Interest schedule and debt balance roll-forward
- Repayment scenario analysis
- Covenant tracking and credit headroom visibility
- Borrower financial statements with revenue, EBITDA, and free cash flow
- Loan structure: term, rate, fees, covenants, and amortization
- Debt service coverage ratio (DSCR) and debt-to-equity analysis
- Loan-to-value (LTV) for secured loans with collateral valuation
Bank Loan Model: How the Two-Tranche Structure Works
This bank loan model captures a two-tranche capital structure with a Term Loan and Revolver, designed for credit analysts stress-testing real credit documentation. It links borrower operating drivers to debt service, covenant tests, and a summary of all-in economics.
The explanation below covers the operating drivers, calculation flow, outputs, and practical use for evaluating the template.
Operating Drivers and Loan Assumptions
The model begins on the Assumptions tab, where the Term Loan inputs cover amount, fixed rate, term, interest-only period, amortisation method, balloon percentage, origination fee, service fee, and PIK percentage. The Revolver block holds commitment, rate, commitment fee, and a per-period utilisation vector.
- A separate rate structure allows fixed or floating rates with credit spread, cap, and floor, alongside a per-period base rate vector. Drawdown follows a schedule with a sum validator that flags over 100 percent utilisation.
- Borrower financials include base revenue, revenue growth, COGS percentage, OpEx percentages with a margin ramp, capex percentage, useful life, and tax rate, so operating performance drives the credit profile rather than sitting separately.
How the Calculation Flow Connects
The Term Loan opening balance carries prior-period closing, including PIK accretion. Interest is split into cash and PIK portions, with PIK capitalised into the closing balance.
- Principal follows either straight-line or annuity logic, locked at the interest-only end to avoid double-paying a balloon. A cash sweep applies an effective rate from the ratchet, which uses prior-period leverage to break the circularity with CFADS.
- Prepayment penalties follow a step-down schedule. The Revolver targets an outstanding balance from the utilisation vector, drawing or repaying to reach it, with interest on average balance and commitment fees on undrawn amounts.
Combined debt lines feed the Debt_Service sheet, where revenue flows through COGS, gross profit, ramped OpEx, EBITDA, capex, D&A, and EBIT.
Covenant Testing and Equity Cure Mechanics
Covenants on the Debt_Service sheet include DSCR as CFADS divided by total debt service, ICR as EBITDA divided by total interest, and leverage as combined closing debt divided by EBITDA. Each receives a pass or fail check with conditional formatting.
- When DSCR falls below the minimum, an equity cure can be applied, calculated as the shortfall between the minimum DSCR requirement and available CFADS, capped at a percentage of EBITDA. The cure is gated by a trailing five-period count so it cannot be used more than the specified maximum.
- A post-cure DSCR is then computed, and this feeds the breach summary on the Summary tab, which reports the breach count and first-breach year using the post-cure figure.
Outputs and Practical Use
The Summary tab consolidates a loan overview, cost of borrowing, covenant summary, all-in economics, breach summary, tax shield, and a sensitivity grid. All-in borrower cost is total interest and fees divided by the sum of opening and drawn balances, giving a weighted-average outstanding yield that exceeds coupon when fees are positive.
- Lender yield and IRR are also shown, with the IRR cash flow netting the origination fee at time zero. The tax shield totals combined interest times the tax rate, discounted at the term loan rate.
- The sensitivity grid is a closed-form 5x5 approximation on term loan rate and revenue growth, not a live recalculation. This structure suits evaluating repayment capacity, covenant headroom, and refinancing outcomes under the documented assumptions.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What does a bank loan model show?+
It shows repayment, interest, debt balances, and often covenant metrics over the life of a facility.
Who uses bank loan models?+
Borrowers, lenders, finance teams, and advisers use them to plan and monitor debt.
What should a bank loan model include?+
It should include amortisation, interest expense, principal repayment, debt balances, and any key covenant tests or affordability metrics.
Can it be used for refinancing analysis?+
Yes. A bank loan model can help compare repayment structures, interest assumptions, and covenant headroom.
Why does repayment visibility matter?+
Because the timing of interest and principal payments affects both affordability and liquidity planning.
Have more financial modelling questions? Contact us
Related templates
Bank Capital Adequacy Model
Regulatory capital calculations and stress testing for banks.
Syndicated Loan Underwriting
Model syndicated loan terms, pricing, club structure, and lender commitments with covenant tracking.
Distressed Debt Analysis
Valuation and recovery analysis for distressed bonds and loans with recovery timeline and outcomes.
Credit Card Portfolio Model
Model credit card loan losses, interest revenue, and regulatory capital requirements.
