# Bank Loan Model Build a bank loan model for repayment planning, debt service visibility, refinancing analysis, and covenant monitoring. - Canonical: https://finamodel.com/templates/bank-loan-model - Excel download: https://finamodel.com/templates/bank-loan.xlsx - Category: Banking - Model type: Underwriting - Difficulty: Intermediate - Audiences: Credit & risk, Founders & operators, Loan officers, Credit analysts, Risk managers, Bank underwriters - Tags: commercial lending, credit analysis, underwriting, DSCR, covenants ## Overview 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's included - 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 - Covenant monitoring: financial covenants (leverage, coverage) and operational metrics - Stress testing with downside revenue and EBITDA scenarios ## 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. ## Built for financing analysis Use this model when you need to understand affordability, repayment timing, covenant pressure, or the impact of changing debt terms. ## Useful for borrowers and lenders A bank loan model helps both sides see how interest, amortisation, and debt service affect cash flow over time. ## Better for refinancing and structuring work This gives you a clearer debt framework than a basic amortisation sheet, especially when loan structure and covenant headroom matter. ## Built for financing analysis Use this model when you need to understand affordability, repayment timing, covenant pressure, or the impact of changing debt terms. ## Useful for borrowers and lenders A bank loan model helps both sides see how interest, amortisation, and debt service affect cash flow over time. ## Better for refinancing and structuring work This gives you a clearer debt framework than a basic amortisation sheet, especially when loan structure and covenant headroom matter. ## Workbook structure ### Loan Inputs This sheet captures facility size, tenor, pricing, and any key covenant assumptions that shape the debt profile. - Facility amount and tenor - Interest rate and fee assumptions - Repayment structure inputs - Covenant and affordability thresholds ### Amortisation The amortisation sheet shows how principal is repaid and how balances decline over the life of the loan. - Opening and closing loan balances - Scheduled amortisation logic - Outstanding balance by period - Repayment visibility over time ### Interest & Debt Service This sheet calculates interest cost and total debt service obligations under the current structure. - Interest expense calculation - Total debt service by period - Cash burden of the facility - Sensitivity to rate or structure changes ### Covenant Output The covenant and output sheet highlights headroom, breach risk, and the practical affordability of the debt package. - Covenant ratio outputs - Headroom visibility - Potential pressure points - Summary view for lenders or management ### Loan Inputs This sheet captures facility size, tenor, pricing, and any key covenant assumptions that shape the debt profile. - Facility amount and tenor - Interest rate and fee assumptions - Repayment structure inputs - Covenant and affordability thresholds ### Amortisation The amortisation sheet shows how principal is repaid and how balances decline over the life of the loan. - Opening and closing loan balances - Scheduled amortisation logic - Outstanding balance by period - Repayment visibility over time ### Interest & Debt Service This sheet calculates interest cost and total debt service obligations under the current structure. - Interest expense calculation - Total debt service by period - Cash burden of the facility - Sensitivity to rate or structure changes ### Covenant Output The covenant and output sheet highlights headroom, breach risk, and the practical affordability of the debt package. - Covenant ratio outputs - Headroom visibility - Potential pressure points - Summary view for lenders or management ## Features - **Covenant and early warning dashboard:** Track financial and operating covenants quarterly; flag violations or deterioration before they escalate. - **Industry-specific underwriting:** Adjust DSCR targets, collateral haircuts, and covenant tightness based on borrower industry and cycle. - **Cash flow waterfall:** Model cash sources and uses to verify that operating cash flow supports debt service, capex, and working capital needs. ## Use cases - **Loan approval and pricing:** Determine appropriate rate, fees, and terms based on borrower credit strength, collateral, and risk-adjusted pricing. - **Portfolio monitoring:** Track covenant compliance and early warning metrics on a quarterly basis to identify deterioration early. - **Loan modification and workout:** Model covenant waiver, rate reduction, or term extension scenarios for borrowers in distress. ## Frequently asked questions ### 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. ## Related templates - [Bank Capital Adequacy Model](https://finamodel.com/templates/bank-capital-adequacy-model) - [Syndicated Loan Underwriting](https://finamodel.com/templates/syndicated-loan-model) - [Distressed Debt Analysis](https://finamodel.com/templates/distressed-debt-model)