Debt and Revolving lines of credit.  In many instances, you will need to model multiple lines of debt and revolving lines of credit with different and interlocking credit terms.  This example focuses on that task.  Other portions of the model are truncated to make it easier to follow the steps needed in modeling debt and revolvers.  Please download the free revolver example Excel spreadsheet:  Revolver.  This example will focus on debt mechanics and will skip basic model mechanics.  Please refer to the simple model for more explanation on basic mechanics.

  1. Profits, capital expenditures, and working capital are all arbitrary inputs to illustrate the functionality of the debt portion of the model.  Other inputs germane to debt are located below row 85 of the spreadsheet.  Debt payments are handled in the cash flow statement and the debt is a result of the payments or borrowings.  This is more straightforward than the approach of directly changing the debt level and deriving the cash flow result.  In reality, it is the payments that are determined anyway.
  2. Interest expense (row 7).  Interest expense can be calculated all in one line (including income).  However, your model will be easier to use, follow, debug and create if you refer to a more detailed schedule (on or off of the sheet) for calculating interest.  Here, interest is sub-calculated on row 59.
  3. Financing cash flow, revolver (row 41).  The revolver payments and draws refer to a sub-schedule that is covered later.
  4. Senior debt payments.  This formula appears complicated, but its function is simple.  It divides the borrowed amount by the tenor of the debt to create a payment amount.  It then applies the payment amount only if the prior period debt is greater than the payment amount.  If not, it applies the amount of the prior period debt as a payment.  Because of this second function, the formula automatically stops paying when the debt is exhausted.  Manipulate the term of the senior debt in C95.  Note how the length of repayment adjusts in the cash flow statement and how the debt is retired on the balance sheet.
  5. Subordinated debt payments.  This formula is the same as the senior debt formula, except that it has the added feature of not applying a payment at all unless the senior debt is paid off in the prior period (the first “if” in the nest).  Many subordination agreements function such that the senior note is paid off first, or paid down to a certain level prior to repayment of subordinated instruments.  One could change the “>0” expression to refer to a senior debt input to trigger sub debt payment upon a certain senior debt level.
  6. Revolver mechanics (row 61).  In general, a revolver allows for periodic borrowings and provides some sweep mechanism to pay back the revolver with excess cash so that it is quickly extinguished.  It is often the case that term loans have fixed payment schedules that are served even if revolver borrowings are used to pay them.  As such, the term loans are paid first and the revolver deals with the “leftover” cash.  To find the leftover cash, first determine the amount of cash that could be used to pay the revolver (or is necessary to borrow).  Row 64 adds up the cash flow including term debt payments and the opening cash on the balance sheet.  This is the cash position of the company if there were no revolver.  Some revolvers sweep 100% of excess cash and allow borrowings up to specified limits.  Others are more complex, with features such as limiting the amount of cash that is swept (% of cash flow, hard minimum cash, etc.).  To add complexity, this model has a revolver rule that repays all but $1,000 of cash.  Note that the formula in row 65 subtracts this amount only when the revolver is being repaid.  When this adjustment is made to the available cash or total need, the borrowing or repayment is the result.  The actual repayment calculation (row 69) tests against the outstanding balance so that over-repayment will not result.  Needless to say, you could put all of this functionality in one formula in the cash flow statement, but others will have great difficulty understanding what is going on and you will not have high confidence in the result.
  7. Look back at Net Cash Flow (row 47) and Cash EOP (row 50).  Here you can see if the revolver logic is functioning correctly.  When the company has cash or is making cash, the revolver should be paid down with all extra money as long as at least $1,000 is left on the books.  When the company is short cash, it will borrow exactly what it needs.  In 2010 (column C), the company had $6,556 of cash to pay against the revolver.  It was only required to pay $5,556 under the terms of the agreement.  However, the revolver balance was only $5,000, so the entire balance was paid off.  In 2011, there is enough cash on hand to meet all obligations.  In 2012, the company is losing cash.  The revolver borrows enough to make cash zero at the end of the year.  In 2013, the company again borrows.  Then, in 2014, the company begins to pay off the revolver.  As it does so, the terms of the agreement allow for the company to keep $1,000 on its books, so this amount is reserved.
  8. Most revolvers (and other credit agreements) are subject to covenants.  Here, some example covenant tests are included below row 73.  Many common covenants (such as minimum tangible net worth, debt to equity, current ratio, working capital ratios, portions of A/R or inventory, debt/EBITDA, etc.) are not included for brevity and because the example does not contain a complete data set.  Most often, it makes sense to separate credit tests and design them to “flag” upon failure.  You could wire the revolver to stop drawing, but that would most likely result in negative cash (which could go unnoticed).  It is better to see when you trip and what you trip so that inputs, credit agreements, and business plans can be adjusted to prevent a problem.  Here cell A74 contains a simple aggregation of the tests provided.  This feature might not matter with two tests, but with multiple tests off sheet, a flag in a prominent place will help alert the user that parameters have been exceeded.