Development Model - Mezz

REValuation:

Could anyone send me a spreadsheet with a mezz option?

I'm supposed to edit a pre-existing acquisition model; I'm trying to keep it "user friendly" and would like to add a mezzanine option that would re-lever the property back to the original LTV. I have no mezz experience and I'm guessing I just need to use some basic logic-statements, if anyone could provide some insight on this I would really appreciate it.

Yeah, I mean... adding mezz shouldn't require someone sending you a built out model - it's not exactly rocket surgery. I assume that in your excel model you've already got a "financing" sheet that shows a full month by month amortization schedule for the first mortgage portion that the mezz will sit behind. If you don't have this, it's nothing to be ashamed of, you just aren't an expert in CRE modeling, but you've got bigger problems than the mezz loan. I don't mean this in any kind of insulting way and feel free to ignore if you already know this kind of shit, but if your existing model doesn't have a well built amortization schedule for the first mortgage feel free to PM me and I'll show you how to do it or just help you out with RE modeling technique in general if you want.

Anyways - assuming that you have a first mortgage amort schedule, the mezz schedule will be a lot simpler. You can either add another page or put the mezz loan next to the first mortgage on the same page. The top left area of your first mortgage amort schedule page should already be an "assumptions" or "model drivers" section that lists all the inputs for the first mortgage - coupon, amort schedule, settle date, first pay, maturity - and you should be running your amort schedule off those inputs so you can change assumptions quickly if necessary.

Just title your initial "assumptions" section thing "first mortgage assumptions" and make an identical thing at the top of the sheet called "mezz assumptions." Lets assume the notes are co-originated, so the dates are the same, and I obviously haven't seen the term sheet or whatever, but market standard right on mezz is like 10-11% fixed coupon, no amort, lets call it 10 years (just match it to however long the first mortgage is.) Obviously you'd have to consult the term sheet of the deal, but those are pretty reasonable assumptions in my mind knowing nothing about the deal.

From there it's pretty straightforward - build it out in the same way you built out your first mortgage amort schedule. You only need five columns for your amort schedule, and from left to right they should be:

1. Payment date (the calc at the top of the amort schedule is just =firstpaydatecell, and after that its just =edate("dateincellaboveyou",1) this will actually generally cause slight imperfections in your calcs, because it doesn't take weekends and holidays into account. Payment Date is generally defined as like X day of the month unless that day is a weekend or holiday in which case its the business day before or whatever. But the "market convention" for cash flow modeling is to pretend weekends and holidays don't exist. It's technically slightly incorrect, but it's just what everybody does.

2. This is your days/accrual column. This is actually kind of embarassing - its been a few years now since ive written a vanilla commercial mortgage, so im sort of hazy on the accrual. a little voice inside me is saying commercial mortgages accrue actual/360, so that's my guess. my current market and most structured finance accrues 30/360, but i feel like commercial mortgages are different. Plus the nasty little Jew Rats that populate that specific little hotbed of white collar criminal activity are the only people i can think of who would be be so self-absorbed as to believe that the rest of us didn't notice they were snaking five days of accrued interest from us and that we just give it to them because nobody cares.

Anyways, it's kind of irrelevant - it's just a slight change in the calc. If your bonds accrue actual/360, then this calc should be:

=days360(paydate1,paydate2)/30

If actual/360, it should be:

=(paydate2-paydate1)/30

And then you use this number for your accrual. Lots of people leave this column out, because realistically other than the first payment day, which often accrues actual/360 even in a 30/360 deal so you have to calc manually anyways, doesn't really change anything. I insist on including one of these columns to accrue my bonds with absolute, to the 1/100th of a penny precision, and just personally I'm pretty judgemental of people who don't accrue correctly. Like, if I open someone's model and they don't bother to accrue the bonds correctly, I assume they're a lazy fucking asshole that sucks at modeling.

But realistically, it doesn't fucking matter. My obsession with to the penny precision is probably a holdover from my banking days when I used to have to tie out OM numbers with accountants and those nerds would have a heart attack if I were a nickel off.

I don't know, I guess I take a certain amount of pride in constructing my models absolutely correctly even if it doesn't change anything as far as the relevant analysis is concerned. It's a personal choice, but... Come on. Build your models right.

3. Third column is PMT, assuming its mortgage style amort. Pretty straightforward - calc is:

=pmt(principal,coupon/12,maturity,amort,0)*accrual (or whatever order they're supposed to be in)

In the case of this mezz loan, I can't tell you exactly the term/am/coupon, but I would just assume theyre the same length as the first mortgage, no amort, 10-11% coupon.

4. Fourth column is iPMT. It's just annual coupon/12*BeginPrin.

5. Fifth column is pPMT, just PMT column net of iPMT

Next column is BeginPrinc for the next PMT date, calc is just BeginPrinc from the earlier period net of pPMT.

Yeah, so, apologies if I'm telling you shit you already know, but the mezz loan shouldn't require substantially different modeling from the first mortgage (assuming its like, a normal mezz loan, obviously every loan is bespoke and if you give me more info I can tailor my advice better.) at least on the front end. Feel free to reach out if you've got any other questions about this shot. Wasted several years of my life on it.