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.
Step 1: Use Financial Model to Forecast Revenue Growth
Before adding debt financing to a financial model, you need to forecast the amount of cash a business can generate. Open a new Excel spreadsheet and set sales growth targets. Then, take the estimated sales and multiply them by the average sales price.
The calculation will result in monthly revenues. The cash flow model splits the Excel 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.
Step 2: Calculate Costs and Forecast Cash Flow
Now, it’s time to complete the financial model with cost calculations. Splits the cash flow spreadsheet into the following three cost categories:
- Direct Costs: Expenses directly related to sales revenues vary depending on the number of sales. Examples of direct costs could be acquisition costs if you can track your marketing costs to your sales. Other examples are commissions or revenue share that you may need to pay to a third party.
- Operating Costs: Costs 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. It’s always good to get professional advice about what you can include in the CapEx estimate as accounting rules may differ.
Step 3: Calculate Required Cash Flow Funding
After you have modelled revenues and costs, take the costs and subtract them from the revenues. The resulting figure will be the company’s cash flow. You can estimate the funding requirement by looking into cumulative cash flow and finding its minimum monthly value.
There are two ways you can fund a business with:
- Equity: Funding coming from investors in exchange for a share of ownership and potential future returns.
- Debt: Funding 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. In later years, with established revenues, you can introduce some debt financing.
Step 4: Calculating and Modelling Debt Financing in Excel
While equity investors may be more interested in future performance, the lenders will most likely be more conservative. Assume they will most likely 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.
The 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.
Divide your debt model into tranches, so you don’t draw all your debt at once. For example, to forecast debt financing every year, take cash stream revenues from each year. Then, model the repayments and interest payments as they were completely separate loans. Finally, consolidate all the tranches to calculate the total debt funding and obligation.
The example of a financial model below calculates the level of debt using Excel’s Present Value formula. The number of monthly payments should reflect the expected customer lifecycle. Using this value and taking the average historical gross margin will ensure that you can pay off your obligations using revenues from the existing users. The debt amount is topped up every year by drawing another tranche of debt. Notice that the model applies a 75% ‘haircut’ to control the overall level of debt drawn.
Step 5: Using Financial Model to Calculate Equity Returns
The rest of the financing will come through equity raised by potential investors. The Financial Model Template calculates the investment required as the difference of the following cash flow components:
- Current Cash Balance: This could be the cash sitting currently 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 our 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 both debt raised (inflow) and debt principal repayments (outflow).
The result will most likely result in lower equity funding but higher debt obligation. Remember to a financial model for any liabilities when calculating the terminal value of a business.
You will see that although the returns may differ by just a few percentage points, the NPV for the post-debt financing (i.e. levered cash flow) case is much higher. This results from a decrease in the overall cost of capital, as the debt funding and interest rate lower that requirement.
Step 6: Using Covenants to Moderate Debt Funding
Last but not least are the covenants you need to be aware of when calculating your debt financing. Here is the list of covenants included in the Financial Model Template:
- 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 that the company won’t be able to pay 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 you should expect most of your investment will come from 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 measure differs by industry, and you can find average values here.
Step 7: Useful Excel Formulas to Model Debt Funding
A few handy Excel formulas will help you calculate and model debt financing. The 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. Our model uses a conservative approach of taking the revenues from the previous quarter to forecast cash flow. The discount rate used in the interest rate a lender is likely to charge you.
- PMT: This Excel formula helps you determine the level of payment 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 estimates the percentage return on your investment over a time period. The NPV (net present value) will allow you to compare cash flows’ attractiveness before and after debt funding.
Step 8: Add Debt Funding Calculations to a Financial Model
Ensure you link all the outputs related to the debt funding calculations to the rest of your financial model. Specifically, you need to include debt amount, principal and interest payments in the post-debt funding (levered) cash flow lines.
The debt funding is reflected in the levered cash flow section of the financial model file. You will see that the earnings have been adjusted for estimated interest payments. There is a tax adjustment line to account for the increased expense.
The debt liability is also reflected in the Balance Sheet under the short- and long-term debt obligation. The outstanding debt is adjusted for in the exit proceeds and valuation calculation in the Equity Returns spreadsheet.
Summary: Adding Debt Financing to a Financial Model
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 to estimate the level of debt funding a business can attract. Remember to stay conservative, so use historical performance to calculate the debt level. Also, build covenants tracking to ensure that 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 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 calculated 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, so the debt is raised based on the period. That 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 yearly view.
- Equity Returns: Calculates the level of required equity funding under both pre-debt financing (unlevered) and post-debt financing (levered) scenario. It uses an EBITDA multiplier to estimate the enterprise/exit value level and the corresponding returns of equity.
- Balance Sheet and Debt: This sheet summarises the projected yearly balance of assets, liabilities and equity investment. The debt summary summarises the debt liability.
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 are looking for help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.
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 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 you a few tips on your first steps when starting a new business. You will also be able to 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.