This page demonstrates how to implement the UDF concept for a case with a single debt issue in the context of an annual model. The idea is to show you how to get comfortable with the process of adding the UDF function as a new sheet in your model. Of course, if you cannot get the simple cases to work, how could you possibly trust the complex ones.
Example 1: IDC Only
The model below is a simple annual model with just about nothing except a circular reference for IDC. The model provided has a circular reference with dreaded blue lines. You are to open this model, insert the circular template (and copy over the UDF) and then resolve the circular reference. My idea is that you do this in a transparent manner sort of like a copy and paste macro where you can see the calculated and fixed (but not fixed as it comes from a UDF).
To resolve the circular reference for this case and other cases, you can use the following four step process.
Step 1: Copy the UDF from the circular reference template (press the ALT, F8 to find the function in the circular reference template) and copy the template into a new sheet (you can use the ALT, E, M short-cut).
Step 2: Attach the different input variables from the model for the interest rate and the debt percent to the variables that are in the circular reference template sheet that you copied from Step 1.
Step 3: Enter the circular template function and use the SHIFT F3 function to find the variable names. When you are finished entering the variables press the SHIFT, CNTL, ENTER keys to get the variables. For this exercise you can only get the first line.
Step 4: Find the variables that a causing the circular reference in the file and attach those variables to the function output in the circular template page.
Example 2: Sculpting and Taxes Only
The model below is again a very simple annual model with no construction period (and therefore no IDC). But this one has sculpting and taxes that create a circular reference. You are to try the same process and again copy and paste the circular reference template into the simple sculpting and taxes model.
Example 3: DSRA Funding Only with Fixed Debt