How to Create a Financial Model with Debt Financing

Last Updated on May 11, 2025

Building a Financial Model with Debt Financing in Microsoft Excel is a vital skill for forecasting cash flows, structuring funding needs, and analyzing returns. In this tutorial, I’ll guide you through building a dynamic financial model — from projecting revenues and costs to calculating the equity and debt required to finance a business.

You’ll also learn how to model loan repayments, apply debt covenants, and use Excel’s financial formulas to create a complete forecast.

You can download my Financial Model with Debt Funding Template to apply the techniques shown here. Or watch the video tutorial at the end of this post.

Step 1. Forecast Monthly Revenue Growth in Microsoft Excel

Before adding debt financing to a model, start by projecting revenue growth. Open a new spreadsheet, set realistic sales growth targets, and calculate monthly revenues by multiplying projected unit sales by the average sales price.

You can structure the cash flow forecast into three parts:

• Units of Sales — estimates the number of units sold each month, growing over time as the business scales.
• Sales Growth Rate — defines the month-to-month percentage increase in sales, typically starting high and gradually slowing as the business matures.
• Revenue Projections — calculates monthly revenues by multiplying sales units by the average price. For example, 100 sales at $100 each would generate $10,000 in revenue.

You will link monthly sales growth directly to both revenues and cost of sales while steadily growing more stable expenses, like staff and office costs, over time.

Step 2. Estimate Costs and Build a Cash Flow Forecast (Unlevered)

Once you set your revenue projections, the next step is forecasting costs and completing the cash flow model.

Divide expenses into three key categories:

• Sales Costs — variable expenses directly tied to revenue generation, such as customer acquisition costs, commissions, or revenue share agreements,
• Operating Costs — more stable expenses like salaries, rent, utilities, and administrative overheads, which grow steadily over time,
• Capital Expenditure (CapEx) — investments in technology, research and development, or major equipment purchases required to support long-term growth.

The above example starts with an unleveraged cash flow. This unlevered view shows how much cash the business can generate from operations without considering loans or repayments, providing a baseline for planning debt and equity needs.

🏗 If you’d like to see how long-term CAPEX impacts financial planning, my Capital Investment Plan tutorial walks through linking capital spend to future growth and returns.

Step 3. Calculate Funding Requirements from Forecasted Cash Flow

With revenues and costs projected, you can now calculate the cash flow forecast. Subtract total costs from revenues month-by-month to find the company’s net cash flow over time.

Then, identify the funding requirement by locating the lowest point of cumulative cash flow, which is the moment when the business needs the most financial support.

Split funding sources into two main types:

• Equity — capital invested in exchange for ownership and a share in future profits,
• Debt — loans or financing from banks or investors, requiring regular repayments with interest.

In the early stages of a business, equity often covers most funding needs because revenues are still unpredictable. As the company grows and builds a stable track record, debt financing can replace part of the equity burden and leverage the business with loans while preserving ownership.

The appropriate balance between equity and debt will depend on cash flow patterns, risk appetite, and the debt covenants discussed later in this tutorial.

Step 4. Model Debt Tranches, Repayments, and Interest

When building a debt financing model, it’s important to remember that lenders tend to be more cautious than equity investors. They typically focus on a company’s historical cash flows rather than its future projections. As a result, raising debt can be challenging for businesses that lack a proven revenue track record.

The example below begins with the previous year’s gross margin, calculated as revenues minus direct costs.

It then follows these steps:

• Models debt tranches by splitting total borrowing across multiple stages or periods,
• Forecasts principal and interest payments separately for each tranche,
• Consolidates the repayment schedules to determine total debt funding and servicing obligations.

📥 You can download my Financial Model Template to see how I use Excel’s Present Value formula and past gross margins to estimate realistic debt levels and plan sustainable loan repayments.

Step 5. Calculate Equity Needs and Estimate IRR and NPV for Investor Returns

Once debt is in place, equity funding will fill the remaining financing gap.

Calculate equity needs based on four key components:

• Current Cash Balance — cash available at the beginning of the period to support operations,
• Cash Flow from Operations — the monthly net cash flow after revenues, operating expenses, interest payments, and working capital changes,
• Cash Flow from Investment — cash spent on research, development, and other capital expenditures,
• Debt Financing — loan inflows and repayments modeled in earlier steps.

The example above uses Levered Cash Flow (i.e., cash flows after debt service) to estimate investor returns. For simplicity, it applies an EBITDA multiplier to estimate the exit proceeds and Microsoft Excel’s NPV and IRR formulas to calculate the return on investment.

Remember that increasing debt reduces the amount of equity needed but raises debt obligations, so it’s important to balance risk and return when setting funding strategies.

💼 To see how debt and equity funding work together across multiple phases, my Project Finance Model tutorial shows how to balance return targets with funding constraints.

Step 6. Apply Debt Covenants and Monitor Key Risk Ratios (DSCR, Debt-to-Equity, EBITDA)

Adding debt covenants to your financial model is essential for managing risk and ensuring a business can comfortably meet its current and future debt obligations.

For example, my financial model tracks three key covenant ratios:

• Debt Service Coverage Ratio (DSCR) — measures how much of the company’s debt obligations are covered by current earnings. A DSCR below 1.0 signals potential trouble.
• Debt-to-Equity Ratio — shows the proportion of total funding from equity versus debt, helping ensure the business maintains a strong capital base.
• Debt-to-EBITDA Ratio — compares the company’s total debt to its annual earnings before interest, taxes, depreciation, and amortization. Higher ratios indicate more leverage and greater repayment risk.

🔗 Compare your debt ratios to average benchmarks, which vary by industry – you can find reference values here [external link].

Step 7. Use Excel Formulas for Debt Modeling (PV, PMT, IRR, NPV)

Microsoft Excel provides several powerful formulas that make modeling debt financing faster and more accurate.

Use the following functions to automate debt and equity calculations:

• PV (Present Value) — calculates the value of a series of future cash flows discounted at a given interest rate. It helps estimate how much debt can be supported based on projected revenues.
• PMT (Payment Formula) — determines the size of periodic payments needed to repay a debt tranche, splitting payments into interest and principal portions.
• IRR (Internal Rate of Return) and NPV (Net Present Value) — estimate the returns for equity investors, allowing comparisons between different financing scenarios.

Step 8. Integrate Debt into the Cash Flow, P&L, and Balance Sheet

The final step in building a financial model with debt financing is integrating all debt-related calculations into the broader forecast.

Link the outcome of your debt calculations to three key areas:

• Levered Cash Flows — add interest expenses and principal repayments to the cash flows from operations and financing.
• Profit and Loss Statement — reflect interest expenses in earnings and adjust taxable income accordingly.
• Balance Sheet — record debt balances under short-term and long-term liabilities and update them dynamically as repayments occur.

It’s essential to link all debt calculations carefully so that cash flow, tax, and financing flow changes are consistent across the model.

Finally, always verify that the Balance Sheet balances correctly, meaning Total Assets must equal Total Liabilities plus Owner’s Equity.

📌 Recap: 8 Steps to Build a Financial Model with Debt Financing

Here’s a step-by-step summary of the process for creating a financial model that includes debt financing:

1. Forecast Revenue Growth in Excel. Set realistic sales growth targets and calculate monthly revenues using units sold and average price.
2. Estimate Costs and Build a Cash Flow Forecast. Forecast variable and fixed expenses to project unlevered cash flow before any financing.
3. Calculate Funding Requirements from Cash Flow. Identify the peak funding gap by tracking cumulative cash flow over time.
4. Model Debt Tranches, Repayments & Interest. Use gross margin data to determine debt capacity, and build repayment schedules using Excel formulas.
5. Calculate Equity Needs and Project Investor Returns. Estimate equity requirements after debt and calculate investor returns using IRR and NPV.
6. Apply Debt Covenants and Monitor Risk Ratios. Track key metrics like DSCR, Debt-to-Equity, and Debt-to-EBITDA to ensure sustainable financing.
7. Use Excel Formulas for Debt Modeling (PV, PMT, IRR). Automate key financing calculations with Excel functions to streamline your model.
8. Integrate Debt into the Full Financial Model (P&L, Balance Sheet). To complete the model, link debt effects across cash flow, income statement, and balance sheet.

 

📥 Download My Financial Model with Debt Funding Template

This is the Excel file featured in the tutorial—designed to help you build a complete financing model from scratch. It covers everything from revenue forecasting and capital expenditure planning to debt structuring and equity return analysis.

The template includes all the key components you need to model complex funding strategies:

• Monthly Cash Flow Model — projecting revenues, operating costs, CAPEX, and working capital needs.
• Debt Financing Schedule — calculating debt tranches, repayments, and interest expenses.
• Equity Funding and Return Analysis — showing unlevered and levered equity requirements, NPV, IRR, and exit proceeds.
• Balance Sheet Summary — tracking assets, liabilities, and equity funding over time.
• Debt Covenants Monitoring — automatically testing DSCR, Debt-to-Equity, and Debt-to-EBITDA ratios.

The template is built in Excel but can easily be converted to Google Sheets if needed.

👁️ View full template details

Video Tutorial on Using my Financial Model with Debt Funding

You can follow along with my complete video walkthrough, where I explain step-by-step how to use the Financial Model with the Debt Funding Template.

In this video, I cover:

• How to forecast revenue growth and project unlevered cash flows.
• How to model debt financing tranches, repayments, and interest.
• How to calculate equity requirements and project investor returns.
• How to integrate debt funding into cash flow, P&L, and balance sheet forecasts.
• How to apply debt covenants and stress-test funding scenarios.

▶️ Watch on YouTube

 

Get in Touch

Hi, I’m Jacek — and I’m passionate about building financial models that help businesses grow and succeed. I hope this tutorial clarified your understanding of structuring debt financing inside a complete cash flow model.

Feel free to reach out if you have questions about financial modeling, debt structuring, Excel techniques, or project forecasting.

You can also explore my other tutorials, or check out my One-to-One Training and Financial Modeling Services if you’d like personalized support.

Disclaimer: This tutorial is for informational and educational purposes only and should not be considered professional advice.

Explore More Financial Modeling Tutorials

Want to sharpen your financial modeling skills even further? Here are more tutorials you might find helpful:

• Project Finance Model — Consolidate multiple projects into a single forecast and optimize debt and equity structures.
• Merger and Acquisition Model — Model acquisition strategies using debt financing and project returns on leveraged deals.
• Telecom Investment Model — Forecast customer growth, infrastructure CAPEX, and funding requirements for telecom projects.
• Real Estate Investment Model — Add debt funding to property investment forecasts and evaluate long-term cash flows and returns.
• Capital Investment Plan — Project capital expenditures and connect them to cash flow and balance sheet forecasts.

→ View all tutorials