Excel Revolving Credit Calculator for annual models

Description
This model is for:
- financial modelers
- corporate finance
- investment professionals

Is it impossible to make a fully automated revolving credit calculator for annual models without using macros or circular references? Many would say yes, but actually this is doable.

The attached excel is a very simple annual model that includes a fully automated algorithm that allows to calculate an optimal schedule of debt payments.

When you have a monthly financial model, the algorithm is relatively simple as the issue is solved by shifting the income tax and interest payments to the next month. Closely matching what happens in a real case actually.

However, when wanting to use an annual time frame in a financial model, it becomes a real pain to find an optimal debt when you have a revolving loan facility implemented.

The solution provided here is efficient thanks to a smart combination of different flags.

Some checks also included show:

1/ When cashflows are positive (by looking at the interest payments and income tax savings), all excess cash is swept to reduce the amount of debt.

2/ When the investing and operating cashflows are negative, there is a calculation of additional debt required to cover the cash gap. It is calculated so that the ending cash balance corresponds to the minimum required cash balance.

Models using circular references and macros are harder to use, audit and update and very often subject to errors. In addition it is impossible to include a common sensitivity analysis in models built this way.

Consequently, it is fairly common to come across financial modelers who make manual adjustments to debt calculations in the case of operating and investing cashflow change. Not a best practice and definitely not the most optimal.

This is my relatively simple solutions to the issue described.

Feel free to share it out.

- Ivan Klykov