Modelling an Arrangement Fee Circularity

Ok, in project finance deals you’re going to come across a particularly circularity around calculating senior debt arrangement fees. This blog tutorial will focus on how to model this circularity.

What is an Excel circularity?

In basic terms an Excel circularity is where a calculation refers to itself. In Excel you can allow circularities and the number of iterations which the calculation undergoes; however when you’re modelling and there are a few circularities it is best to break these up.

Why are arrangement fees circular?

Let’s think about this process. Firstly debt arrangement fees are calculated based on the total amount of debt. However debt arrangement fees are used to build up the total construction phase funding requirements, which are funded by debt. So basically as the arrangement fee amount goes up so too does the debt amount, which then causes the arrangement fee to increase. The arrangement fee and debt amount do converge though. Hopefully you see this circularity; however we will look at an example below.

An example of arrangement fees

For this example you will need to open up the pre-populated spreadsheet and youtube video attached to this blog tutorial. Now in this example we assume that all construction costs are fully debt funded. Arrangement fees are 2% of the debt amount and the other construction phase costs are as per the spreadsheet.

Now if we calculate the arrangement fees by multiplying the debt amount by the 2% arrangement fee, you’ll notice that we get a circularity as shown below.

Now to break this circularity we need to hardcode a debt amount. You’ll see why this is important soon. Copy and paste the calculated debt amount into the hardcoded amount as shown below. You’ll need to paste the value so push Home, then the Paste dropdown, then values to do this.

As shown above, create a check by subtracting the hardcoded debt amount from the calculated debt amount. Now when the model is solved this check amount will be zero. Link up the arrangement fee calculation to the hardcoded debt amount. Now, copy and paste values the calculated debt amount into the hardcoded debt amount, until the check amount is zero. This will occur when the arrangement fee converges and model is solved.

If you liked this, check out our free project finance modelling training by signing up to our newsletter.