Not surprisingly given the current and expected future market conditions it is becoming increasingly common to see project finance debt being structured as a sequential series of loans, each one being refinanced by the next.
I have seen some horrific modelling as a result of this methodology and so I thought it might be a good idea to write up a neat alternative to having each debt account ‘taken out’ by another ‘debt account’ which requires the duplication of many calculations and takes up many lines without adding much to the model. Lets take a look at how its done using the following example.
How is it modelled
We have a slightly simplified senior debt facility of $150m which is a ‘repayment’ facility. Assuming a 7 year tenor and a base rate of 5%.. Gathering this information up within the model we might have something that looks like:
A traditional way of modelling a refinancing is to have another set of debt repayment mechanics (facility) which take over the account that is being refinanced. This approach, although arguably more transparent, requires a lot of lines in the spreadsheet. The way I am presenting below is a lot more efficient and further more allows inputs to be laid out more clearly too. In the schedule below, “Financing 2” refinances “Financing 1” and so on.
In the schedule below I have set it so that the first facility is refinanced. This triggers “Financing 2” with its associated margin and fee.
These inputs drive the following timing flags. The schedule below shows that on the dates prescribed a refinancing ‘event’ happens which sets the ‘repayment’ flag per facility.
Binary flags used for refinancing
Application of binary, refinancing and repayment flags is fundamental to this approach.
The single account, multi-facility, method drives off of a re-setting repayment flag.
Credit foncier repayment
Should you want to try this for yourself the extract below shows the total principal and interest resulting from the initial and subsequent financings.
The particular inputs show a smooth repayment profile which is punctuated by several refinancing events.
If you harness this approach it is straight forward to make each refinancing a little more sophisticated with additional functionality such as
- A grace period
- A different repayment method
- A Ballon % on a given date
hi nice tutorial and spreadsheet. very useful thanks.
there is a minor formatting error at cells E53 and E54 of ReFi sheet
I really like the simple and effective charting technique.
A few formula changes to the example and the refi parameters can also be used to change the original loan term of 7 years.
This is very clever! I do a fair bit of financial modelling of upside return analysis for infrastructure funds and this method will save me hundreds of lines of code! Thanks for sharing! Getting tips from professional financial modellers certainly help!
Liza
Hi Kevin,
Thank you for your comment – what sheet are you referring to? There is no download on this post….
Nick