Debt Repayment Modelling – Multiple Methods

Download this workbook

Financiers are often required to analyse multiple debt repayment methods in a project finance transaction. This process is particularly important in the structuring or credit approval process. This tutorial demonstrates step-by-step techniques on how to dynamically build such optionality into your financial model.

For illustration, we have prepared a case study of a simple project finance model. Note that in this tutorial we are focusing on modelling the debt repayment, and not on the operational side.

Screenshot 1 shows the input page for the senior facility, highlighting the different debt repayment methods that will be built into the model.

The financiers would like to dynamically incorporate the following debt repayment methods for the senior facility in the model (refer to the switch/drop-down cell in C6):

• Annuity (credit foncier or equal P+I)
• Equal principal (straight line equal P repayments)
• Repayment Profile (user to enter % repayment profile)

How to model such multiple debt repayment methods in a transparent way, and at the same time, needs to be dynamically activated by a single switch (C6). For example, when the switch is turned to annuity, then the annuity style repayment method is activated in the model.

Similarly, when the % profile is selected, then the user is able to input the repayment profile (see screenshot 2) before it is activated in the calculation.

Step 1 – senior debt facility account

The first step would be to build the debt account for the senior facility, as shown in screenshot 3. For the time being, leave the principal repayments line empty.

Step 2 – interest payment

The next step is to model the interest payment for the debt (screenshot 4). The formula is simply:

Interest = % per quarter * debt balance b/f

Step 3 – modelling debt repayments

The next step is to model the debt repayment under each of the repayment methods.

Modelling of an annuity repayment schedule

We have previously covered how to code the annuity repayment for debt facility, or you might have done this before.

Screenshot 5 demonstrates our preferred formula to calculate the annuity instalment. Note that the interest payment is linked to the earlier line calculation in step 2.

Modelling of equal principal repayments

Screenshot 6 illustrates how to model the repayment for the equal principal (straight line) method.

Modelling a percentage of principal repayment profile

Next is to model the third debt repayment method (screenshot 7).

Step 4 – populate the calculated repayments

Next is the key step – that is, to populate the debt repayments under each method, as calculated in Step 3.

• C46: Link the switch for the selected method from Inputs (in this example the Annuity is selected)
• Row 44:45: Populate the debt repayment under each method
• Row 46: Use an UNDEX(MATCH()) formula to first match the selected repayment method in C46 with the appropriate repayment label and then use the result to index the correct repayment method row.

Step 5 – link the applied repayment to the debt account

The final step is to link the applied repayment to the debt facility account.

Corality Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

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

• PPP/P3/Infrastructure Financial Modelling and Transaction Analysis
• Advanced Project Finance Modelling