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. i.e. the hardcoded debt amount multiplied by the 2%. You’ll notice that the check will change from zero to a number. Now, copy and paste values the calculated debt amount into the hardcoded debt amount, until the check amount goes back to 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.

neilJune 11, 2013 at 9:22 pmHow do you avoid circularity in modelling a commitment fee on the undrawn portion of a loan?

adminJune 28, 2013 at 9:41 amNeil, you can avoid a circularity on a commitment fee by modelling monthly, with draw downs done at the end of each month. The commitment fee would then be the (total senior debt (which would be hardcoded to avoid the arrangement fee circularity) less opening balance or the amount drawn to date) x the commitment fee percentage. If you were modelling monthly you’d then have to weight it by 1/12 or the number of days in the month divided by 365.

Let me know if you’d like a further explanation or a tutorial on this.

I hope this helps and please let us know if you have any further questions.

Regards

Brett

Certified Financial AnalystJuly 21, 2014 at 6:49 amIt’s a very useful piece of information over financial modeling.

Thanks! for sharing these amazing tutorials.