Debt modelling 6: Debt sizing & debt sculpting
Using debt service coverage ratio to determine debt capacity.
Download the lesson start file:
To obtain the worked example file to accompany this chapter buy the financial modelling handbook.
Until now, we have used the 70% leverage input given in the case. Leverage is only one of the metrics that lenders will use to determine the maximum debt capacity.
They will also use "affordability" ratios like Debt Service Cover Ratio.
We are using the leverage % to determine the amount of debt and then calculating the resulting debt service cover ratio.
We will change that now and determine the amount of debt using the minimum DSCR, and then calculate the resulting leverage.
We are also going to use the ratio to determine the debt service. If lenders require a certain ratio in every period, we can use that to determine what debt service level is required to meet the ratio.
Let's walk through it.
Step 1: Calculate debt service at the target ratio
We have already seen that DSCR is calculated by dividing Cash Flow Available for Debt Service, by Debt Service.
Since we know CFADS (it's on our cash flow statement), we can rearrange the formula to determine the level of Debt service required to meet a certain coverage ratio.
Let's say that lenders require a ratio of 1.3x.
Our first step is to calculate the maximum debt service in each period, given the target ratio of 1.3x.
Given that DSCR is an "affordability ratio" - looking only at the cash flows in each period, we can only consider the CFADS during the debt term.
Our first step, therefore, is to calculate CFADS during the debt term:
We can then use this to calculate debt service at the target ratio. We divide the CFADS by the target ratio to calculate the debt service.
Step 2: Bring up the senior debt discount factor
Next, we want to figure out the amount of debt drawn at the transaction date that will generate the debt service we have calculated. This will be the maximum debt capacity of the business.
For this, we need to discount the debt service using the period debt interest rate as our discount rate.
We have already created a Senior debt discount factor when calculating the amortising debt profile.
Since we need this discount factor for both calculations, I will move it further up the debt sheet. As your model build progresses, don't be afraid to move calculations around to improve the structure and flow of the model. Remember when you are moving calculations around, use Ctrl+x to cut rather than Ctrl+c to copy.
Step 3: Calculate debt capacity
Now that I have both the ingredients I need, I can calculate the debt capacity by multiplying the debt service in each period by the discount factor for that period. SUMPRODUCT is an efficient function to use for this.
We can see that using the target ratio for debt sizing gives us a higher debt amount than our initial assumption around leverage.
Typically lenders will provide minimum DSCR and maximum leverage constraints. If the maximum leverage were 70%, we would not be able to borrow more than that even if the DSCR debt sizing indicated that we could.
Step 4: Switch between DSCR and leverage debt sizing
I want my model to easily switch between DSCR based debt sizing and a straightforward leverage percentage input as we have now.
I'm therefore introducing a simple switching mechanism.
Step 5: Calculate periodic principal repayment amount to maintain the target ratio.
When we calculated the amortising debt profile, we first calculated the period debt service and then deducted the interest to arrive at the principal repayment amount.
We have now calculated the periodic debt service to maintain our target ratio. We can use the same approach of deducting interest from this debt service profile to calculate the principal repayment amount that will give us a constant DSCR.
Once we have deducted interest to calculate the principal repayment amount, we can wire that into our "principal repayment selection" block and choose profile 3.
We can see that our IRR has gone up as expected, and our ratios are at 1.30 in every period.
To achieve this, the profile of debt service has changed dramatically:
However, we can see a problem with the principal repayment amount in the first period. There is insufficient cash flow in the first period to even pay the interest on the debt without breaching our minimum ratio. Therefore, our calculation yields a positive principal payment, which doesn't make any sense.
I could have set the assumptions in the model up to prevent this from happening, but this kind of issue is common when building models. It's not a problem or error with the model. It's the model telling us that there is a problem in the business that needs to be solved. And solving that problem requires a commercial / structuring solution.
In the next chapter, we'll look at some options for fixing this.
Download the model completed to this stage:
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.