Debt modelling 1: Balance and initial drawdown
By breaking down the debt modelling process into small steps I’m hoping to show that it’s more straightforward than you might expect.
Download the lesson start file:
To obtain the worked example file to accompany this chapter buy the financial modelling handbook.
Step 1: Set up a new worksheet.
- Use skill 9: How to set up a new worksheet.
- I have called the worksheet "Debt" and moved it to before equity in the sheet sequence. I aim to keep my sheets' left to right reading order broadly in line with the order items that appear on the income statement.
- Set up section labels for Senior debt. I find it helpful to add the headings for each section before I know where I'm going.
Step 2: Sort the corkscrew labels.
We are following the steps here from Skill 8.
Step 3: Calculate the initial drawdown
We know that the total accumulated project cost by the transaction date will be $100m. At our estimated 70% leverage, $70m of this will be debt-financed.
To create this block, I added a new input for the 70% leverage and copied the link to the $100m initial asset value from the Asset sheet.
I've then linked the $70m debt drawdown into the corkscrew.
Step 4: Add the debt balance to the balance sheet.
Since we have just added $70m of liability into the business with no other changes, our balance is now out by $70m, which makes sense.
The correcting change we need to make here is to reduce the amount of share capital going into the business by $70m. We will now only need $30m of share capital.
Therefore, we have increased the amount of debt by $70m and reduced the amount of share capital by $70m, so our balance sheet is happy again.
Note that our IRR has reduced after we made this change?
Is that in line with your expectation?
Download the model completed to this point:
To obtain the worked example file to accompany this chapter buy the financial modelling handbook.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.