Including Releases or Deposits to DSRA in DSCR and Sculpting

This page discusses modelling mechanics associated with how to treat cash flow that comes from changes in the debt service reserve account (the DSRA) in a financial model when computing debt repayment. If cash flow from changes in the DSRA account is positive, meaning that the DSRA is reduced through withdrawals from the bank account, then some would argue that this cash flow should be included in calculation of the DSCR. After all, it is cash that is available to pay debt service. Similarly, if the DSRA account is required to be increased and you must deposit cash into the DSRA bank account, one could argue that CFADS should be reduced. Including deposits or withdrawals from the DSRA in the calculation of repayments with sculpting and the DSCR is not easy.  It can can cause painful circular reference issues. With some mathematics and VBA you can solve the DSRA moves and DSCR calculation, but it is not easy.

Many years ago I had a very smart student who explained that in a PPP project with a tight debt service coverage, including changes in the DSRA in the DSCR can be an important issue in financial models. I became obsessed with this for years.  When trying a copy and paste macro or a UDF, the process blew up. Later, another person told me that the DSRA balance should be used to pay debt service in the final year.  This is a variation of the same issue.  The last period change in DSRA is the repayment of the DSRA and this change can be included in sculpting.

Developing a project finance model that can evaluate the effects of including DSRA deposits and releases included in the DSCR are discussed below.  Videos and files on this page describe how to resolve this problem with a UDF and some NPV formulas. The project finance structuring analysis below addresses incorporation of DSRA cash changes in debt sizing analysis. If you really want to see how all of debt sculpting in project finance works you can torture yourself by working through the project finance debt sculpting videos and then trying the debt sculpting exercises.

There are a couple of key files where I put the financial formulas, modelling examples and the VBA code for cases where you run into circular references.  You can file these file on the google drive in the Project Finance Section under exercises and then Section D for the Sculpting course.  The first file is the sculpting file that includes a separate section that isolates on the issue of DSRA changes.  The second file is a model with the comprehensive parallel UDF.

Excel File that Includes Sculpting Exercises from Basic to Advanced with VBA

Theory of How to Treat DSRA Deposits and Withdrawals and Simple Examples

Perhaps the ultimate complex issue in project finance modelling is including changes in the DSRA account in CFADS and in the sizing of debt.  This issue can be relatively minor but it demonstrates a lot of programming and theoretical points.  So lets first discuss the theory.  If you are modelling MRA accounts, most would say that changes in the MRA should be included as CFADS.  This smooths out the CFADS to account for capital expenditures for things like re-surfacing of a road. You can then say why not do the same with changes in cash associated with the DSRA account.  As the DSRA account will eventually be extinguished, including the changes in DSRA account will increase cash flow and allow for more debt.  It will thus make the DSRA a less painful item.
The above discussion is arguably all wrong. When you think about the DSRA you could think about the corporate concept of net debt and specifically the notion that cash is tantamount to negative debt. For example, when computing the LLCR as the present value of cash flow divided by debt, you should subtract the DSRA from debt and not add the DSRA to the present value of cash flow in the numerator. This is consistent with the idea that cash on the balance sheet (and that is dedicated to debt repayment) can be called negative debt. The same idea should be applied to computing the DSCR.  Instead of adding DSRA cash to the CFADS, the DSRA cash should be deducted from debt service.  This will increase the effect of the DSRA changes.  The same can be applied to the MRA cash. Maybe the best way to think about this is the large reduction in the DSRA that occurs at the end of the debt term. This money that is received from the DSRA should reduce the debt service on a one-for-one basis. If the change in the DSRA is deducted from the denominator, this happens.  If the DSRA change is in the numerator, the debt service is reduced only in part.
In terms of programming, you cannot try to force the CFADS and then apply some kind of copy and paste macro.  This will create a crazy cash flow.  Instead, you need to use some math.  To solve the problem with equations, you can compute the present value of the DSRA movements divided by the DSCR if you want to use the method at the beginning of the paragraph. Then, you can compute the PV of the debt service without the adjustment for the DSRA moves.  Finally, you can compute the repayments as the repayments for sculping without the adjustment, less the adjustment for the DSRA.  This is summarised below for the case where changes in the DSRA are treated like other cash flows and where the DSRA changes are treated as negative debt.

Change in the DSRA Balance with Sculpting when DSRA Changes are Included in CFADS

The key for incorporating changes in the DSRA account into sculpting is to split the problem into two parts.  First, you evaluate the sculpting formulas as if there were no movements in the DSRA as normal.  Second you make an adjustment to the debt size (to increase the debt size) because of added cash flow that comes from the DSRA releases.  Second, the repayment is reduced by the DSRA moves (it could be increased if the size of the DSRA is increased).
There are two ways the DSRA could apply are to treat the cash flow like operating cash flow (Case 1) or to treat the cash flow as a reduction in debt service (Case 2).  For each of the cases the, the method for computing repayments in the case where the DSCR drives the debt size and the method where the debt size is given and the debt is sculpted.
Case 1: DSRA Changes Treated Like Other Cash Flow and Debt Sizing from DSCR
First, compute the NPV of DSRA moves (this only will really work with the UDF approach) and divided the NPV by the DSCR
Second, Sculpt the Debt without DSRA moves (i.e. CFADS without DSRA Moves/DSCR)
Third, Compute the Debt Balance from Sculpting without DSRA and subtract PV of DSRA Moves/DSCR: Debt Balance = PV(Debt Service from Normal CFADS) – PV of DSRA Moves/DSCR
Fourth, Compute the Repayment as the Repayment from Sculpting Less the DSRA Moves/DSCR
This method is illustrated in the simple example below where the interest rate is zero and the moves in the DSRA are only for releasing the DSRA at the end of the debt term. In the example below, note that the 100 of DSRA is divided by the DSCR of 1.5 to arrive at the amount for sculpting.
Case 1: DSRA Changes Treated Like Other Cash Flow and Debt Size Given
When the Debt to Capital is the Constraint, you need to make the following adjustments:
First, compute the NPV of DSRA moves (this only will really work with the UDF approach)
Second, Compute the LLCR that will yield the correct debt balance with which allows DSRA Changes to be included.  This is LLCR = PV (CFADS+DSRA Moves)/Debt
Third, Compute the Repayment as the Repayment from Sculpting Less the (CFADS/LLCR) and later add the DSRA Move/LLCR to the repayment
 .
Case 2: DSRA Changes Treated Like Negative Debt
First, compute the NPV of DSRA moves (this only will really work with the UDF approach)
Second, Scult the Debt without DSRA moves but do not divide by DSCR (i.e. CFADS without DSRA Moves)
Third, Compute the Debt Balance as Sculpt without DSRA – PV of DSRA Moves
Fourth, Compute the Repayment as the Repayment from Sculpting Less the DSRA
When the Debt to Capital is the Constraint, you need to make the following adjustments:

Illustration of Alternative Methods in Cash Flow

The screenshot below demonstrates how different methods could be presented in the cash flow statement.  For the method where DSRA moves are ignored in computing the DSCR and evaluating sculpting, the DSRA changes are shown at the bottom of the cash flow statement.  In the case where DSRA changes are treated like other cash flows, the DSRA changes are part of CFADS.  In this case a reduction in the DSRA account is treated as positive cash flow for purposes of computing the DSCR and sculpting. In the final year the cash flow is much higher and the debt service requirement may be negative. For the third case where the changes in DSRA are treated like negative cash flow using the concept of net debt, the DSRA changes in the debt service section of the cash flow.

 

The effects of including the DSRA moves in the sculpting process is illustrated in screen shots below. In the first case the DSRA is not funded but instead an L/C is applied. You should look at the equity IRR.  You can press the check boxes and choose different methods.
In the next screen shot, a funded DSRA is applied, but the DSRA moves are not in the sculpting process.  This is the worst case for the DSRA from the perspective of the equity investor.  In this case the equity falls to a lower level. Note that the check box is unclicked. Not only is the IRR reduced to 12.09%, the debt to capital ratio is also reduced.  The DSCR is the same as the case with the letter of credit.
The third screenshot presents the case with the DSRA funded, but the DSRA moves are included in the DSCR using the mathematical equations shown above. In this case the equity IRR increases relative to the case without the DSRA being included. But the equity IRR is still below the best case where the Letter of credit is used instead of a funded DSRA account. In this case the debt to capital is somewhat higher than the case without making the adjustment.  Note that in these cases the Letter of credit fee and the interest income is not included.  Also note that the check box fir the DSRA in the CFADS is checked and also that the CFADS is increased in the final period.

Cannot Include Debt Service Effects of DSRA Changes in the DSRA Itself from Either a Theoretical or a Practical Standpoint

The DSRA balance comes from debt service. With sculpting and changes in the DSRA included in CFADS, the size of the DSRA is driven by the debt service but the debt service comes from the DSRA.  This idea does not make sense and the change in DSRA that is part of debt service should not be included in the DSRA itself.

Consider a simple example. You need liquidity for a trip — some cash in case things can go wrong.  After the trip, you will not need the cash.  You need to decide how much liquidity you will need from your fixed costs.  At the end of the trip you can subtract the money you have in the bank for liquidity from the fixed costs.  The liquidity is the DSRA and the fixed cost is the debt service.  Assume your fixed cost is 100 for three months — a long trip.  Assume you need to put aside 100 for the trip. If the liquidity requirement is reduced for the last period, then the fixed cost is reduced to zero and you don’t need the liquidity anymore. This does not make sense.  The fixed costs still exist and the liquidity account must not be distorted by changes in the account itself.

 

Video Discussion of DSRA Changes and Sculpting

 A couple of videos that work describe the process for sculpting. The first video works through the case where the DSRA moves are included as cash in the numerator of the DSCR.  The video does go into detail about how the VBA programming works, but describes the formulas and how to use the user defined function.
A second video explains the case where the DSRA moves are adjusted in the denominator of and the repayment rather than the numerator. In this case, the change in the DSRA affects the debt repayments on a one for one basis.  This means that if there is cash flow coming from the DSRA of 100, the debt repayments are lowered by 100. You could certainly argue that from a theoretical perspective, if the DSRA gives you cash, it should directly reduce the debt.