Free tutorial (PDF + XLS) on financial modelling of DSRA in project finance without circular references. The Debt Service Reserve Account (DSRA) works as an additional security measure for lenders as it is generally a deposit equal to a given number of months projected debt service obligations. This tutorial explains how to code a transparent and efficient DSRA and how it is linked to the Financial Statements without circular references.
This tutorial covers the theory and you can download (bottom of the page) an example DSRA spreadsheet. For more debt modelling related items, please refer to our financial modelling training course ‘Essential Project Finance Debt Modelling‘
Most, but not all, project finance transactions have requirements for a Debt Service Reserve Account (DSRA or DSRA/c).
The purpose of a DSRA is to provide a cash buffer during periods where Cash Available for Debt Service (“CFADS”) is less than the scheduled payments. The existence of this buffer allows some breathing room for Operational issues to be resolved and / or in more extreme situations the debt to be restructured before the borrower defaults on the debt.
Operation and funding of DSRA/c
The DSRA/c is usually funded up to a dynamic target balance. The target balance for the DSRA/c includes both the interest and principal repayment amounts. This might be set at three (3), six (6), nine (9) or twelve (12) months or may even be a fixed amount.
The funding method for the establishment of the DSRA/c (“Initial Funding of DSRA/c”) is usually stated in the Term Sheet which could be one of the following:
- Funded in full on the last day of Construction ;
- Partially funded on the last day of Construction, then built-up. from the Project’s Cashflows; or
- Completely built-up from the Project’s Cashflows.
In terms of positioning in the cashflow waterfall, the Cash Available to Fund DSRA/c is ranked after Debt Service but takes precedence over any Payments to Equity, thus providing an additional security for the lenders.
A DSRA has two (2) modes of operation:
Modelling DSRA in a Project Finance Model
Modelling the mechanics of a DSRA involves linking-up the formula within various components of the Project’s
Cashflows and DSRA itself. Essentially, modelling the DSRA involves Cash Inflows and Cash Outflows as described below:
Cash Inflows (i.e. Additions to DSRA from Project’s Cashflows)
- Initial Funding of DSRA: Various ways of funding are discussed above.
- Funding from Cashflow: This is funding from the Project’s Cashflow (using Cash Available to Fund DSRA) to top-up the DSRA to the Target Balance.
Cash Outflows (i.e. Releases to Project’s Cashflow from DSRA)
- Release to Cashflow (during distress): This is the cashflow release from the available balance in the DSRA to fund the shortfall in CFADS.
- Release to Cashflow (excess cash released) : This is the release from the DSRA to reduce the balance down to its Target Balance, including the release on final maturity
Screenshot #1 illustrates the elements and calculations of the Debt Service Reserve Account.
This example assumes that the Initial DSRA/c is debt-funded on the last day of Construction and the Target Balance is equal to the next three (3) months of debt service. A binary flag is created to check if the DSRA/c is fully funded during the debt term and this flag is commonly linked to the Equity distribution test. Generally, interest is earned on the opening balance of the DSRA/c and recognised in the same way interest on cash balance is in the cashflow waterfall.
Screenshot #1: Elements and Calculation of DSRA/c
Position of DSRA/c in the Financial Statements
Please refer to Screenshot #2. The Cash Inflows to / Outflows from DSRA/c is linked to the cashflow waterfall, and the Closing Balance of the DSRA/c forms part of the Current Assets in the Balance Sheet. As the Initial DSRA/c is debt-funded in this example, it is positioned before Debt Funding in the cashflow waterfall.
Tips to keep in mind when modelling a DSRA/c
Care must be taken when modelling the DSRA/c to avoid circular references.
This is particularly if the initial balance is debt-funded during the construction period. There is usually no reason for a circular reference to occur due to the DSRA/c. The core logic itself is not circular.
Calculating the Target Balance using the debt service for the next period could result in a circular reference if Interest on the DSRA/c is being considered. This can be solved using a macro or other modelling techniques, which are often a reasonable approximation.
Screenshot #2: Position of DSRA/c in the Financial Statements
It is important to check if the mechanics of the DSRA/c are sensible in the Base Case as well as in other Scenarios, especially in Downside Scenarios where the DSCRs often fall below 1.0x. For example
- Funding from the Project’s Cashflows to top-up the DSRA/c should not exceed the Cash Available to Fund DSRA/c.
- The balance of the DSRA/c should never be negative.
- The ‘addition to’ and ‘release from’ the DSRA/c should not occur concurrently.
- In the Base Case, besides the initial funding and the final release, all other DSRA/c movements should be minimal.
- The sum of all cash movements + Initial Funding should equal zero.
- The DSRA/c balance should be zero at the end of the loan life and should gradually decline in the periods leading up to that time.
- The release from the DSRA/c during a period of distress should only be sufficient to preserve a DSCR of 1.00x.
We have another article covering the topic of how Debt Service Reserve Accounts work in relation to Sculpted Debt Repayments without circular references.