How to Create an Excel Financial Model with Debt Financing

This step-by-step tutorial will take you through an example of using Excel to create a financial model with debt financing. First, it will show you how to calculate and forecast cash flows. Then, it will show you how to estimate the debt and equity required to finance a business.

Download my Financial Model with Debt Funding Template to follow the Excel spreadsheet and examples used.

You can also watch the video version of the tutorial at the end of this post.

Step 1: Create a Financial Model to Forecast Revenue Growth

Before adding debt financing to a financial model, forecast the revenues and costs to calculate the cash flow a business can generate.

Open a new spreadsheet and set sales growth targets. Then, multiply the estimated sales by the average sales price. The calculation will result in monthly revenues.

My Financial Model Template splits the cash flow into the following sections:

• Units of Sales: The top section of the spreadsheet calculates the level of sales a business can achieve, assuming an initial number of sales that will grow over time. For example, the units can be paid downloads or any other sales that generate customers’ revenues.
• Sales Growth Rate: The revenue model assumes that the sales will grow incrementally at a given rate. For example, you can start with a high month-to-month percentage increase and then decrease the growth rate as the business matures.
• Revenue Projections: The financial model applies a direct correlation between the number of units sold and an average price at a period. For instance, if you predict the number of sales in a given month to be 100 with an average sale of $100, the resulting sales revenue will be $10,000.

Notice that the marketing cost in the example above is directly related to the number of new sales; therefore, I have included it in the Cost of Sales. However, depending on the accounting treatment and the type of business, you may need to move it to the General Opex section of the Profit and Loss Statement.

Learn More: Visit my blog on How to Build a Cash Flow Forecast in Excel for more examples of revenue projections.

Step 2: Calculate Costs and Forecast Cash Flow

Complete the cash flow model by forecasting variable and fixed expenses.

For example, my Financial Model Template splits the spreadsheet into the following three cost categories:

• Sales Costs: Expenses directly related to sales revenues vary depending on the number of sales. Examples of sales costs could be acquisition costs if you can track your marketing costs to your sales. Other examples are commissions or a revenue share you may need to pay a third party.
• Operating Costs: Expenses associated with running your business. Although they can vary with sales numbers, they tend to be more steady and predictable over time. Examples would be salaries, regular bills or the cost of maintaining an office.
• Capital Expenditure (CapEx): Any cost associated with investing in assets or technology needed to grow your business. In our example, those would be software development or R&D cost. Other examples would be equipment and investment-related costs.

Notice that the financial model uses an “Unlevered Cash Flow” to indicate the resulting cash before any debt injection.

Learn More: Visit my blog on How to Create a Marketing Investment Plan in Excel for another example of creating a cash flow forecast.

Step 3: Calculate the Required Cash Flow Funding

Subtract the costs from the revenues to forecast a company’s cash flow. Then, estimate the funding requirement by looking into cumulative cash flow and finding its minimum monthly value.

My Financial Model Template uses two methods of financing:

• Equity: Funding coming from investors in exchange for a share of ownership and potential future returns.
• Debt: Financing from banks or other lenders in exchange for a promise of future repayments and interests (and potential claim on your assets should you break that promise).

In most young businesses, you can expect the heavy burden of financing to fall on equity. With established revenues in later years, you can introduce some debt financing in later years.

Note that the level of debt is subject to various constraints, which are discussed in the later parts of this blog.

Learn More: Visit my blog on How to Create a Project Finance Model in Excel for more examples of calculating funding requirements by source.

Step 4: Calculating and Modelling Debt Financing in Excel

While equity investors may be more interested in future performance, the lenders will likely be more conservative. They will probably focus on historical performance. That is one reason debt is difficult to raise in the early period of business growth when there’s no steady track record.

My Financial Model Template takes the gross margin, i.e. the sales revenues less the direct cost, to estimate the potential debt funding a business can receive. Specifically, it will use an average gross margin from the previous months to calculate the forecast revenues’ present value.

It also divides the debt model into tranches, so a financial model only draws some of the debt at a time.

For example, to forecast debt financing, take cash stream revenues from each year. Then, model the repayments and interest payments as separate loans.

Finally, consolidate all the tranches to calculate the total debt funding and future debt servicing obligations.

The example of a financial model above calculates the level of debt by applying Excel’s Present Value formula. The debt model takes the average historical gross margin to ensure a business can pay off its obligations using revenues from existing sales.

Learn More: Visit my blog on How to Create a Telecom Investment Model in Excel for more examples of calculating debt financing.

Step 5: Using a Financial Model to Calculate Equity Returns

The rest of the financing will come through equity raised by potential investors. My Financial Model Template calculates the investment required as the difference between the following cash flow components:

• Current Cash Balance: This could be the cash in a business bank account from which a business can cover its day-to-day operations.
• Cash Flow from Operations: Cash surplus or deficit generated by business revenues less its direct and operating costs, interest payments, and working capital adjustments.
• Cash Flow from Investment: In my example, this will be the cash spent on investment in R&D and development.
• Debt Financing: This is the amount of debt funding calculated in the model’s previous step. It includes debt raised (inflow) and principal debt repayments (outflow).

Notice that the model uses “Levered Cash Flow” to indicate the cash needed after debt injections and repayments. The result will result in a lower equity financing requirement but higher debt obligation. Remember to adjust a financial model for any liabilities when calculating the terminal value of a business.

Learn More: Visit my blog on How to Create a Subscription Model with Churn Calculation for another example of calculating an equity funding requirement and the Returns on Investment.

Step 6: Using Covenants to Moderate Debt Funding

Adding debt covenants to a financial model will help a business measure its ability to service current and future debt obligations.

For example, my Financial Model Template includes the following list of covenants:

• DSCR: The proportion of your debt obligation (i.e. debt service) that a business’s current earnings can cover. The required level may differ, but overall, any figure less than 1.0 means the company can only pay a portion of its debt obligation.
• Debt-to-equity: An indication of how much of the business funding is covered by equity. The exact requirement may differ, but a business may need to add a significant portion of the equity to cushion any shocks from future performance.
• Debt-to-EBITDA: The measure calculates the ratio of a company’s debt obligation versus its earnings. The higher the figure, the longer a company will pay off its debt using its current earnings. As a result, the less mature businesses with less predictable revenue should err on the conservative side. The Debt-to-EBITDA measure differs by industry, and you can find average values here [external link].

Use the debt covenants output to adjust the debt model’s assumptions and keep the debt amount and future repayments within reason.

Learn More: Visit my blog on How to Create a Real Estate Investment Model in Excel for another example of creating a financial model with debt funding.

Step 7: Useful Excel Formulas to Model Debt Funding

Excel has a few handy built-in formulas to help you calculate and model debt financing.

My Financial Model Template uses the following Excel formulas:

• PV: Present value formula allows you to calculate the debt level you can raise given projected cash flows. My model combines it with the revenues from the previous quarter to forecast the debt injection. The discount rate used in the interest rate a lender is likely to charge you.
• PMT: This Excel formula helps determine the payment amount required to pay off the remaining tranche of debt. To calculate the interest payment, multiply the debt balance by the interest rate. Deduct the result from the amount to estimate the principal.
• IRR / NPV: The Financial Model uses those formulas to calculate equity returns. The IRR (Internal Rate of Return) estimates the percentage return on your investment. The NPV (Net Present Value) allows comparing cash flows’ attractiveness before and after debt funding.

Learn More: Visit my blog on Your First Steps in Excel – Beginner’s Crash Tutorial to read more about basic Excel formulas and functionalities.

Step 8: Adding Debt Funding Calculations to a Financial Model

Ensure you link all the outputs related to the debt funding calculations to the rest of a financial model. Specifically, you include debt amount, principal and interest payments in the post-debt funding (levered) cash flow lines.

My Financial Model Template adds the debt funding to the levered cash flow section. It also includes the estimated interest payments in the earnings forecast and adjusts the tax estimate to account for the increased expense.

In addition, the model reflects the debt liability in the Balance Sheet under the short- and long-term debt obligation.

Always check that the Balance Sheet’s Total Assets equal the Total Liabilities and Owner’s Equity.

Learn More: Visit my blog on How to Create a Capital Investment Plan in Excel for more examples of integrating debt financing calculations into a financial model.

Summary: Creating a Financial Model with Debt Financing

This tutorial has taken you through the basics of creating and structuring a financial model in Excel. It specifically focused on debt funding calculations and their impact on financial returns.

Creating a financial model starts with setting up a basic cash flow spreadsheet that will allow you to tweak growth sales and revenue assumptions. Then, it follows by adding cost calculations to calculate the unlevered cash flow.

The resulting cash flow will help you estimate the debt funding level a business can attract. Consider using historical performance to calculate the possible debt injections. Also, build covenants tracking to ensure a business can cover its debt obligations.

Finally, use the resulting cash flows to calculate the investment returns. The post-debt funding model should reduce the required equity funding requirement and the weighted cost of capital. As a result, the net present value will most likely favour the scenario with debt.

Download: Excel Financial Model with Debt Funding

Download my Financial Model with Debt Funding Template to see examples of the main topics covered in this tutorial. While the Excel spreadsheets contain many calculations, I’ve tried to structure them into easy to follow layout:

• Cash Flow Model: Uses basic assumptions and Excel formulas to forecast revenue and costs. The estimated sales units drive revenues and direct sales. Then, fixed costs calculations follow and finally, capital expenditures. The model also calculates the estimated taxes, working capital and depreciation charge.
• Debt Model: Takes the cash flow model results to determine the level of potential debt finding. The model splits the cash flow into tranches, raising the debt over several periods. That also allows you to differ terms such as interest, grace periods and repayments over time.
• Summary: Consolidates the Cash Flow Model and Debt Model sheets results and aggregates them into a yearly view.
• Equity Returns: Calculates the required equity funding level under pre-debt financing (unlevered) and post-debt financing (levered) scenarios. It uses an EBITDA multiplier to estimate the enterprise/exit value level and the corresponding equity returns.
• Balance Sheet and Debt: This sheet summarises the projected yearly balance of assets, liabilities and equity investment. The debt summary summarises the debt liability.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].

 

Video: How to Use the Financial Model with Debt Funding Template

For more details and a step-by-step explanation of using and creating the Financial Model with Debt Template in Excel, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about financial analysis in general or any topic in particular, don’t hesitate to get in touch.

If you would like to book my One-to-One Financial Modelling Crash Course, click here. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

 

Learn More:

How to Build a Cash Flow Forecast in Excel – This step-by-step tutorial will show you how to use a financial model to forecast cash flow revenues. It also contains additional sheets to help you with sensitivity analysis, scenarios and presenting the model to potential investors.

How to Create a Project Finance Model in Excel – In this tutorial, you will learn how to use a Debt-to-EBITDA method to raise additional bank financing. It will also show you how to consolidate a series of project cash flows into one forecast.

How to Create a Merger and Acquisition Model in Excel – This post explores using debt financing in a merger and acquisition transaction. It uses examples of calculating repayment, interests and debt fees based on leverage percentage assumption.

How to Create a Telecom Investment Model in Excel – This tutorial explores adding a debt forecast to a capital-intensive infrastructure project. It also includes the revenue and cost projections, headcount estimate and a return on invested equity calculation.

How to Create a Real Estate Investment Model in Excel – This post discusses adding debt to a cash flow forecast using a real estate business example. It also shows how to create growth and build cost projections.

How to Create a Subscription Model with Churn Calculation – Learn more about subscription-based business models and how to build customer churn and lifetime value calculations into your cash flow forecast.

How to Start a Consulting Business from Scratch – This post will give tips for starting a new business. You can also download a free budget spreadsheet that will help you analyse and forecast customer revenues and expenses.

Your First Steps in Excel – Beginner’s Crash Tutorial – If you need to refresh your Excel knowledge, this tutorial will give you a quick overview of using spreadsheets. It will introduce you to basic calculations, v-lookup formulas and pivot tables.