This step-by-step tutorial will take you through an example of using Microsoft Excel to create a financial model with debt financing. First, it shows how to calculate and forecast cash flows. Then, it covers estimating the debt and equity required to finance a business.
Download my Financial Model with Debt Funding Template to follow the spreadsheet and examples used.
You can also watch the video version of the tutorial at the end of this post.
Table of Contents
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.
Firstly, 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 depending on the accounting treatment and the type of business, you may need to move the marketing cost to the General Opex.
Learn More: Visit my blog on How to Build a Cash Flow Forecast in Microsoft 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 related to the sales. Other examples are commissions or a revenue share paid to 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 expenses associated with investing in assets or technology needed to grow a business. For example, those could be software development or R&D costs. Other examples would be equipment and investment-related costs.
Finally, notice that the financial model uses an “Unlevered Cash Flow” term to indicate the resulting cash before any debt injection.
Learn More: Visit my blog on How to Create a Marketing Investment Plan 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 the assets should a business break that promise).
For example, in many young businesses, you can expect the heavy burden of financing to fall on equity. Then, with established revenues in later years, you can introduce some debt financing.
Finally, 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 for more examples of calculating funding requirements by source.
Step 4: Calculating and Modelling Debt Financing in Microsoft 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. As a result, debt may be more difficult to raise in the early period of business growth when there’s no steady track record.
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.
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 uses an average gross margin from the previous months to calculate the forecast revenues’ present value. Also, it divides the debt model into tranches, so the financial model only draws some of the debt at a time.
The financial model above calculates the level of debt by applying Microsoft Excel’s Present Value formula. As a result, the debt estimate uses 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 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. For example, 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).
Finally, the model uses “Levered Cash Flow” term to indicate the cash needed after debt injections and repayments. Therefore, a higher loan amount will result in a lower equity financing requirement but an increased debt obligation. Also, 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 requirements 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].
In short, 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 for another example of creating a financial model with debt funding.
Step 7: Useful Formulas to Model Debt Funding
Microsoft Excel has a few handy built-in formulas to help you calculate and model debt financing.
For example, my Financial Model Template uses the following formulas:
- PV: The present value formula calculates the debt level given projected cash flows. My model combines it with the revenues from the previous quarter to forecast the debt injection. The discount rate is the interest rate a lender will likely charge a business.
- PMT: This 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 the equity. The NPV (Net Present Value) allows for a comparison of the investment’s attractiveness before and after debt funding.
Learn More: Visit my blog on Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial to discover basic 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, 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. Also, it includes the estimated interest payments in the earnings forecast and adjusts the tax estimate to account for the increased expense.
Finally, the model reflects the debt liability in the Balance Sheet under the short- and long-term debt obligation.
Lastly, remember to 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 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 Microsoft Excel. Specifically, it 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.
Consequently, the 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 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 an easy-to-follow layout:
- Cash Flow Model: Uses basic assumptions and formulas to forecast revenue and costs. The estimated sales units drive revenues and direct sales. Then, fixed cost 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. Then, 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. Then, 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. In addition, the debt summary illustrates annual debt liability.
The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].
*Order risk-free with a 30-day Money Back Guarantee
*Order risk-free with a 30-day Money Back Guarantee
For more details and a step-by-step explanation of using and creating the Financial Model with Debt Template, watch my video tutorial below:
Get in Touch
Hi, my name is Jacek and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Financial Model with Debt Funding in particular or financial analysis in general, don’t hesitate to get in touch.
Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.
How to Build a Cash Flow Forecast in Microsoft Excel – Create a financial model to forecast cash flow revenues with additional sheets to help you with sensitivity analysis, scenarios and presenting the model to potential investors.
How to Create a Project Finance Model – Learn how to use a Debt-to-EBITDA method to raise additional bank financing and consolidate a series of project cash flows into one forecast.
Merger and Acquisition Model – Explore using debt financing in a merger and acquisition transaction with examples of calculating repayment, interests and debt fees based on leverage percentage assumption.
Telecom Investment Model – Add a debt forecast to a capital-intensive infrastructure project with revenue and cost projections, headcount estimates, and a return on invested equity calculation.
Real Estate Investment Model – Another example of adding debt to a cash flow forecast with growth and build cost projections.
Monthly Budget Forecast – Convert monthly revenue and cost projections into a long-term forecast with accruals and cash balance movement calculations.
Subscription Model with Churn Calculation – Learn about including customer churn and lifetime value calculations in a cash flow forecast.
How to Start a Consulting Business from Scratch – Analyse and forecast business revenues and expenses with an easy-to-use spreadsheet.
Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial – Refresh your basics with this quick overview of using spreadsheets.