DSRA's in Project Finance Models

Calculating the DSRA target balance

The most common way the DSRA target balance is calculated in a project finance financial model is as the sum of the coming [X] months debt service. This value can be derived in a number of ways, each with compromises.

• Manually link up (it doesn’t change very often)
• SUMIFS()
• SUM(OFFSET )

Both SUMIFS() and SUM(OFFSET) will give you a dynamic lookforward function however in reality this functionality is only needed during the structuring and term sheet negotiation stage of a project finance transaction. When I am looking for ways to streamline a model, this is an area I can justify hard-wiring; not hard-coding! For example here is the target balance of a 3 month case.

Understanding the DSRA line items

• Initial funding (funds going in)
• Withdrawal to meet a CFADS shortfall
• Withdrawal to meet the target balance (we call this ‘draining the DSRA’)
• Funding to meet the target balance (we call this ‘topping-up the DSRA’)

This post is designed to be read closely with the accompanying workbook rather than detail every calculation - when you review you will see that the DSRA has the following lines each with supporting workings.



The key principles behind these lines are:

Funding a CFADS shortfall
Release to fund a cash shortfall = MIN ( CFADS shortfall, DSRA Balance b/f)

Where:
CFADS shortfall = MAX(0, Debt Service - MAX(0,CFADS))

Target: Release 
Top-up = -MAX( Target Balance c/f - DSRA Balance b/f + Release to fund shortfall, 0)

Target: Top-up 
Actual release = MAX ( Release , 0)
Release = MIN ( Cash Available to Fund DSRA, Target DSRA c/f - DSRA Balance b/f) 

There are some extreme cases which in a perfect world the model would capture but the reality is that in these cases the project is in sweep and the mechanics are somewhat irrelevant. For example, a case that is not captured here "There is a CFADS shortfall, the target balance also falls off due to a following period of significantly lower debt service and the opening balance of the DSRA is not sufficient to meet the shortfall - in theory there is a little extra to be squeezed out of the DSRA to meet a shortfall. However in this event we would consider using Cash at Bank. If the DSRA is not fully funded there is unlikely to be any releases and as a result the project will be in lock-up. 

Important notes on meeting the target balance

The target balance and releases to fund cashflow shortfalls are the critical success factors in modelling the DSRA, here are some tips on the mechanics and how they relate to real-life transactions.

1. Debt service and DSRA balances are structured so that a DSRA is only called upon in an unexpected situation.
2. The DSRA being at its target balance is usually a covenant for distributions to subordinated parties.
3. If there is a shortfall in the period and the DSRA is drawn to fund a shortfall then there will be no cash available for topping the target up.
4. If there is a shortfall in the period and DSRA is drawn to fund a shortfall and the target balance is falling then you can theoretically have a plug for debt service shortfall and a release to achieve a falling target. We don’t model it this way because if there has been a release from the DSRA to support CFADS, the DSRA would be locked-up by senior lenders.

Here are some of the first things I look for as a first pass to see if the DSRA has been properly modelled.

1. Does the sum of the sums of the line items equal zero? They should over the life of the DSRA.
2. Is the DSRA releasing to fund beyond debt service requirements?
3. Is the DSRA causing a circular reference?
4. Is the DSRA finishing up with the life of the loan, too early or too late?
5. Is interest being recognised on the DSRA?
6. In a period of stressed CFADS, including prolonged stress, does the DSRA release correctly all the way to empty? Potentially a moot point as the balance would be swept by senior lenders or receivers in extreme cases.

Success factors

Here are 6 tips for building a professional DSRA into your Project Finance financial model.

1. Be critically transparent, sequential and label lines very clearly – this avoids errors, allows you to debug efficiently (and you will need to!) as well as facilitating a third party understanding of what is happening.
2. Releases from the DSRA all come after the CFADS line.
3. Spend as long testing the model as you do building it.
4. Build the logic up sequentially, sense checking as you go.
5. Be able to completely switch off the DSRA, this makes it much easier to do "with / without" testing and analysis.
6. Target balance "top-ups / releases" being incorrectly prioritised over a release to meet a CFADS shortfall.

Common errors

If you can avoid these you are doing well!

1. Shortfall test is performed on a DSCR that is not purely CFADS / Debt Service in the period. The logic must test “is there sufficient cash in the period to repay the principal and pay the interest that is due in that period".
2. Releases are calculated against total debt service rather than only the shortfall that needs to be fulfilled.
3. The DSRA being drawn <0 resulting in a negative balance!!
4. DSRA releases more than it needs. The DSRA release is limited to the shortfall between CFADS and Debt Service – nothing else.
5. Funding of the DSRA is not modelled as a cash out-flow in the Cashflow.
6. Withdrawal of the DSRA is not modelled as a cash in-flow in the Cashflow.
7. When handling multiple senior repayment tranches not all of the debt service is included in the DSRA target balance.

Avoiding circular references

The DSRA is a magnet for circular references, apart from the initial funding loop, even when you are dealing with advanced debt sculpting / sizing the following points will help you avoid them.

1. Ensure interest is calculated on the DSRA Balance b/f.
2. Don’t include DSRA movements above CFADS in the cashflow waterfall.
3. Don’t include current period debt service in the target.
4. Don’t build interest up within the DSRA.
5. As a last resort manage the target balance profile with a macro.
    

The DSRA goal seek

There is usually a quite expected circular reference when it comes to setting up the size of the initial funding of the DSRA. The circular reference is due to ....deep breath....the DSRA target balance being a function of the Principal which is the refinance of the construction facility which includes the initial funding of the DSRA. In most cases we handle this with a simple goal-seek cut and paste macro, which isolates the circular logic. The code is contained in the accompanying workbook and is a simple Goal Seek within a For Next loop.

For i = 0 to 10

Range("DSRA.Funding.Delta").GoalSeek Goal:=0, ChangingCell:=Range("DSRA.Funding.Applied")

Next

This code is linked to the blue button below. If the macro needs to be run then the informative text  changes accordingly. In a full model this warning would be linked up to a master test result visible throughout the model.

Worked example 1

• CFADS is constant at $25M per quarter
• Debt of $172.85M is repaid over 10 periods, interest rate 10%
• DSRA target balance = 6 months
• DSRA initially funded at $49.74M