The following happens when interests are calculated on the average debt balance during construction:

1. The model calculates the total funding requirement as being the capital expenditures + the interests from previously drawn debt

2. Based on a specified debt-equity ratio (70-30 for example), the model calculates how much debt is needed and how much equity is needed

3. The stock of debt at the end of the period has now increased by the additional debt that has been drawn in 2

4. Because the stock of debt has increased at the end of the period, it is now necessary to recalculate the interests for the period

5. Since the interests from the debt drawn during the period need to be financed, the new funding requirement is actually higher than the funding requirement calculated in 1

6. Start again from 2

 

This mechanism creates a circularity in the model that must be avoid at all costs (see best practices here), but several solutions exist.

 

 

Solution #1: calculate interests on opening balance only

 

The easiest solution to get rid of the problem is to ignore the interest from the debt that is drawn in the current period and to calculate interests on the opening balance only. This solution is easy to implement but may not reflect what will happen in reality as debt will be drawn during the period and not on the last day of each period (which is the underlying assumption since to interests are due on the debt drawn during the period).

 

 

Solution #2: increase model granularity and calculate interests on opening balance only

 

The solution #1 can be easily improved by increasing the granularity of the model: instead of calculating annual funding requirements, calculate monthly or quarterly funding requirements. This way even if interests are still calculated on the opening balance only, since the periods are now shorter, the difference between the interests calculated in the model and those that will actually be paid is dramatically reduced.

 

 

Solution #3: approximate the value of the interests on the debt drawn during the current period

 

One way to overcome the circularity issue and to get a more precise calculation of interests without using macros is to approximate the value of interests on the debt drawn during the period. The following steps explain the way to do it:

1.  Calculate the interests on the opening balance

2. Calculate the interests on the new debt drawn with following formula:

    Percentage of debt in financing structure x (Capital Expenditures + Interests on opening balance) x interest rate x 0.5

3. Sum 1 and 2 to get the interest charge for the period

This solution will give a good proxy to what the interest charge will actually be in the period, you can check it by dividing the total interest charge calculated in 3 by the average debt balance over the period, the result should be very close the interest rate

 

 

Solution #4: use a macro

 

The best way to calculate the precise value of interests on the average balance is to use a macro that is going to run as many iterations as needed until the results converge. It is necessary to have a basic knowledge of VBA to apply this solution. Go to the Visual Basic section here to learn how to do it.