Over the last few months I have received a number of questions from people who have attended the project finance modelling training courses I have presented in Melbourne, Sydney, Shanghai and Los Angeles. It is great to hear from top financial modellers and to exchange ideas about best practice modelling techniques, such as Corality’s SMART modelling methodology.
Interestingly, in the last month I have had a couple of repeat questions being
- How to sculpt to a target DSCR without macros and without a circular reference (a key challenge in Project Finance Modelling)
- How to do this when interest is calculated on an average interest basis (ie average of opening and closing balance)
Great questions that have eluded me for a long time. They raise the issue of using accounts to avoid circular references, which can be quite confusing at first but once understood are one of the most powerful “tools” in Excel.
So what are the challenges above?
Sculpting to a target DSCR algebraically
Firstly, the point of sculpting to a target DSCR is to ensure the principal and interest obligations are appropriately matched to the strength and pattern of cashflows each period. I often see people using VBA or the Solver in function for what is actually quite an easy solution.
The total debt service is simply
Cash flow available for debt service (CFADS) / DSCR (Target).
Any interest or other fees (such as Letters of Credit for a debt service reserve) can simply be backed out of the calculation.
So…
Principal = Total Debt Service – Interest – other fees.
The issue is that the principal payment affects the debt size. But remember the interest is based on the period’s opening balance of debt whereas the principal only affects the closing balance, which is taken through to the next period.
Free tutorial on debt sculpting
Luckily, we have a great tutorial, Debt sculpting to target DSCR without VBA, which gives you a step by step example.
But what about with average interest?
Now this is a little trickier…but with an algebraic solution we have the problem solved! Check out our tutorial, Solution to circular interest – calculate interest on average balances.
Better still, attend our project finance debt modelling masterclass training course to learn this and many other handy tricks first hand. There is no better way to learn than to build this from scratch with the help of expert project finance advisory and modelling tutors who do this every day, not just in the classroom but on multi-billion dollar transactions globally.
Post new comment