Average DSCR - How to Calculate?

There are two different ways to calculate the average DSCR that could result in different numerical outcomes. What are the methods? What are the limitations that we should be aware of? And which one shall be used?

DSCR (Debt Service Coverage Ratio) 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 average DSCR is an important output in a project finance model.

There are two ways to calculate the average DSCR:

• Calculate the average of the period by period DSCR’s over the life of the loan
• Divide the total CFADS over the life of the loan by sum of principal and interest

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 further discuss why they are different and which method is to be used particularly when we are dealing with exotic cashflows or repayments.

Method 1 – Average of the period by period

This may be the most common way people used when calculating the average DSCR.

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 utilized
• ARRAY function is activated with “Ctr + Shift + Enter”.

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

Method 2 – Total CFADS over sum of P+I

The average DSCR is calculated using the simple steps below:

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

Average DSCR = Total CFADS (life of loan) / Total P+I (life of loan)

Comparing the two average DSCR methods

Let’s take a look at an example in Screenshot 1 where the CFADS is pretty much on a straight line profile and the debt is repaid on annuity basis. The life of the loan is from Jan-10 to Mar-14. Period by period DSCRs are then calculated during the life of the loan and plotted as shown in Screenshot 2.

Screenshot 1: CFADS vs. Annuity debt service (example 1)

Screenshot 2: DSCR plot (example 1)

How to calculate the DSCR using the two methods? Do you think there will be difference? Refer to Screenshot 3.

Screenshot 3: Average DSCR (example 1)

You can see that the sum of CFADS over the life of loan is $86.6 million and the sum of P+I is $46.4 million, therefore the average DSCR calculated using Method 2 is 1.867x. If we calculate the average using Method 1 then the result is 1.867x too!

Now, let us take a look at different repayment profile. Refer to Screenshot 4 – the final repayment being very small compared to the other earlier periods. Period by period DSCRs are plotted in Screenshot 5.

Screenshot 4: CFADS vs. Debt service (example 2)

Screenshot 5: DSCR plot (example 2)

Similar to the first example, let us calculate the average DSCR using the two methods and see how different the outcome in Screenshot 6. The average DSCR calculated using Method 1 (2.027x) which is much higher compared to that calculated in Method 2 ($86.6 mil / $45.9 mil = 1.886x)

Screenshot 6: Average DSCR (example 2)

What is the difference in the DSCR calculation methods?

There is a concept difference behind the two calculation methods:

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

The difference is not obvious when the cashflow / debt service is flat such demonstrated in the first example.

This is however 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 (refer to Screenshot 5) which is given equal importance in Method 1 and distorting the overall average

Which calculation method for average DSCR is correct?

There is nothing wrong with both methods. 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.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...