View More Content

Circular reference on debt service reserve account - no thanks!

Circular reference on debt service reserve account - no thanks!

Precision is the enemy of flexibility. Have you ever found yourself building your financial model when your coding of a flexible target for the debt service reserve account (”DSRA”) causes a circular reference?

If yes, well welcome to the world of project finance modelling and don’t worry, it is not unusual and you are not the only one at this very minute having the same issue! This situation, in many instances is due to a simple coding error and I would refer you to our tutorial but often, especially if you are repaying debt via a cash sweep mechanism the logic is indeed circular.  Further more this is probably because of the interest earned on the DSRA/c balance causing an issue with tax payments.

How do we solve the circular reference issue?

Well, like most things, there is a way to work around it but as with all fixes, you have to live with a few estimates.  I think that most practical solution to the DSRA circular reference problem is to approximate the target balance. So instead of having two quarters look forward we could use two times the current debt service as our target because:

  • In a steady repayment profile, this will be a very good approximation of future repayments
  • Even if the repayment is not constant (annuity), we would normally expect the repayment profile to be higher during earlier periods and lower during later periods. This would mean that the approximation would be towards the conservative side.
  • Easy to implement
  • This approach generally keeps the bankers happy and it is generally conservative

If you still insist on a precise target, the only other way to do it would be through a copy paste macro. This means a lot of lost flexibility and makes the data table function (great for Scenario analysis!) of Excel virtually useless. It is a big compromise to make for too little benefit, if you’d ask me.

Its 3am and I just need it to work!

You be the judge if you like this approach, would you rather use a conservative flexible approach or compromise flexibility for negligible benefits? The answer to this question also depends upon how much time you have and at what stage in the transaction you are at!

Happy modelling!

Haydn Palliser
by Haydn Palliser

As a Chartered Professional Engineer, Haydn is experienced across engineering, management consulting and finance. Originally from New Zealand, Haydn now leads Corality's Americas & European headquarters. He has led multi-billion dollar projects across the infrastructure, power and mining sectors globally. Haydn specialises in strategic consulting and transaction advisory services.

Contact Haydn Palliser

view all