TL;DR: a debt capacity excel model I made which will calculate how much debt a company can take on depending on your top line projections
If you a corporate acquirer, or working in Private Equity / M&A, you will look at potential LBO (leveraged buyout) deal flow and wonder about debt capacity as a matter of course. Sure, you can (and eventually will) build a large, standard LBO model (You’re probably an analyst right, and it’s your job to churn these out?), but do you want to miss Friday night doing triage, or do you want to be smarter?
Wouldn’t it be cool to pull out a filing and a broker note during a meeting, type in 10 cells and magically come out with an initial impression if a deal might work?
It’s very useful to have some models to hand to triage deal opportunities and get a quick and dirty view. Meet the debt capacity calculator.
I built this for fun as a way to quickly get a sense of the debt capacity of a company with only a few assumptions. You can rapidly read a P&L, insert a couple of actuals and forecast assumptions and boom- behold a beautiful model with the numbers you need.
When I built it I was going through a phase of wanting to pimp out excel (I clearly hadn’t discovered coding). There’s a whole lot of bells and whistled built in, particularly around formatting. Yes, they’re totally pointless at the end of the day, but it’s really cool and so pretty!
I’m not going to write a ‘how to use’ guide on this. You either know what it’s for, the inputs and variables etc, or not.
If you’re reading my blog, it’s more likely that you are a VC or a founder. So here’s an example of a Pareto model. Download, have a play and see what learnings you can pick up to make some cool models. Geek on!
I have a bunch of models I made for fun years ago I’m not using presently and hope they will be beneficial to others, so I’m putting them out.
Preview of the debt capacity calculator
Usage notes on the debt capacity calculator
To use it you need to have the usual Excel add-ins:
- Analysis TookPal
- Solver Add-in
Also, when prompted you need to allow macros.
Comments 6
i would like to check how it works before leaving a proper comment…
There are many hours that have been put into this spreadsheet!
It looks cool and “could” be useful.
However it is of limited use in its current state given there are no explanations on the process & formulas this spreadsheet uses to derive a result.
One cannot just get a result and say “I can afford X $ of debt” without a clear understanding of how he has reached that conclusion.
Author
It’s free. You can also do the math manually.
I am not complaining and I find it a great spreadsheet offered for free (on which quite a bit of time was spent!). I have the Arzac book (the maths/process looks similar) on hand so I think I can figure things out. I am just wondering how to include a bullet loan instead of a mix of senior & subordinated debt.
Thanks for the model, but it is the first time I saw the “Tau” and “Adj Factor”. Not sure to undertsand what does that mean ? Do you have a little answer ?
Author
Julien – It’s math stuff to show calculations. Study formulas to understand if you want to go to that level.
I made it in 2009.