Average DSCR in Financial Modelling

Download Excel Workbook

There are two different ways to calculate the average debt service coverage ratio (ADSCR) that could result in different numerical outcomes. What are the methods, what are the limitations that we should be aware of and which one should be used?

Debt service coverage ratio (DSCR) is one of the most commonly used debt metrics in project finance. Aside from the profile of the DSCR calculated on every calculation period, the ADSCR is an important output in a project finance model.

Two financial modelling solutions to ADSCR

• Calculate the average of the period-by-period DSCRs over the life of the loan
• Divide the total cash flow available for debt service (CFADS) over the life of the loan by the sum of principal (P) and interest (I)

On the face of it there is not much difference, but this tutorial will demonstrate that they can result in very different numerical outcomes. We will discuss why they are different and when to use each method, particularly when dealing with exotic cash flows or repayments.

ADSCR method 1 – Calculate ADSCR of the period-by-period

This may be the most common way to calculating the ADSCR.

Let’s recap this calculation method:

• Calculate period-by-period DSCR (CFADS/P+I)
• Calculate the average of the period-by-period DSCRs
• It is calculated using the ‘Average’ function in Excel
• Remember to define as an ‘Array’ to ensure that non-zero figures are utilised
• Activate the ARRAY function with Ctr + Shift + Enter.

ADSCR = {AVERAGE ( IF ( RANGE < > 0, RANGE ) ) }

ADSCR method 2 – total CFADS / total of P+I

The ADSCR is calculated using the simple steps below:

• Total the CFADS over the life of the loan
• Total the debt service over the loan life (i.e., sum of principal and interest)
• Divide the total CFADS over the sum of principal and interests

ADSCR = total CFADS (life of loan) / total P+I (life of loan)

Comparing the two financial modelling solutions to DSCR methods

Let’s take a look at an example where the CFADS are flat and the debt is repaid on annuity basis, which means equal  P+I. Period-by-period DSCRs are then calculated during the life of the loan and plotted, as shown below.

[image to be updated]
SCREENSHOT 1: FLAT REPAYMENT PROFILE

Here you can see that the average DSCR using both methods give very similar results, 1.62x and 1.63x.

SCREENSHOT 2: DSCR CHART FOR FLAT DEBT REPAYMENT

Now let’s look at what happens when the repayment profile is flat but with a final repayment which is lower than the others. This is not unusual but can seriously skew the average, the plot below shows the final DSCR is significantly higher than the others in the example.

SCREENSHOT 3: FINAL DSCR IS HIGHER

Here you can see that the two methods give very different results. Method 1 gives1.63x and method 2 results in 4.08x.

[image to be updated]
SCREENSHOT 4: LOWER LAST REPAYMENT

Comparing the financial modelling methods for ADSCR

There is a conceptual difference behind the two calculation methods:

• Method 1: calculates the average of the DSCR values over time, treating all of the elements as equally important
• Method 2: weighs each element by the relative importance of the sum of principal and interest in each period

The difference is not obvious when the cash flow/debt service is flat, as demonstrated in the first example. However, this is best highlighted when there are extreme values, such as the final repayment being very small as shown in the later example. The DSCR in the last period is enormously high, which is given equal importance in Method 1 and distorting the overall average.

Which calculation method for ADSCR is correct?

There is nothing wrong with either method. The important thing is to understand what they actually mean and be aware of the limitations.

In certain situations, be aware that Method 2 is probably more meaningful and would be the more accurate representation of the average.

Corality Training Academy – SMART CAMPUS

There are numerous other tutorials and free resources related to financial modelling in Corality’s SMART Campus.

Some of the more popular courses that relate to this topic include:

Project Finance : Concepts and Applications

Best Practice Project Finance Modelling