Debt repayment modelling - multiple methods

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 processes. 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

Screenshot 1: Debt input of the case study

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 E50, i.e.

• Annuity (Credit Foncier or Equal P+I)
• Equal Principal (Straight line equal P repayments)
• % Profile (User to enter % repayment profile)

How to model such multiple debt repayment methods in a transparent way and at the same time it needs to be dynamically activated by a single switch (E50). 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 (Screenshot 2, row 54) before it is activated in the calculation.

Screenshot 2: Input for the repayment profile

Step 1: Senior debt facility account

The first step would be to build the debt account for the senior facility such as shown in the screenshot below. For the time being leave the principal repayments line (row 19) empty.

Screenshot 3: Debt facility account

Step 2: Interest payment

The next step is to model the interest payment for the debt (row 29). The formula is simply:
Interest = % per quarter * Debt balance b/f

Screenshot 4: Interest payment calculation

Step 3: Modelling debt repayments

Step 3 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 installment. Note that the interest payment is linked to the earlier line calculation in step 2.

Screenshot 5: Annuity repayment

Modelling of Equal Principal repayments

The next screenshot illustrates how to model the repayment for the equal principal (straight line) method.

Screenshot 6: Equal Principal repayment

Modelling a % of principal repayment profile

Next is to model the third debt repayment method.

Screenshot 7: Repayment profile percentage repayment

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.

E36: Link the switch for the selected method from Inputs. In this example the Annuity is selected.

B33:36: Build a binary code cell – if the Annuity is selected the cell will turn to 1 (red) or 0 (white) otherwise.

Row 33:35: Populate the debt repayment under each method and multiply with the binary cell in B33:36. The idea is when a particular method is selected then it will be activated or zeros otherwise.

Row 36: This row is to code the applied method which is simply the sum of row 33:35.

Screenshot 8: Populate debt repayment method

Step 5: Link the applied repayment to the debt account

The final step is to link the applied repayment (row 36) to the debt facility account.

Screenshot 9: Link to the debt account

Want more Tutorials?

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