How to Build a Financial Model for a Bank Loan Application in India

As an entrepreneur or small business owner in India, securing a bank loan is often essential for growth, expansion, or managing working capital. However, approaching a bank without a clear financial roadmap can be challenging. Banks require a comprehensive understanding of your business's financial health and its future potential. This is precisely why you need a robust financial model for a bank loan application.

When your business needs funding and you approach a bank, they will invariably ask on what parameters they should trust your venture to grant a loan. This is where creating a detailed financial model becomes essential. It’s not just about showing past performance; it's about projecting your business's financial position over the next 5-10 years and demonstrating how you plan to utilize the loan effectively.

Why Banks Ask for a Financial Model (And What They Look For)

Banks are primarily concerned with two things when considering a loan application: risk and repayment capacity. A well-constructed financial model provides them with the data to assess both. They need to see a clear picture of how your business will generate enough cash flow to service the debt, including principal and interest payments. Key metrics they scrutinize include:

• Debt Service Coverage Ratio (DSCR): This ratio indicates your business's ability to cover its debt obligations from its operating income. A higher DSCR is always favorable.
• Profitability: Banks look at your net profit margins to understand the efficiency and viability of your business operations.
• Liquidity: Your ability to meet short-term obligations is crucial. Current ratios and quick ratios are often evaluated.
• Solvency: Long-term financial stability, indicated by debt-to-equity ratios, assures the bank of your business's endurance.

Essentially, the bank wants to understand how you intend to utilize the loan they provide and what your business's financial position will be over the next 5 to 10 years. This forward-looking perspective, captured in your 5-year financial projections for a bank loan, helps them gauge the safety of their investment. Without this foresight, your application is unlikely to progress.

Step 1: Gathering Your Historical Data (The Base Year)

Before you can project the future, you must establish a solid foundation based on your past. The first step in building a compelling financial model for a bank loan is to gather your historical financial statements. These "real financial statements," as they are often called, include your Income Statement, Balance Sheet, and Cash Flow Statement for at least the past three to five years. These prepared statements serve as the base for preparing your financial model.

For example, if you're applying for a loan in 2024, you would use your actual financial figures for 2021, 2022, and 2023 as your base years. This historical data provides credibility to your future projections, showing the bank that your assumptions are rooted in actual business performance rather than mere speculation. Ensure this data is accurate and verifiable, as banks will often request supporting documents.

Step 2: Building Your Revenue and Cost Assumptions

This is where you start to paint the picture of your business's future. Creating realistic assumptions for your revenue and costs is critical for sound 5-year financial projections for a bank loan. Your assumptions should be detailed and justifiable.

Revenue Assumptions:

• Units Sold: Project the number of units or services you expect to sell each year. This could be based on market growth, your marketing efforts, or historical sales trends.
• Average Selling Price (ASP): Determine the price per unit or service. Consider potential price increases, competitive pressures, and inflation.
• Revenue Growth Rate: Based on the above, calculate your projected annual revenue growth. Be conservative yet ambitious.

Cost Assumptions:

• Cost of Goods Sold (COGS): This includes direct costs like raw materials, labor, and manufacturing overhead. Project this as a percentage of revenue or per unit.
• Operating Expenses (SG&A): Selling, General, and Administrative expenses include salaries, rent, utilities, marketing, and office supplies. Categorize these into fixed and variable costs and project their growth. For instance, you might project salaries to increase by a certain percentage annually, or marketing expenses to scale with revenue.

Every assumption should be backed by market research, industry benchmarks, or your business strategy. Banks will scrutinize these assumptions, so be prepared to explain your rationale. For instance, if you project a significant jump in sales, be ready to explain the new product launch, market expansion, or increased marketing spend that justifies it. Understanding how to track sales rep performance in Excel can also help you refine your revenue projections based on historical team output.

Step 3: Creating the 3 Statements (Income, Balance Sheet, Cash Flow)

With your historical data and forward-looking assumptions in place, you can now construct the three core projected financial statements for loan application. This forms the backbone of your financial model for a bank loan.

1. Projected Income Statement:

Start with your projected revenue. Subtract COGS to get Gross Profit. Then deduct your operating expenses (SG&A) to arrive at Earnings Before Interest and Taxes (EBIT). Further subtract interest expense (which we'll model in the next step) and taxes to get Net Income. This statement shows your projected profitability over the next five years.

2. Projected Cash Flow Statement:

This statement tracks the actual movement of cash in and out of your business. It starts with Net Income from the Income Statement, adjusts for non-cash items (like depreciation), and then accounts for changes in working capital (receivables, payables, inventory), capital expenditures, and financing activities (like loan proceeds and repayments). The Cash Flow Statement is crucial for banks as it directly shows your ability to generate cash to repay the loan.

3. Projected Balance Sheet:

The Balance Sheet provides a snapshot of your assets, liabilities, and equity at a specific point in time. Each line item on your projected Balance Sheet should flow from your Income Statement and Cash Flow Statement. For example, retained earnings will grow by the net income less any dividends, cash will reflect the ending cash balance from the Cash Flow Statement, and fixed assets will incorporate new capital expenditures less depreciation. This statement ensures that your entire model is internally consistent.

Mastering the intricacies of these statements can seem daunting, but Juno School offers a free certificate course on 3-Statement Financial Models in Excel, which can guide you through the process step-by-step.

Step 4: Modeling the Loan Itself (Debt Schedule)

This step integrates the specific details of the bank loan you are applying for into your financial model. You'll need to create a separate "Debt Schedule" that calculates the principal and interest payments over the loan's tenure.

• Loan Proceeds: The amount of the loan received will increase your cash on the Balance Sheet and create a new liability entry under "Long-Term Debt."
• Interest Expense: The interest calculated each year from your Debt Schedule will be recorded as an expense on your Income Statement, reducing your net income.
• Principal Repayments: The principal portion of your loan payments will reduce your "Long-Term Debt" liability on the Balance Sheet and be reflected as a cash outflow under financing activities on your Cash Flow Statement.

Accurately modeling the loan ensures that your projected financials reflect the true impact of taking on new debt, allowing the bank to clearly see your capacity to handle the repayment burden.

Step 5: The Final Check - Does Your Balance Sheet Tally?

Before submitting your financial model for a bank loan, a critical final step is to ensure that your projected Balance Sheet always balances. The fundamental accounting equation, "Assets = Liabilities + Equity," must hold true for every projected year.

If your Balance Sheet doesn't tally, it indicates an error in your model. Common culprits include incorrect linkages between statements, miscalculations in working capital, or errors in how retained earnings or cash balances are carried forward. This check is crucial because a non-balancing Balance Sheet immediately signals a flawed model to the bank, undermining your credibility. Take the time to meticulously review all formulas and connections to ensure everything aligns perfectly.

Preparing financials for a business loan requires attention to detail and a clear understanding of financial principles. By following these steps, you can build a convincing financial model that demonstrates your business's viability and repayment capacity to potential lenders in India.