How to Model a Debt Waterfall (Debt Schedule) - Investment Banking Financial Modeling Skills

In this video, we discuss 3 types of debt instruments - revolving credit facilities, term loans, and bonds. There are unique properties for each type of instrument and each requires slightly different modeling techniques.
Disclaimer: XYZ is an illustrative company and any resemblance to a real company is purely coincidental.

[@majinnhec]

Excellent video - straight to the point and helped me answer a question that I had really quickly. Huge thumbs up!

[@abdullahiahmed102]

Thanks for the video! Was super helpful.

[@ExcelforFinance_1]

Glad to hear it! thanks for watching!

[@saritsrinavakul3416]

Hey! is there any chance you could do your take on a complete walkthrough model on a particular company (from scratch)? that'll be an interesting video.

[@ChiChi-sw5iu]

Thank you. I really appreciate this 🙏

[@ExcelforFinance_1]

Glad it was helpful!

[@AnnaliseStevenson]

Very helpful, do you have this template available for download?

[@mohanishgolatkar2756]

Firstly, thank you for the brilliant video! I have one quick clarification and one observation. A) The interest expenses (which are mandatory) shown for the debt instruments in rows 88, 97 and 106 would flow into the income statement and then to cashflow statement and effectively reduce row 71 - cash available for discretionary debt paydown - is this understanding correct? B) When interest expense for revolver shown in row 85 changes to 1 at 2:50, the net interest expense reduces from 2 to 1. I was thinking it should increase from 2 because using the revolver is more expensive Libor (0.6%) + 500 bps (5%) than commitment fee (0.33%).

[@rusmart1234]

This is a great video. Love the step-by-step and the formatting for presentation. Can you post the Excel model so that I can see the exact logic in each cell? Actually I see in the comments "we can't share the model at this time." Just for clarification, you can't or won't? Its your model and your choice. Just trying to understand the reason for not sharing. However, can you share the logic in row 154 on the FCF tab? What is the cell logic for the check? Thank you.

[@no-rq7fp]

get a job at mcdonalds lil bro

[@richieeasterly1728]

Where do you grab the LIBOR rates from? How do you choose which rates you should use?

[@mypage9331]

do you have example of convertible bond?

[@micahsnow346]

So I'm brand brand new to this and will probably have to watch this multiple times to understand...one thing that's puzzling me is the mandatory amortization %. Where do you find information about this in SEC filings? It's puzzling for me because I can usually only find information on the maturity and the interest rate.

[@hu123bnn]

in the term loan when calculating mandatory payments, I kept getting the same 50 and it goes until the end of my estimates. not like you are showing in the video. Is there anything wrong with my excel set up?

[@NHN20]

Would the Mandatory Payment of $150 not reduce the FCF available for Discretionary Pay Down. So wouldn't the discretionary paydown be $182, because that's the cash available after paying $150 of the madatory paydown (from the $332 cash)?

[@jayfreeman2454]

Great easy description I have found to share with students. Would be helpful if you provided the excel worksheet also

[@ExcelforFinance_1]

Thanks for watching and glad you find it helpful. We're figuring out a way to share the excel, stay tuned!

[@jayfreeman2454]

@@ExcelforFinance_1 good because that's what separates the pages from who gains a major following especially given the firms like Wall Street prep share whole lbo models

[@deathroll914]

@Excelforfinance_1 do I include current liabilities in the debt stack? Also, when I do total debt and net debt calculations on a comp set, do those include total liabilities or only long term liabilities? Thanks!

[@srikanthkambhampati1073]

When you talk about debt, you talk about anything that you are paying interest. Most of the debt reporting is done in Longterm liabilities, but, there will be a current portion on long-term debt reported in current liabilities. Interest bearing debt is the key here.

[@andreserradeoliveira4244]

Hello, thank you for the video. Is it 100% necessary to have a circular reference wihtin the model?

[@ExcelforFinance_1]

It is not 100% necessary but it is more accurate with a circular reference. One way to avoid a circular reference is to calculate interest payments using just the beginning balance of debt (instead of average of beginning and ending). However, using the average is more accurate since interest is typically paid multiple times over the course of the year and the balance of debt can change drastically from beginning of year to year end. Let me know if that is helpful.

[@victorz1200]

Thanks for the video! If the bond was issued at discount or premium, will the interest be based on the face value of the bond rather than average of BB and EB?

[@ExcelforFinance_1]

Interest is always based on the face value

[@ro5252]

@@ExcelforFinance_1 hello, any chance you could do a real world example on a highly levered company (for example Qurate Retail). That would be so great, thanks!

[@DL-vg9np]

for the draw/(paydown) row of the revolver, noticed at 1:32 that the f86 wasn't fixed, but at 2:45 it became $f$86, so which is the correct formula for dragging across? thanks!

[@phillthethrill7973]

My guess is that you don’t anchor it. This way it compares the amount remaining with your cash deficit. Example: if I start the year (previous year’s ending balance) with 100 undrawn and drew 30 throughout the year, then my amount available in my RCF is 100-30=70.

[@larryhora2346]

I think the formula in G82 for the Revolver doesn't handle the scenario in which the cash available for discretionary paydown is an amount way less than the undrawn allowed. In short, if I put -1,000 rather than 362 in cell G71, the number I get in G82 is off. I added an absolute value function in the formula to take care of this. Do you guys think is right? I would love to hear your feedback in case I am seeing it wrong. Thanks.

[@ExcelforFinance_1]

Hi Larry, that is a great point, thanks for your comment. In those cases, you would need an additional inflow of cash beyond just the revolver to plug the difference. This is a slightly more advanced modeling concept but you could include an equity investment line to show where additional investment is needed. That is one of the great things about free cash flow models - they can signal the shortfalls in advance. Hope that helps!

[@ro5252]

@@ExcelforFinance_1 hello, any chance you could do a real world example on a highly levered company (for example Qurate Retail). That would be so great, thanks!

[@lourencosousacoutinho918]

Great video, could you please share the Excel file?

[@broplzjuststop]

How do letters of credit affect revolver utilization and payback/would this model allow for these variables??

[@ExcelforFinance_1]

LCs outstanding typically reduce revolver availability

[@swetapatra]

thank you so much for this vid, please someone tell me what do we have in cells 71 and above, what is minimum cash balance? do we assume this or is it given in the reports? i only have information about minimum revolver balance that i need to maintain month on month

[@ExcelforFinance_1]

I'll make another video to clarify the lines leading up to discretionary cash flows. The min cash balance is usually an assumption. Its the minimum cash the management teams wants to maintain on the balance sheets (so cannot use it to pay down debt)

[@ExcelforFinance_1]

We show the free cash flow build in this video - kzbin.info/www/bejne/bKuwlKSAiqqeepI - Let me know if that helps!

[@andoy1850]

Hey, this schedule is applicable for LBO models as well right?

[@ExcelforFinance_1]

Yes, that's right, the same schedule can be used!

[@falayyou]

Can you please share the worksheet?

[@ExcelforFinance_1]

Thanks for watching! Unfortunately we can't share the excel at this time but please let us know if you have any questions in recreating it

[@sameersood5699]

Can you please share the worksheet also?

[@ExcelforFinance_1]

Thanks for watching! Unfortunately we can't share the excel at this time but please let us know if you have any questions in recreating it

[@Schmettgala]

Is this model up for sale?

[@Vinckw]

Will this be required of a graduate IB analyst for an excel model case interview?

[@ExcelforFinance_1]

Hi, if there is a model case interview, I would assume yes. But that being said, I did not have any model cases for IB analyst interviews. Hope that helps!

[@swetapatra]

Also is cell 71 the Total liquidity ? but total liquidity = available cash + undrawn revolver

[@ExcelforFinance_1]

Hi, cell 71 is not total liquidity, it is cash flows less mandatory payments and min cash. This line lets you know how much cash you have available for discretionary debt paydowns. Hope that helps!

[@davidecortellino6330]

what happen inside the cell of Interest expenses total for revolving? the formula is not showed in the video

[@ExcelforFinance_1]

Hi there, that cell adds row 85 and 87 and multiples by -1 to make it negative. Hope that helps!

[@chandrakantkamble3091]

nice 👍pls share excel sheet

[@shaikhahmed45]

Can you make a Financial model + Valuation of any listed company from scratch ? If possible plz make a video on that

[@ExcelforFinance_1]

Good suggestion, thanks! Stay tuned!

[@ExcelforFinance_1]

@@ro5252 hi there! Thanks for the suggestion, I do care but these videos are meant to help you create your own models. The debt waterfall would be very similar for a highly levered company as well, but you may need to build in a bigger contingency for what happens if the business runs out of cash to pay amort or interest. Perhaps an equity investment or a sale of assets which would flow through the walk from EBITDA to cash flows. I hope this is helpful! If there are specific questions I can help answer, more than happy to!

[@ibrahimdaas]

Very helpful, do you have this template available for download?