This is Part-2 of a 2-Part series going over models that you can use as a real estate developer to plan out projects.
In Part 2, we are going over a more in depth Ground Up Residential Development model - including a presentation-ready section to show investors your project as need be.
Marina Gita - Okay, so here we have a typical Ground Up Residential Development Model. It has all the features that I would have put in a package that I would have worked on for a REITs project. It also has a presentation ready that an investor may want to use, you may want to use it for banks and we'll go through all that.
So first, we're going to go through these blue tabs, which is all the input tabs. Then you're going to have your tabs that you're going to show to investors or even internally for presentations. And then we're going to go into more detail, cost spreads and things like that show the backup of profit and loss by year and by month.
So let's get started with the input tabs.
So I have these three tabs (Property Data Inputs – Development Cost – Cost Spend and IRR Calculations). You're going to want similar information to what we went through, but more detail, and again, you can start off with less detail and then build up to it, even in this model.
So [in the Property Data Inputs tab] I have information about my building, then I have my timing, you also have the planning period in here as well, so you can see from day one, when you start putting money down all the way through opening, what it looks like. That's important for calculating your IRRs and you have your cap rates.
[The Property Unix Mix tab] [shows] a small building. So I built it to where you can see what the average score footages are and also what the opening rents are going to be, so you'll need that information. That populates [on the left-hand side]. And then these move in dates are going to be used for my lease up and occupancy information. If this building was much bigger, I would use this format [on the right of the Property Unix Mix] and I would build a different type of occupancy so that it still works without having specific move-in dates.
A lot of times you'll have concession allowance [listed under Other Income]. What I've seen in the market in the past, -- oh, many years actually – they’ll give three months’ rent to everyone coming in in the first year, so that's what this is calculating.
Other Income would be things like if someone loses their keys, applications pep fees, things like that. I just have a ballpark [in Expenses], this could also be made more detailed. You have your Operating Expenses, this again, you can get from a property manager locally. You don't actually need all this detail to build this model, but in this case, I've just broken out a project that I had seen how much things cost... this has 5-100 per unit per year.
A management fee can vary from 2% to 4% -I just plugged in 3% here, that's a typical for what I see in the DC area as well as... even up in Jersey. You have Financing Assumptions that you can get from the loan officer, I'm going to be covering both the construction dev as well as refinancing.
The next step is the Development Costs [tab], as Greg mentioned earlier, you could actually hire someone for pre-construction services that would give you some idea about what your construction and cost will be.
What REITs do is they'll bid out the project and they'll have different estimates from different construction firms like you're supposed to do, and then look at the bids and put the input that they find in these models so we can see which one is realistic and what makes sense for us before going forward with any general contractor.
So what you would do [in the Project Development Cost section] is just input your information. I detailed out most of what you would see that's typical for a real estate project in residential, so that you have an idea of what costs you're going to have to get from external sources.
[On the right-hand side] it is showing also your dollars per unit and per square foot, you have your land value here as well. Always remember that this land value is always credited towards your equity if you've already purchased the land.
Down below, this is feeding from another tab, but you have your interest during construction and your mortgage being calculated somewhere else and feeding here.
In this case, the equity requirement is 2.4. We've already put in a million 'cause we already have the land, so there's another 1.4 that we have to find before the lender will give us 68% of the balance on this project.
The next tab shows my Cost Spend and IRR Calculations. This is important for lenders as well as for yourself to know what your equity spend and then your mortgage draws are going to look like over time.
An easy way to calculate this is just what I've done is I've built this formula [under #mos to spread cost], where it takes your construction costs down below and spreads them by months.
And it spreads a straight line spread, but in my experience, even if you do a straight line spread you get pretty close to what your construction interest and your equity requirements will be.
And down below, I'm calculating my Construction Interest based on the draw. In this model, I'm assuming that all your equity will go in first and then the mortgage will kick in or your construction loan kicks in. Sometimes there are cases where the leader may say, “Give me this much equity let’s say your land, “And then we'll go 50-50 on the equity going forward.” Now if that's the case, I would just come in here and just switch it up a little bit so that could work for you. I've just modeled here, what's typical in the marketplace, which is all equity in first, then your construction loan kicks in.
And down below [under IRR Calculations], I have scenarios on your IRRS. There's different whole periods. Most models, even if you think you're gonna hold it for the longest time, a lot of times you want to know what happens if I wanted to sell it in five years or seven years or 10 years? What does that look like?
And these are in blue because you can toggle these and change them so I can change it to what if I want to change it to the eighth year...or the seventh year, what does that look like?
And also your CAP rate, this is controlled by an input that we put on the other page, you’ll look at what the cap will be on the exit as well. If you keep scrolling it’s just followed with calculations, this is all automated. It also shows the levered IRR, the one above was an unlevered IRR.
So those are all the inputs. Now we get into all the information.
So my Backup Automated Sheets, I have a Profit Loss [tab] by a year, this is typically what you would show an investor or you would show a bank on how you're doing on a yearly basis. Typically I've built it up to 12 years.
You want to at least show a loan officer at least 10 years of cash flow. They're gonna look at your stabilized year. Stabilized year means that I’ve leased up my project, and now I'm at a stabilized position where I can say this is what I'm gonna be making at a minimum without escalations.
And you're gonna take that stabilized year divided by your cost is what your cash on cost is, and that's a metric that we all look at to decide if we're gonna go full with a deal or not.
IRRS are mostly focused on by investors and folks that want to sell projects. When you're holding on to projects that's more cash on cost and cash on cash. You wanna know how much you're gonna generate, how quickly you're gonna get your cost back is really the focus of if you're holding on to a project.
So this is my stabilized years [on the left-hand side], you can see I pulled all this information from the data input tab. There's my rent concession, my occupancy, I have all of my operating expenses by unit, my debt service at the bottom [if you keep scrolling].
Another thing to look at is your Operating Margin. [79% operating margin is] about where it should be. You want to make sure the [Net Operating Income] cell is doing is looking at your net income over your gross income, so you can see how much of it is actually how you're operating basically.
[If you keep scrolling down what you'll find] is your Debt Amortization.
And I'm also calculating my Operating Deficit, which is something that shows up on my Development Cost.
[The Profit and Loss tab] is also shown on the monthly basis - this is going out for 12 years.
You'll also find operating expenses, and you could really see what the Operating Deficit is by month.
All I'm doing is putting the credit [under each month of Cash Flow Available] that you see here, where we're not covering our expenses 'cause our lease up hasn't gotten to where we want it to be.
This number, the 61,000, is thrown into your Development Costs, and that is something that loan officers do allow... they don't know that that's what that's for.
The [Lease Up Occupancy Calc-Yrs-1-2 tab] shows you how I calculate the Occupancy. So I'm showing based on the move in dates what occupancy is to the point where I reach... in this case, I've said that 87% is going to be my stabilized month
So you can see ... the cell information is stabilizing [after the year 2022]. I'm also calculating my free rent calculation [under Free Rent Calculation on the bottom left].
So over the first nine months, I'm giving away basically for the occupancy at 23 units, I'm giving them a month of free rent.
The next tab is Financial Loan Sizing.
So our finances, when it comes to construction loans, we have anywhere from 65% to 80% I've seen where you'll have loan-to-cost. So [on the right by Construction Loan Sizing] where my Construction Sizing is happening.
I'm saying that my net project costs are 7.5, I'm going to finance 5.1 million of it.
Down below is the Re-Financing assumptions [section]. I'm assuming that I'm going to refinance in year four, and the way that we look at this typically is you look at different things. The bank is gonna look at your NOI multiple.
This is a typical calculation in the market, you'll say that you can afford to pay at least 10 times your stabilized net operating income. So in this case, it's 6.6 million.
You could also look at your loan-to-value. This varies anywhere from the 60% to 75%. 70% is a good rule of thumb to keep when you're modeling.
[The Loan to Value section] is saying that based on loan-to-value, which means I'm looking at my stabilized NOI using a CAP rate coming a book of value in taking 70% of that, and I'm saying that my possible mortgage could be 7.8 million.
Then I'm comparing it to the 6.6 million, and I'm also looking at that service coverage which you can get from a loan officer - with that service coverage they'll allow.
And the [NOI Coverage section] is computing 8.6 million. So this is the range that you're gonna be able to refinance at.
In my assumptions, I usually say, we look at the minimum of the three is will populate in the in [Mortgage Cell under Refinancing (Permanent) Terms section], and this is what I'm assuming the model, we're at a minimum can be able to refinance for in year four.
So that's all the details.
Then we go and we can look at what the Presentation Tabs look like.
[The Property Performance Dashboard] is a dashboard that shows your property performance overall. It has the timing, it has these metrics that I've talked about - Stabilized Cash on cost, it has your NOI Stabilized Over Cost, you have your Development Costs.
[The light grey middle section] shows you this little toggle where you can see how much I send you over the next couple of months.
And down below is pretty much a quick summary of everything that's in the model.
You have your Net Operating Income, the Mortgage, Cash Flow available, your cash on cash.
Your cash on cash will typically be - should be higher than the cash from cost.
You have your equity going in, your loan balance is your cumulative return on investment, which is pretty much your investment minus what you've been able to get from your cash flow over time.
As you notice [on the navy blue Cash on Cash ROI line], by year 2026, we regained what we've invested in the project, and now we're making this much in cash every year going forward. Your equity multiple, look at it more as a, "what am I getting for the value".
So it's looking at the property value minus the load, and then it's saying, how much am I getting back for my investment?
In this case, the 1.84 means I've gotten back my .24 million and then I make 0.8 over that in my investment so far, and that's why you see over time that this number [in the green cell] is going up.
Below we have just the summary of IRR calculations that I showed you earlier, and that I think gives a good picture of what the project looks like without looking at all the details.
The other worksheet that I have is a Project Schedule.
This is something that we put in packages, loan officers like to look at it, we also have investors that want to look at that just to show what your cost spread is over time.
And that's all the tabs - Greg...do you have some questions?
Greg - So go back to the beginning here. And yeah, I think that was awesome. Great high level overview of the model.
So one thing to keep in mind is that you put together for this presentation based on a project, so this is totally customizable for anybody when they engage. You can build this thing out however, they wanna look at it.
So if I wanted that schedule that you just had placed on the dashboard, you could just move that over and put it in the dashboard, so I have a one-page presentation that shows everything, right?
Marina - Absolutely, yeah, yeah totally customizable.
Greg - Yeah, so we're back to the beginning. So some of the things that I really like about this is the way you've got your unit mix broken out. So if somebody wanted to... and this is a 250-unit development, and they wanted to literally list every unit, lease up date projections and all of that, they could literally do that or they could just do... like you have it summarized on the five where you have four 51 bedrooms, 62 bedrooms, 53 bedrooms, and just kinda... however they wanna do it.
Marina - Right. Exactly, yeah.
And if someone had a 200 or something or 400 unit building, what I would do is not do it this way, but there's another way to do occupancy where you don't need all these move-in dates at this level, at this time. You could always leave it in here for later, when you get closer to actually doing the lease up and keep track of how your occupancy is actually doing in real time.
But during construction where you're not gonna have all this information, there's another way to build something in this model that's really quick to change how the occupancy is calculated.
Greg - We're phasing out the building, so let's say you have a multi-building site, maybe even got 50 buildings with 20 units in each building or whatever, you can phase out if you're gonna build them out at least them up as they finish along.
Marina - Absolutely, yeah.
Greg - Awesome.
Marina - Do I have anything on the Development Costs?
Greg - So development cost. So this is high level, again, this is, as a developer, generally, you're gonna be hiring a general contractor or a CM firm - construction management firm -that's then going to engage the general contact on your behalf. So this model, we're not drilling down into the line item cost for the hard cost of construction.
This is all just high level hard cost, soft cost, things like that. But if somebody were a vertically integrated development firm where they are self-performing then you could insert or have another tab that breaks down the construction cost into the line item details correct?
Marina - Yes, that's a good point actually. Typically, what we would do is this line item here is what would be the top level information of a backup worksheet that we've gotten from a general contractor that has all the details of the contract itself.
Greg - Yeah, and like we said before, if you noticed this model, we do have a construction contingency, which is again, unforeseen things. Whereas in the line item general contractor your escalation of the construction costs are gonna be built in the GC model on that, so you would not see... like in the one-pager escalation and continue to be broken out here because the escalations packed with GCs
Marina - Exactly, exactly. We always leave it up to the GCs for when it comes to escalations.
Greg - Right.
Marina - Here [in the Development Cost tab], I could also say the A&E - sometimes you don't know right away what the architect is gonna charge you. It's typically a percentage of hard cost, you could almost guestimate what that number's gonna be.
And from 4-7%, you could plug that in and then talk to an architect about getting that more firmed up.
Greg - Yeah, okay, great. I like this 'cause you've got a broken down where you've got some FF&E in there and all that kind of stuff for your common areas and things like that.
Marina - Yeah, yeah, and this again, could be changed customized, you don't have to use all these line items as well.
Greg - Yeah, and for people that are watching FF&E furniture, fixtures and equipment in your common areas are gonna have furnishing, you gonna have gym equipment, you're gonna have miscellaneous items in your leasing office depending on the size of property, things like that.
So you'll have some FF&E that your GC is not going to include. So there's gonna be a number of things that you're gonna have to separate out from your general contractor.
Marina - Yeah and that's typically called soft cost in the market.
Greg - Right
Marina - Then you'll always have some legal fees.
Greg - Yeah, and then ramp-up. You're always gonna have some ramp-up fees and things and... stuff like that to get the project up and running.
Marina - Another thing that's important is real estate taxes. You always make sure to look at what's going on in the market. 'Cause especially if it's a piece of land that you're looking at - look at what's going on in the market and make sure that you get this number right you can really throw off things by - you will get an assessment once you're built and you're gonna be paying a lot more once that construction is done, so be careful about this number as well.
Greg - Yeah, that needs to be vetted with the municipality or district that you're operating in, because they could be assessing you at full stabilized retail value or they could be assessing you at a percentage of that cost or full cost, so there's any number ways that they do that all over the country.
So you definitely wanna drill down into that and make sure that you know.. And they're gonna base that on largely on the praise value that they do on their own.
They could base it on the construction permit, the value that you're allocating to the construction permit. So you just wanna make sure that you know what that is. 'Cause that's huge.
Insurance is another one. Obviously, you've got builders risk and things like that, but once we're stabilized, you might have to pay a whole year's worth of insurance premiums up front for the stabilized insurance.
Marina - That's true, that's a good point.
And on the real estate as well, some jurisdictions, depending on how your land was zoned, you have to be careful that sometimes they won't change. Let's say you have something that's office/residential, and you decide to build a residential building, but it was zoned and taxed as an office parcel.
You wanna be careful that you may be hit with a bigger bill until they switch it over to residential. I've seen that happen as well.
Marina - Yeah, and the development fee as well. When it comes to reads, this covers all of our overhead. So this number could be 6 or 7%.
Typically for smaller firms, I've seen where you can... they'll lower this number so that they will make their numbers work because it's actually just a fee, so just keep in mind this could be just the fee or it could actually cover a lot of overhead.
Marina - Yeah, and here's an operating deficit that I talked about before, this is the cash flow - or negative cash flow - that we have at the beginning before lease up, so that's where that shows up.
Greg - Yeah, that's great to factor that in, 'cause that is a cost of the project to get developed that a lot of people forget about. They say interest costs, but they don't think negative cash flow while you're stabilizing and ramping up.
Marina - Yeah, and... and this is just a 27-unit building. When this one goes up to 250-300 units, that number is usually a really big number, and we ended up end up really vetting that out.
Greg - Yeah, just 10X on this is 270 units, that's $600,000 so yeah that's the big number.
Marina - Yeah. So the cost also...
Greg - Yeah, I like this. So this, for a lot of people, this is draws, so everybody wants to know how is the money going to go out including your developer fee. 'Cause you're not gonna get that upfront, that's gonna bleed out. Generally, as the project progresses. So I love this cash flow and draw schedule, this is critical for lenders investors, everybody wants to know how the cash is used and how it is and when it is going out.
Marina - That's right. And then as you go forward, you could actually hard code over all of this with your actuals if you really wanted to see how your cash flow is going. And we typically would do it month by month, and reset it until you get to the end of your project.
Greg - Well, some of its lenders require that. They require you to update your draw requests so they can see the project cycle as you go.
Marina - Exactly, yeah.
Greg - So they're not gonna just give you X amount of dollars each month, they might say, send me your updated cost, and then you're gonna allocate as you go along. So a lot of people think they're gonna get a lot of money front loaded here. And especially right now, they're drilling down into the details on a lot of these, so that's a great tool right there.
Marina - Yeah, yeah, even in big corporations, doesn't matter if you're small or even REITs, they would have to do specific draws and show exactly what you've spent and exactly what you want your money for...
Yeah, they're very tough out there. And this is just a typical profit and loss statement that you can use not only -
Greg - So... Yeah PNL pretty standard there, and then you've got the PNL broken down by month... pretty standard stuff there. Again, customizable, however, I love the lease-up, you've got that broken out so the people can really drill down in some details and lease up, that's really... that's a valuable tool there.
Especially looking at it down to which unit puts us at stabilization, I love that you can pinpoint the unit put you over the hurdle.
Marina - Exactly, yeah.
Greg - Yeah that's awesome... Now on returns.
So on the return metrics, does this have a tool in there for Refi and hold? So you pull your equity out, pay them off and then adjust the returns based on a Refi return of capital and whole moving forward?
Marina - What I have right here is I put the years that Refi... assumption in year five, and it shows where your equity multiple is at that point.
Marina - Now, this could be built all sorts of ways, depending what you're trying to do, if you're trying to refinance it and then you have partners or waterfalls or anything like this. This could all be customized, all I'm showing here is the refinancing assumption and the cash coming in and what it does to your return on investment.
Greg - Right.
Which at that point can be turned into a whole different project. So right now we're developing as a merchant to build itself into a Refi so if we have investors that are on board for Refi then we can return equity. Now it's a new project moving forward, and your model could contemplate that for me, as the owner developer continuing forward once I paid everybody else.
Marina - Absolutely, absolutely.
Yeah, as you can see how your return on investment is ramping up after that Refi. And even this project schedule here, what you can do is not only show the project schedule, but I could also build... let's say I wanted to give this to a loan officer, I would pull my construction draw from the other tab and just plug it in right here and just send that to them.
Greg - Yes, yeah, that'd be a great idea, especially on that dashboard where you have the schedule and the draws allocated to it on a graph, so they can kinda look at it and... that would be pretty neat.
Okay, well, cool. Well, I think we have covered it all, and if you have anything else, Marina, that you wanna add or you feel pretty good?
Marina - I feel pretty good. I think we covered quite a bit of bases considering the two models.
Greg - Alright, cool, I appreciate you doing this today and Mason will wrap it up for us and tell everybody where they can find this and download that model.
Mason Fiascone - Great, absolutely, thank you Greg for providing the questions here in the format, and Marina, thank you so much for sharing your models and your expertise.
So many details in here, as you can see there's thousands and thousands of inputs on any project here. And it's critical that you get each one of those right along the way, we're talking 27 units or 270 units.
Every detail here has an impact on all the other players down the road. And so having expertise on your project is really necessary, you shouldn't be trying to do it alone, as Greg mentioned early on, you wanna build a team, build people who are experts at what they do like Marina experts at underwriting these ground-up models and having all the assumptions and all the updates in there.
And so the best place to go and hire a team essentially, flexible for your projects and your deal flow is BullPen, so BullPenRe.com is the best place to go.
We will... Greg will provide a link out to the post that'll have the one-pager model available for download, you can also on that page, get on and hire Marina through our process, and so you can bring her on to your projects and have her expertise working on your team not just in the walk through here, but right on your team.
And so if you're looking to scale up with expertise or you're overwhelmed with deal flow, a lot of projects out there, BullPenRe.com is the best place to go and find a top to your talent across commercial real estate. So Marina thanks so much for sharing your expertise here and really looking forward to having people engage with you and engage with us at BullPen.
So thank you Greg again for walking through this as well.
Marina - Yeah, absolutely
Greg - Yeah, thank you for doing this today and having us here. And you guys have a great week and we'll talk to you soon.
Marina - Thank you.
Mason - Absolutely, thanks.
Enter your email address to get updates and new blog posts sent right to your inbox.