When you are at the early stage of the appraisal of a project finance deal and you haven’t yet approached any lenders, you might only need to model one tranche of debt in your project finance model. As you progress with the project and you start negotiating with different banks, you will need to test different financing instruments and different debt terms.
I always recommend to at least include 2 tranches of debt in your financial model. You can use only one tranche at the early stage and activate the second tranche when needed.
In this blog post, I want to show you how to build your models in such a way that if you need to add another tranche of debt, you could easily do it following couple of steps and you won’t need to spend half day to balance the balance sheet after adding a third tranche in your model.
Let’s tell this with a story. Let’s say that you are working on a project for a couple of months now and you have a financial model with 2 tranches of debt. Up to now you could do with these two tranches of debt because you have a pool of lenders giving an 18 years tenor and another pool of lenders providing 15 years tenor. You just received an email from the Mandate lead arranger that one of the banks pulled out of the deal and there’s no possibility for any of the lenders to go beyond their current commitment to replace that lender and cover the gap. There’s a possibility for engaging with a local bank but the maximum tenor offered by this bank is 12 years. Now you need to add a third tranche in your existing model.
When I was building the model in the first place I had this in mind that what if in future I need to add other tranches. So, I did the formulas, links and modules in such a way that I could copy one of the existing tranches and paste it, make a couple of adjustments and have my third tranche. Here is how:
- List all tranches in your inputs: In your Inputs sheet, in the financing section, list all the senior tranches and include the maximum commitment from each tranche. You can express it in percentages or in $ value. If the size of the debt is not yet fixed and is still limited to a certain gearing or achievement of a minimum DSCR then you need to keep one as the floating/balancing tranche.
Just a side note regarding the format of my Input sheet, I am presenting my constant inputs (inputs that are not time dependent) in the form of scenarios. For more on that please check this link.
This way user can see upfront how many tranches are available and how many of them are active in the model (if the %s or amount is 0 it means it is not active in the current case)
2. Step 2 is to start listing the specific terms of each debt tranche in your Input sheet. While building the Senior Debt terms for each tranche, also link the labels to the name of the tranche that you just listed above (step 1)
3. After you have done step 1 & 2, for the next tranche you just copy and paste all the inputs for tranche 1. Change the subheading to the cell where you have the name for tranche 2 and customize the inputs to match the terms you have for tranche 2. You repeat the same thing for tranche 3.
This way if you need to add a 4th tranche you simply do step 1, meaning adding the tranche to the list of pooling lenders and then copy and paste any of the debt terms and customize it.
4. Now that you are done with the inputs, you create a calculation sheet or if you already have one go there and start building the debt balance for tranche 1. But first start with the header and link the header to the name of senior tranche 1 in your input sheet.
5. Then carry one with building different calculator blocks for the debt, meaning:
- Senior debt Tranche 1 timing flags and counters
- Senior Debt Tranche 1 drawdown
- Senior Debt Tranche 1 Fees
- Senior Debt Tranche 1 Interest payable during construction
- Senior Debt Tranche 1 Interest payable during repayment
- Senior Debt Tranche 1 Principal repayment
- Senior Debt Tranche 1 balance
- Senior Debt Tranche 1 Check
– When you are making these calculations, always bring the input that you need from the input sheet to this calculation sheet. This has 2 benefits. One the auditor or user can see directly next to the formula what;s the value of the input that you are using and avoid the back and forth movement between inputs and calculations to trace the formula. Second, in the next step, when you want to replicate the tranche to create tranche 2 and 3, you simply need to change the inputs and link them to the relevant tranches.
– Note that every time
that you are doing these calculations, in the labels instead of typing “Senior Debt Tranche 1”, link it to the cell in your Inputs where you put the name of the tranche (Step 1)
6. Now that you have one generic tranche, you simply:
- Copy and paste it beneath the Senior Debt Tranche 1
- Change the header to the cell referring to Senior Debt Tranche 2
- Change the links to refer to inputs related to tranche 2 (cells with green fonts are the one coming from Inputs sheet)
Now you have created all calculation blocks for tranche 2. Go over them again to make sure that you have done all the links and there are no links to inputs from tranche 1 in tranche 2.
And repeat the same thing for tranche 3.
- You need to now make the links to the financial statements and sources and uses of funds.
- Interest during constructions and fees should go to the sources and uses of funds statement
- Interest during repayment and principal repayments should go to calculation of Debt ratios as well as link to financial statements
- Ending balances should go to your balance sheet
- And checks should go to your master check sheet.
However instead of individually linking these cash flows, i create another calculation block I call “Summary Senior Debt” and for each of these flows, I make the sum of the 3 tranches and link the sum to the relevant statements and calculation blocks, this way next time if I add a 4th tranche, I just need to include it in the summary debt calculations and trust me you’ll have less trouble balancing your balance sheet.
This was not easy for me to explain, I hope all these made sense. I also made a video explaining the same, you can check it out here.
