Modeling debt paydown monthly
I am working in a 3-statement monthly operating model that sums 12 months into a consolidated yearly output.
I have to take on a revolver and term loan one year into the model. Having trouble showing the debt paydown schedule because its by month. Any advice is much appreciated.
Maybe I am looking into this too simply, as I am drinking at the moment, but why don't you just sum the payments? If you did the revolver (or even the term loan) on one row, you may want to split it into two rows of payments and issuance. Sum the monthly payments to get an annual.
the company may generate enough cash to "wash" the monthly payment. one way of showing this is to show monthly model or just footnote "note: company draws on seasonal revolver of $xx during the month of x"
Would recommend you cash sweep the revolver (ie assume any extra cash generated goes to pay down the revolver balance) and compute interest on the average balance each month (1/12 * annual interest rate*avg( begin bal, end bal)and figure out if the term loan has any sort of amortization. If there is amort - don't sweep the TL, just have 1/12 the interest and the amort each month (ideally this is simple and just straight line amort so you can assume 1/12 of the amort each month) - you should have a monthly cash balance yes?
Thanks for your help. This might be a stupid question, but I modeled in a revolver that will draw down the total cash need / full sweep with no limit - the company needs capital midway through the projection period, so there will be no cash on the B/S and heavy revolver draws.
I want to show the total debt to LTM EBITDA for the projection period to get a sense for how much equity/debt we may need. Does it make sense to show total debt as the sum of the past 12 months of End Revolver balance (as an annualized number), or is it only correct show one month of debt drawn at a point in time over LTM EBITDA to derive the multiple..?
Appreciate the help, thanks
How is it determined by the Credit Agreement? Typically this is only done on a quarterly basis.
There is no credit agreement - this is for deal structuring purposes pre-investment, and a few years into the projection period. No actual debt on the company now.
Cum et iusto nesciunt. Animi vel sed ullam velit dolores corporis. Magnam quos voluptatem accusamus molestiae animi placeat non dolorem.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...