Forecasting Finance (Equity, Debt, Interest)

Part three of the four-step financial forecasting model in Excel

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

Forecasting Finance: Equity, Debt and Interest

This article on forecasting finance is part three of the four-step financial forecasting model in Excel and focuses on how to model equity, debt, and interest. Having completed revenue forecasts down to EBIT for the income statement and operating assets forecast for the balance sheet, we can now move on to complete the balance sheet and income statement by forecasting debt and equity financing.

Forecasting Finance - Equity, Debt and Interest

By the end of this article, you will be able to:

  • Forecast debt financing and related interest costs
  • Forecast equity financing and dividends
  • Use both iterative and analytical approaches to manage circular references

Forecasting Financial Statements

Forecasting the capital structure of a company impacts both the balance sheet and the income statement through different items, including dividends and interest expense. Forecasting equity requires forecasting stock issuance and repurchases, as well as changes in retained earnings. Changes in retained earnings will be strongly driven by forecasted net income and dividends. When forecasted retained earnings changes in a model, users should complete a retained earnings supporting schedule section, much like how we used a note to forecast PP&E in forecasting balance sheet items.

Forecasting debt requires forecasting both short-term and long-term debt, as well as the associated interest costs. Once we’ve completed the financing forecast, we can complete the cash section, thereby completing the balance sheet. In short, cash is determined simply as the balancing figure in the balance sheet.

Financing Structure

 Forecasting the Capital Structure

The first question to ask when forecasting the financing structure is: what should be the split between equity and debt financing?

To answer this question, we need to consider leverage ratios, such as debt to equity, and coverage ratios, such as EBIT to interest expense.

Leverage Ratios and Coverage Ratios

Typically, financial covenants on loan agreements dictate the maximum leverage ratios and minimum coverage ratios that can be factored into our model. However, if the leverage or coverage information is not available, we can at least consider management’s willingness to take on additional debt or equity. Moreover, we can use the company’s current level of access to the debt and equity capital markets.

The Practicalities of Forecasting Finance

There are many practical modeling issues that need to be considered in forecasting finance, particularly in forecasting the capital structure, including equity, debt, and interest.

A useful question to ask at the beginning is:

Practicalities of Forecasting Finance

In other words, do you need to forecast debt levels as a multiple of equity? If you don’t need to forecast debt levels as a multiple of equity, consider using opening debt to calculate interest and assume that long-term liabilities are constant. This will keep your model simple and straightforward. More importantly, it will minimize the chance of circular references. There are many practical modeling issues that need to be considered when forecasting the capital structure.

If you need to use target leverage figures in your model, the next question to ask is: What level of precision is required in modeling the capital structure? In case you need to model debt and equity issuance from the first principles approach with high complexity, you will inevitably generate circular references that need to be solved. Solutions for circular references will be discussed later in this article.

The halfway point is to build your model with an intermediate level of complexity, where leverage ratios are used to calculate debt, whereas opening debt is used to calculate interest.

Debt Financing vs Equity Financing

Circular References

When calculating the debt and interest expense in our financial forecast, we can either use the opening debt (which is equivalent to last year’s closing debt), this year’s closing debt, or an average of opening and closing debts. Using the average debt will typically provide the most accurate interest expense; however, it can also lead to circular references.

Understanding the Problem: Average Debt Circular References

For simplicity’s sake, consider a debt facility (loan) with no principal repayments and where accrued interest is added to the existing principal, instead of being paid out each period. In order to get closing debt, we need two figures: opening debt and the accrued interest expense. To calculate accrued interest expense, we need two more figures: the interest rate and average debt. However, in order to calculate average debt, we need both opening and closing debt. In essence, we need closing debt to calculate closing debt. This problem is an example of a circular reference.

Average Debt Circular References

Solving Circular References with Iteration

Iteration is trying different values for model variables to ultimately find a solution. Each repetition process is called an iteration, and the result of one iteration is used as the starting point for the next iteration. For PC users in Excel 2007 and later versions, iteration is accessible on the “File” menu or Office button, by selecting “Options”, and then by selecting “Formulas”. Check the “Enable Iteration Calculation” box.

Excel Iteration

Excel Iteration Command

For Mac users, iterative calculative can be enabled by going to the application menu bar, Excel -> Preferences -> Calculation, then check the box for “Use iterative calculation”.

Circular References: Analytical Approach

Although the Excel function allows us to solve circular references, it must also be used with caution, as Excel no longer alerts users to any additional circular references. Instead, Excel will automatically attempt to solve these circular references, which is particularly dangerous if there are multiple solutions to a circular reference.

The equation below offers an analytical solution that can be used when modeling amortizing debt that allows us to avoid turning on the iteration function.

Average Debt Equation

Additional Resources

Thank you for reading CFI’s guide to Forecasting Finance (Equity, Debt, Interest). To keep learning and advancing your career, the following CFI resources will be helpful:

Analyst Certification FMVA® Program

Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.

 

Financial Analyst certification curriculum

 

A well rounded financial analyst possesses all of the above skills!

 

Additional Questions & Answers

CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.

In order to become a great financial analyst, here are some more questions and answers for you to discover:

 

0 search results for ‘