Semi-annual debt repayment in a quarterly model

In a project finance transaction, we often need to model semi-annual debt service in a quarterly period model. This tutorial demonstrates how to code this concept in a flexible way which minimises a whole range of potential errors.

For illustration, we have prepared a case study of a simple project finance model. Screenshot 1 shows the input page - highlighting the model timing and debt repayment of the senior facility.

Screenshot 1: Input page of the case study

The project has a 10-year operational period and is modelled on a quarterly basis. The financiers are yet to decide the debt payment period to be either on quarterly (3 monthly) or semi-annual (6 monthly) basis (refer to the drop down cell in E53).

Screenshot 2: Counters and flags

Step 1: Counters and flags

The first step would be to calculate number of days in period and counters during the operations. In a quarterly model, the number of days would be 90-92 days in a period. This step is straight forward and is as shown in row 9 and 10 in Screenshot 2.

Step 2: Debt service period

Next is the key step – that is to code the periods to include a debt service flag. The formulas are shown in Screenshot 3 – this is the result when a semi-annual debt payment period is selected from cell E53 in the Inputs tab, note how the result will change if debt service period is changed to quarterly.

Screenshot 3: Code in the debt service period

Period(s) to include (row 18):

The reverse ticker illustrates the number of periods to look back for the selected debt service period and is used in the Sum (Offset) calculation in the next section.

The “Months_Qtr” is a pre defined name which equals to three (3).

Debt service period (row 19):

The formula means if the debt is repaid on a semi-annual basis, then the debt service period would be in Operations number 2,4,6,8 and so on (row 10).

If the debt is to be repaid on a quarterly basis, then the debt service period would be in Operations number 1, 2, 3, and so on. This is the only row that is using an IF formula.

Step 3: Interest and Principal repayment

Number of days for interest (row 20):

Refer to Screenshot 4 – The calculation of number of days for interest payment is coded using SUM(OFFSET) function, using the calculations previously coded in Step 2. Screenshot 5 illustrates if the quarterly debt payment period is otherwise selected from Input page.

Screenshot 4: Senior debt Interest (semi annual)

Screenshot 5: Senior debt Interest (quarterly)

Principal repayment (row 38):

Refer to Screenshot 6 – The flag for the repayment period (row 38) is linked to the debt service period coded in Step 2. Try switching the repayment period to quarterly and note how the calculations will flow through.

Screenshot 6: Senior debt principal repayment (semi annual)

Step 4: Debt ratios, Dividend payment

Note the “periods to include” and “debt service” flag calculated in Step 2 will also need to be linked to other calculations such as CFADS in the debt ratios and dividends payment.

Screenshot 7: DSCR calculation (semi-annual)

Screenshot 8: Semi-annual dividend calculation in a quarterly model

Want more Tutorials?

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