Revolving Debt Facility

Hi Ryan - there's not one on the Modano website but we could mock something up for you pretty quickly.

I've always done these with a VBA macro - i.e. cash sweep/payback period analysis. Is this what you're expecting?

Also, what time series are you using - e.g. annual, monthly, annual historical & forecast, etc?

Ok Ryan - I've built a cash sweep (i.e. revolving debt facilities functionality) into the debt module within a monthly forecast-only financial model and attached it to this comment.

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:

1. 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.
2. 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.
3. 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.
4. 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:

1. 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.
2. 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:

1. Insert (or replace your existing debt module with) the debt module from my example modular workbook into your modular workbook (using the Insert from File tool)
2. Copy the VBA module named 'mCashSweep' from my example workbook into the VBA project within your modular workbook.

Let me know if you have any issues.