Revolving Debt Facility

Is there a sample debt module that includes a revolving debt facility? Looking to include a revolving facility that draws down and repays as needed based on available cash and minimum cash balance assumption.

The approach I've used is one that I regularly use when building financing models, and I've always liked it because it doesn't require the use of a goal seek. It also includes the ability to:

Specify funds available for each facility, which limits the funds drawn from each facility to this amount in each period if funding is required to meet a closing cash target. Note that I haven't included more sophisticated things like fees on committed/undrawn funds, etc., as the focus of the example is the sweep method.

Specify a ranking for each debt facility (or zero to have them ignored), thereby determining which facilities are included in the cash sweep, and the order of the facilities from which cash is drawn or repaid in each period.

Specify the target closing cash balance in each period. To apply all cash to debt repayment (i.e. run a payback period analysis), enter zero in all periods.

Switch the cash sweep on and off.

The approach uses a combination of calculating the required cash drawdowns and repayments in each period and VBA looping to very efficiently calculate the drawdowns and repayments required in each period to achieve a target closing cash balance. It's only limitations are:

It does not allow for multiple debt facilities (i.e. one for bullet, one for credit foncier, etc.). I would use a separate Cash Sweep module to handle this, and it would require some smart linking to ensure that debt facilities can be added and removed from each debt module without the Cash Sweep module breaking. I'll have a crack at this next time.

I haven't included a payback period calculation, which could easily be included by using a MATCH function to search for the period in which total closing debt is zero when applying all cash to debt repayment

Let me know if this is what you were after, or if I've missed anything.

Inserting this module into your own modular workbook

To insert the debt module containing this cash sweep in this example model into your own modular workbook: