Challenges: Monday Morning Mulling: September 2018 Challenge
1 October 2018
Welcome to this month’s Monday Morning Mulling! Last week, we set a challenge that has stumped project finance specialists worldwide – how do you set up an upfront or establishment fee for a debt facility, in a way that updates automatically and doesn’t require a macro to run it or to switch on iterative calculations?
Some of you may be familiar with our solution to removing circular references from interest income calculations. This problem basically required you to solve a series of simultaneous equations and break the algebra down to find a solution. Our problem this month uses a similar solution, but with a twist – this time we’re looking ahead into the future value of the different components.
The first step is to look at our construction costs and measure what the debt balance would be if we didn’t have an upfront fee. To do this, we can calculate the future value of the construction costs, as at the end of the construction period, escalating based on the interest rate being charged on the debt, and compounding at a rate based on the periodicity of our model.
The second step involves looking at the total maximum debt value: this is going to be equal to the future value of the construction costs (increased by the interest compounding) plus the future value of the upfront fee.
Now, the value of the upfront fee is based on the total maximum debt value multiplied by the upfront fee percentage. This then needs to be escalated so that we can work out the future value of the upfront fee. In this case, the upfront fee is being paid in the first period.
Once these simultaneous equations are solved, a single number can be determined that doesn’t result in a circular reference, thus solving our problem!
If you’ve managed to get this far, you can try to generalise the problem and consider what happens if you have different funding arrangements (e.g. drawing equity first, debt second), or if you have line fees, or other complications. If you've found yourself a little stuck with the algebra and want to go straight to the solution, you can download the sample solution file here.
How did you go at solving the issue? Have you run into this problem in the past? Let us know, otherwise, we’ll see you at the end of October for the next challenge!
