Ep. 41 | Syndication Spreadsheets – How Does The Math Work?

On this episode of The Mobile Home Park Lawyer, Ferd talks us through a pro forma and discount cash flow analysis. He goes through one of his properties as an example, explaining everything we need to know.  

HIGHLIGHTS:

0:00 – Intro 1:55 – The first tab is the budget, sources, and uses 2:18 – Next tab is general info, which includes the loan and other fees and expenses 4:16 – Next there is the P&L, which he has covered on another podcast already 5:08 – Ferd goes through some other costs 5:50 – Ferd continues on to the cash flow analysis 6:33 – Ferd notices he only has a 7.3% ROI which isn’t enough to cover the 8% pref, but he explains how he makes it work 10:04 – All told, what are the fees? 11:04 – Ferd explains the business plan for the deal 13:40 – Ferd goes through what the deal looks like without fees  

FULL TRANSCRIPTION:

Welcome back mobile home park nation. Ferd Niemann here again, mobile home park lawyer, continuing our discussion on syndications. I’m going to show you this project I just closed on, a small project in the Des Moines market. I’ve got to set up a syndication to show you the ins and outs, but don’t call me about this deal, trying to put money in it. Already closed it. This was not a syndication. Our team, we sometimes syndicate some of these smaller deals like this. This was a $535,000, 20 pads site deal. It’s got some expansion potential and some, but under 10 acres of ancillary ground. But this one, we’re not syndicating. And I’m not advertising first indication. Cause that would be a potentially a problem. So that’ll come up in future episodes on the do’s and don’ts as far as syndication and capital raise. But today I am just going to show you what you need to know and do from a pro forma or a discounted cash flow analysis perspective. And one of my most recent podcasts, I went over the metrics, the formulas, the kind of the meat and potatoes that you need to understand. Well here, we’re going to look at it today here on the spreadsheet. And you know, just like some of my documents, I sell templates. Some stuff I give away for free. You know, this is my actual spreadsheet. You know, I’ll sell you guys just for like an hour of time if you want it. I’ve got more sophisticated forms. But I think this is, this is pretty good for most deals. First tab, here we got our budget or sources and uses. This is our day one budget. These are not part of my operating expenses. Like I’m bringing in a roll-off dumpster, initial marketing flags, new billboard signage, white picket fence as the core. We’ve got title work and paint the existing park-owned homes. There’s five of them, about $400 a pop. And trees, you know, this is our financing costs our bank gave us. A quarter-point. The next tab, general info, this is the loan. So, this is our $535,000 purchase price at 80% LTV, 428K loan, 20-year ammo, 3.75% interest rate. This is a local lender, just a recourse loan. That’s what you see the lower ammo, but I don’t mind it. It is one of the small deals, it’s hard to get an agency loan or you can’t really get an agency at all, but it’s even hard to get a conduit CMBS loan at this price point. So, from a fee perspective, it’s a quarter-point. That’s not bad. I got 3000 for the appraisal, I got $1,900 for my phase one. I paid about $2,000 for a septic sewer transfer. And then I got an Alta survey. And then I got the additional kind of, I got the regular strut boundary survey plus the Alta. So, all in I’m at about $13,900 in inspections and fees and soft costs, a little painful for a small deal, but you know, it is what it is. I got sub-metering of water. I got the number of lots, 20 lots, times 450, a pop. That’s a metro on meters and installation. And heat tape and kind of foam and stuff to do that. I don’t have any evictions or property manager plugins. It is a small deal and good tenant base, minimum office repairs and a printer. I got $20,000 a models for park-owned homes. I’m going to eat about 5,000 in lost water as part of the sub-metering. And before I’d get them implemented on my new leases, I’ve got a new playground coming in. I got a 10,000 in miscellaneous. I got a sponsor fee of 75K, commission, acquisition fee, whatever you want to call it. And that varies on a deal. This is pretty high, frankly, for a deal this size. But ultimately, it’s what the deal can warrant based on how good a deal you found and what the investors can absorb. So, all in, I got about $681,000 total uses. I got five park-owned homes. I’m going to sell for an average of 20 grand for an average of 100. You’ll see that in my pro forma, it’s on a four-year cycle, about 25,000 a pop per year. So, all told I need about $250,000 of cash. Look at my P and L. I’m not going to go through this. I’ve covered this on another podcast. But this is my P and L, here’s the spreadsheet piece. Look, I’ve got 12 months, zero empty rental homes, zero rental homes, 20 lots, three empty lots. Lot rent is going to be $445. We’re at $430 to $420 and $430 today depending on the tenants. Market runs over $450. So, we’re really over 500, some are over $550, some are for $600. So, we’re leaning to them pretty, relatively gradual, cause we also submeter the water. So, we get people that $20 rent increase plus pushed the water sewer down. So, it’s not a huge raise, but not a small raise either. NOI, there’s our gross. This is all formulaic, blah, blah, blah, potential gross rent, less vacancies. These are all formulas that are drawing off of these other cells here. I think most of you guys don’t to do this. I’m not going to belabor it. Got our other costs in here. Advertising, insurance, legal, accounting, licenses, permits, park greeter, lot rent, motor repair, trash, maintenance non removable, gas, for site visits, property taxes, electric, zero in this case, mowing, office supplies, don’t need internet, phone, rent manager, extra log-ins for the small park. I’ve got streetlights. I’ve got someone I reimburse water for commentary leaks and or heat. I’ve got management fees. I got miscellaneous 1,500. So NOI 48,000. So here we go. Amortization tab really simple. That’s just from the general info tab, pulling the bank info. This will be important for my payoff. Cashflow analysis, NOI, I’m not charging an asset management fee to collection fee for this. There’s my debt service based on the amorization tab. My debt service coverage ratio of 1.58. That’s pretty good. First year banks must be at least 1.2, 1.25. It gets up to 1.76 in year two and 1.93 in year three, as we push the rents a little bit. Cash flow from operations, 17400, 8% preferred return. That’s pulling it off of the $250,000 equity mounts. So that’s $20,000 prefs. So, from cash flow from operations, we have a 7.1% ROI, which yikes is not good enough to cover the pref. Oh no, we’re crazy. Well, here’s how we make that work. We have those park-owned homes, which we can sell. You can sell them a number of ways. You can rent them. You can sell them on cash and some on terms, through a contract-for-deed or lease-to-own. I’ve got other podcast on the pros and cons of that. You can sell them through a 21st mortgage or a pep or a local credit union. In this pro forma, I’m averaging selling those over a four-year time period, which I think is pretty conservative. So as the manager, I’m taking half of that. So, you see down here in my fees in yellow, I’m taking 50% in this hypothetical syndication of the park-owned home in operational fee. So, as you see, appear in operational fees and it was non, we couldn’t even cover the pref. At least from just regular operations. So, we’re going down to the next echelon and that is the sale of mobile homes. Well, if you do that, look at this number, our cash-on-cash return on investment from operations and park owned home sales is 12.1%. That’s not bad for an investor. 8% pref. The pref means that the investors get eight before I get anything. And I do have a commission in this, that’s because I got an assignment and I got to give some commission guys who have found it. I’m not, money in this instance would not be for me. So, I’ve got an 80% waterfall. That’s 80 for the general partner and me, 20 for the investor. Again, that’s probably higher than normal. I think this deal can stomach it. As you see, as you go through the pro forma, we’ve got an eight pref to investors, then 80 to me, 20 to them, they still get a 12.1 in your percent cash on cash the first year, 14% year two, and then year four, they get a big liquidity event. We refinance the property. Then I got a refund ammo tab here. And what I’m, what I’m assuming here is a 70% loan to value at 20-year amortization at 5.5% interest. Those are both pretty conservative. The 70% reasonable, 20 year is pretty conservative. 5.5 I think is very conservative with today’s interest rates. But this is four years in. So, who knows what it’s going to be in four years. For the exit cap, I’ve got it pulling off of a 6.7 cap in this example, excuse me, a seven cap on this deal, which I think is pretty reasonable also. This little bucket here tells you what the equity balance was. Here’s the refi proceeds, less fees. So, on the refi, I’m taking an 80% of the fees in this example, and then the investors get to Delta. I’m not paying the preferred equity down at the refi, just choosing not to in this example. So, the investors can keep their money going. We do it again till the end of the movie. At the end of the movie, which is a hypothetical sale called a reversion value in year 10. We sell the property for 1.45 million. We pay off the bank loan, which is your refi amortization aged 86. So, we just go look at the spreadsheet. Here is a little tab. H86, that’s payment 86, 86 should be payment 74. So, tenants setting four payments on the new loan, it’s a 502 pay off. I’m off a cell. 506, 697 payoffs, which is what we’ve got. And I’ve gotten this, I’ve got the net proceeds of there would be about 950. But the investors get their money back. This is what calculation does. So, here’s the net proceeds. You see at the bottom right here in Excel, $950,000, but I got to give the investors their money back. Then the investors get their 250 after the banks paid off. Then we split those proceeds 80/20. So, all told, over here on the left. What are the fees? Investors, they put in 250,000 in 10 years, they make $650,000 of profit. It’s after getting their money back. So, it’s an equity multiple 3.6. If you go down to my definitions here, I’ve got with these equity multiples, the ratio of equity to total net profit plus the total equity invested, divided by the total equity invested. So basically, it’s cash in, plus profit out, divided by a cash in. 3.6 equity multiple. Internal rate of return, right there. I went over that in the last podcast. It’s 23.68. So, if you’re an investor, I say it’s a pretty good deal. And you get an 8 pref and you got your promoter, me, signing a recourse notes. You know, I’m pregnant on the deal and I’m serious, but you get your one cash-on-cash and double digits, 12%, and you get a 23% IRR and you get tax benefits from being an owner of the real estate. So, I would argue considerably better than a mutual fund. But that’s not good enough for me. I mean, it is, and it isn’t. But here’s the business plan on this deal. This is a four-year hold. So, I got the same sort of metrics. I just jumped to the cash flow analysis tab on the four year. If I sell this deal in year four, because I really, it’s going to be full tilt in year four. It’s fully stabilized. I can only milk the value so much. So, what I’ve done here is in the sale that the total reversion price is less because haven’t had many years to grow the NOI, but I sell 897 instead of like 1.4. But it didn’t take me nearly as long. So, for the investors, holy cow, their rate return is 49.07. Well, the last couple of deals I’ve done had been like 80 to the investors, 80. So just to be honest, it kind of frustrated me that I’m paying the investors that much. I like the investors. I appreciate them. And I need them. And this deal is smaller. I don’t as much, but in some deals, I need them. I got another deal coming down the pike. I need them, right. It’s a big number. So, I want to take care of them, but it was a little hard for me to leave a lot of money on the table. And I tried to be conservative. So, I don’t underperform, and I never have, but you know, knock on wood. 20 plus projects never lost money. But you don’t know what the next project, in my opinion, if it is a syndicator, it is a one-strike policy. You don’t get three strikes like in baseball. You strike out once, you miss a pref payment, you’re toast. You miss, you know, you miss a mortgage payment, you’re totally toast. But anyway, I’m over-delivering at 49.97. Honestly, I don’t want to give the investors that much. So, what I’ve done here is I’ve contributed, or I’ve created a 30% cap and here’s, you can see the management split. Where’s that formula in here. This formula, you can screenshot this and look at it, equals min, future value, blah, blah, blah. Basically what that does is it says if the return to the investors, net of my fees is going to be more than 30% IRR, which it is up to 49 in this example, it’s going to cap it at this at 30. So, the investors make, in this case, they put in 250, they get the 250 back plus 227 in profit in four years, which equals a multiple of 1.91. So not as high equity multiples of a ten-year hold, but obviously it took them four years instead of 10 years to get there. So, one would argue it’s a better deal. It’s a better IRR, 30% versus 23. The downside is they got to go place the money somewhere else at the end of four years, and then potentially pay taxes on it, pay capital gain unless we do a 10 31 exchange. So, pros and cons, that’s what it looks like in a syndication. These are all the metrics you need to know. What a pref return is, what the hurdles are from operations, from a refire sale, is there a cap, who gets paid and when, you know, you got your P and L assumptions, you’ve got your budget assumptions. All this goes into the deal. I’ll just show you real quick what this looks like if I don’t do fees. So, if it is just me, I’m not going to charge the sponsor fee. It’s minus 75. I can raise as much capital 175. I don’t have to do the pref. This is my money. And I got to change this to 175 investment. Look at that IRR 52.69. That’s a pretty damn good deal. I had a chart. I got to change the fees here too. So, it’s way too high. It’s way off. It seems too low. It’s because I had a, I was charging fees mythically to myself. So, if I get rid of the management fees, IRR 100.38. So, this is kind of an extreme example. Generally, is not that much of a spread, depends on the deal. That much was spread between the investor cut. Typically, I don’t say typically, but typically it’s 10% to 15% extra is the fees on a smaller deal, on a shorter hold like this. I mean, yeah, it’s a 100.38. Yeah, I’m doubling my money at quite the clip doing it like that. So anyway, that’s pretty much a typical syndication spreadsheet. Back to the tabs real quick, we got the sources and uses tab. It’s kind of like day one budget. You got the banking tab; we’ve got your profit and loss. This is, you know, over several years, some of these numbers in subsequent years are just increased by inflation. Some of them are not, like the gas goes down considerably. I’m going to visit it less frequently. Once it’s full, that kind of thing. Oh, by the way, here’s one more thing. The city is fighting me right now. So, I got three big vacant lots. Cities fighting me on zoning. So, I am going to have to do a variance or sue them and I’m going to win because there’s supreme court law and statute law in Iowa that protects grandfathering. But I can’t really get that done pre-closing, kind of a pain. But so, if I change this to zero vacant lots, holy cow! What’s my IRR going to do? It goes to 136%. Well, you can bet that’s what I’m going to do. This is what I am going to try to do. The other thing is I got 10 acres of excess ground I think I can sell for 150 K. Which is another reason why I put in this 30% investor cap, because I don’t want to tell my investors, I am going to sell the ancillary ground for 200K, which is basically the brokers don’t know, 200 to 250. This land was, by the way this market is redevelopment. I don’t want to put that in my pro forum, my PPM, my private placement memorandum and then not deliver. So, I’m just going to put it in there as A, I’m giving you a zero credit for it, but it might happen. But if I just do that, that is 200,000 extra for the investors, that seems like unjust enrichment to them. And I did that on another property and it kind of grinds my gears that I left about a million dollars on the table and be honest, it hurt. I still did okay right. But a million freaking dollars left on the table. So, I’m not doing that twice. So, Logan, my business partner in CFA, who is a number cruncher we sat here together and we said, how are we going to make this quote fair to us if we over-delivered? And it was by putting in this cap. And at 30% IRR, I don’t know anybody bitching about a 30% IRR. Let’s edit it up, cause my mom might listen to this. Anyway, that’s how I do syndication. There’s more copies of spreadsheets out there. I’ve got access to some of them I’ve done before. But really this is pretty good. This kind of cash flow analysis, a lot of moving parts still, formulas in here, all that stuff’s in here. So anyway, reach out if you have any questions. Until next time, God bless.