Interactive Debt Sizing Matrices

I created these interactive Debt Sizing Matrices after I observed that my colleagues and I were continually running the same calculations when trying value a property and/or size a loan.

Feel free to post/send some feedback.

Attachment Size
Excel File 59.5 KB 59.5 KB
Instructions 125.99 KB 125.99 KB
 

Don't love it. If you use it and it helps good for you(no sarcasm). Not knowing the "model" I think named cells coulda been helpful. I mean I get that you didn't need to use them and there are no nested ifs so it is pretty straight forward.

I've seen plenty of DSCR tables/calcs. Can't you just use a min function(dscr,ltv)? Not being on the lending side, how does a matrix help you? Also you don't look at deals below 5% cap rate? Maybe include loan constant?

 

well the only 'model' is the PMT function in excel, so I don't know that names cells would have been necessary - understood though..

not sure what you mean about just using a min function (where and when and why would you use a min function)..

As for how it helps / how to use it: The DSCR matrix allows you to quickly use any of the inputs that you are comfortable with or know you will anchor to (say cap rate and LTV) and then understand what kind of rate/amortization the property can sustain. Another way is to set the cap rate, rate and amortization and you can understand what max LTV the property can sustain. Just a really quick calculator as a first test - I found that our team would get through the whole model and then when negotiations start they would play with the am/rate in the model and thought this would be easier...

The Percent of Request helps you when the broker sends a deal with their valuation and LTV request - usually the cap rate is super low and LTV is really high so you make that adjustment and can provide quick feedback on where you will be on proceeds and see if there is a deal to be made...

 

Two words: debt yield. Any loan sizing model needs a debt yield calc. DSCR is secondary and really is a pretty worthless metric because of the following: 1). prime rate and LIBOR are super low historically, 2). DSCR would obviously decrease if the floating rate index spikes, 3). DSCR is worthless if the loan has an I/O period....The list goes on. Point is there are really two things that matter LTV and debt yield.

 

Two words: smoking dope. I agree regarding the relevance of debt yield but you would prescribe throwing DSCR out the window? That leaves out one of the most fundamental considerations in real estate credit: how well the property's cash flows service its debt obligations. Neither LTV nor DY tells you that, not alone, not in conjunction, because neither ratio has interest rate as a component. Also, even if a loan has IO, you look at coverage on IO payments and when it's amortizing. And if it's a floating rate loan, you still look at DSCR, up to the cap and down to the floor.

 

Debt yield is a recent phenomenon. In the middle market where I broker both CRE and CRE loans, DY is rarely discussed. LTV and DSCR are the mainstays that have existed for decades for a reason.

When I model a deal, which I've only been doing for about a year now, I always use mortgage constant. It's a great way to get to max loan amount. NOI/DSCR/Mort Const. Yes, then =Min my way back in to it against LTV for a final loan amount.

I find DY relevant and interesting because it has nothing to do with interest rates, amortization or the borrower. It's a nice tool and will be around a long time.

 

Not to worry. Even if tomorrow everyone started evaluating DY for what it's worth, bad loans would still be made.

Such is the credit cycle, and it's something to celebrate - it's the reason those intrinsically attractive buying opportunities exist for flexible capital.

 
PacNumber:

I can name more banks that don't use DY then do. They all preformed well through the financial crisis.

What is your product type, risk spectrum, and location for deals? My group does opportunistic - core plus, and for each ends of the spectrum DY is / was an important metric for the lenders (banks & debt funds). I would say it was mostly relevant for good news proceeds, however, with regards to initial funding as well (regarding core plus / already income producing assets). All the main players: Wells, BofA, City, US Bank, PNC, etc.

 

I'm a broker in San Diego. I do mostly mid market stabilized deals when it comes to multifamily and commercial loans. Mid market out here is probably $5mil to $15mil. Product type is 3-10yr fixed. Risk is pretty low. Location is Arizona to Washington state. I've know a rep at Wells for 5 years. Never once heard him say DY. I have countless ties as Chase on the MF and Commercial side and its not come up once. Union Bank is a major player here on mid market stuff. Great rates. No DY analysis. Union has almost no exposure in the for sale housing market and did just fine through 2008-2012. All with no DY underwriting. I can name many others that have been around along time.

Each company has to do what they have to do. I think DY might be more of an institutional thing...bigger deals.

Chase doesn't even ask for tax returns.

 
Best Response

TL/DR: Debt yield is a critical metric for any opportunistic deal and for most fund/private lenders.

I see or discuss DY on almost every deal we look at, especially for transitional or construction deals. Banks and lifecos tend to get stuck in their old ways (and many don't touch construction or opportunistic deals), but all the funds look at this metric very closely. Think of a value-add deal - if you as a lender are going in at a 4% debt yield, and the property stabilizes at a 5% DY for an office, is that enough to get you comfortable? Was enough value really added regardless of what DSCR and LTV you are at in the end? It's the metric that is most closely correlated to cap rates - if you as a lender are at a stabilized 5% DY (essentially the lender's cap rate) and office buildings in the area are trading at a 7% cap rate, how are you going to trade out of the asset and get all your money back in a downside scenario?

Appraisals can be total bs, and as a result, LTV is a metric that can be easily manipulated by the appraiser if they are pushed "to get there" by any of the involved parties. Also, LTV can drastically change in just a few months based on the market sentiment and therefore the appraisers outlook. Just think of a land appraisal in NYC now vs. two quarters back.

DSCR is a metric that can't be manipulated as easily as the last cycle as banks now define DSCR tests based on a predetermined rate/constant and amortization regardless of the actual loan terms. Either way, this will tell a lender nothing about how much yield they will make on that money that they put out. How good is a loan for a lender if it's getting 1.50x coverage, but generating only a 2% debt yield? Not an issue for lenders that aren't yield focused (see: banks and lifecos) and just need to put out lots of money primarily on core/stabilized assets. On the other hand, all funds and private lenders will look at this as their main metric since they need to make a certain return on their committed capital, so if their loan is at 60% LTV and 1.50x DSCR but only generating a 5% DY when they need to make 9%, it's not going to cut it.

Intrinsically, all of these metrics are tied together and should be looked at by all lenders as another factor to help manage risk and raise any red flags during underwriting. Just my $0.02.

 

Not sure if you are confusing debt yield vs interest rate. Debt Yield is dividing NOI over the current loan amount, so it really wouldn't really tell you how much yield you are going to make unless you are taking the property over... Also DY can change just as fast, If not faster than LTV - imagine if a large tenant vacates with 30% of your income and now debt yield is sub 3%

 

Ullam sit eligendi maxime reiciendis autem eaque. Id et ex tempora eius necessitatibus beatae. Nisi iste expedita aut quae dolor. Sint quia facilis velit alias commodi qui. Ut ipsam distinctio molestiae nobis. Ratione dolorem rerum deleniti natus repellendus ipsum ex a.

Incidunt quas cum sed repudiandae impedit. Incidunt facilis fugiat quis et fuga possimus ducimus. Quis inventore praesentium consequatur.

 

Velit eaque nemo fuga aut sequi nam repellendus. Quo harum iusto ea aut sed rerum. Rerum quia quos voluptatum non ut pariatur alias. Corrupti quasi voluptatem ratione dignissimos perferendis itaque.

Ipsa cupiditate sunt sint qui quod itaque. Sit est dolor sit sequi ratione quod quaerat.

Officia est sed at iusto dolores dicta omnis. Harum expedita non ipsum nam perspiciatis laudantium. Et expedita velit nihil corporis ipsa accusantium sint. Animi aut perferendis voluptate illo et vel animi. Et odio et eaque enim est sunt voluptate. Cumque provident qui voluptatem voluptatem.

Facere alias reprehenderit qui qui delectus fugit hic. Velit adipisci dicta neque eligendi commodi sunt quas. Quia in at dolores veniam quia et distinctio.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (85) $262
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (65) $168
  • 1st Year Analyst (198) $159
  • Intern/Summer Analyst (143) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Betsy Massar's picture
Betsy Massar
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
dosk17's picture
dosk17
98.9
5
GameTheory's picture
GameTheory
98.9
6
Secyh62's picture
Secyh62
98.9
7
CompBanker's picture
CompBanker
98.9
8
kanon's picture
kanon
98.9
9
pudding's picture
pudding
98.8
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”