UDF Circular Reference in Corporate Model

This page demonstrates how to make a User Defined Function (UDF) that solves the primary circular reference problem in a corporate model, where the interest expense is driven by cash flow but cash flow is driven by interest.  Here are some points about circular references in a corporate model:

1. Iteration Button or Copy and Paste Macros Destroy a Model
2. Making a UDF that solves all of this is easy, but you do need to make a few lines of VBA
3. Start with a simple corporate model.  Use lines for EBIT, Net Debt, Interest, Net Cash Flow.
4. Interest is from Average Debt, or interest = (opening debt – cash flow/2) * interest rate

The higher the cash flow, the less the debt and the interest, but the interest affects the debt, causing the circular reference.  One of the things that is really messed up if you use the iteration button is the goal seek.  When you create the iteration button the goal seek works fine. There are many other problems with the iteration button that cause your model to be unstable.  For example, if you delete some lines and get the #VALUE, then you cannot use the undo key.  With the UDF key these issues are not a problem.

First Video and Exercise

In this short video, you make a UDF that starts with interest expense and moves back to cash flow.  The idea of the VBA code is to start at the back. You need inputs for the interest rate and the cash flow amount and the opening debt balance. 

The video and the associated files are below.