Pro Forma For Multifamily Renovation (Updated 11.17.2021)
I’m excited to share the Pro Forma for Multifamily Renovation model with all of you. This multifamily renovation model is made to analyze value add apartment acquisition opportunities and has a lot of functionality that is meant to provide you an ability to get into the details and go unit by unit for up to 100 units.
In the video and corresponding transcript below, I’ll walk you though how it all works. Enjoy!
Important Note: The Pro Forma for Multifamily Renovation is in beta, meaning it certainly contains errors. I also intend to add features to the model based on your feedback, and what I see as opportunities to enhance this model. If you find a bug, or have a feature you’d like included in a future version, please let me know.
Updates: version 1.1 update video below – posted September 7, 2021.
Pro Forma for Multifamily Renovation Video Walk Through
Pro Forma for Multifamily Renovation Video Transcript
Intro/Overview
Hey everyone, in this video, I’m going to walk you through the Pro Forma for Multifamily Renovation. As with all the other videos that I do usually with model walkthroughs, I’ll do a high-level overview of all the tabs, and then we’ll go into each tab and we’ll talk a little bit about the functionality. So yeah, without further ado, let’s just dive right in. So this model comes with seven tabs.
You have your Summary tab and you’ll notice as usual with all models, the cells with the blue text are where you put in your inputs. So there are some inputs on the Summary tab.
We have our Global Assumptions tab here. And within that, we have some income assumptions. We have some inflation assumptions and OpEx inputs.
We have our Unit Details tab and this tab I’m really excited about, and we’ll go through this in quite a bit of detail, there’s a lot of functionality here.
Michael Belasco (01:09):
And really what I think is the power behind this model is that it gives you the ability to phase in unit by unit. So you’re able to go through if you don’t want to renovate all the units at one time and you want to do it in phases, this gives you that ability. If you have custom plans for each unit, you’re able to model that out here. So it’s in my opinion, a great tab. So, excited about it.
Next one is Global, Non-Unit related capital improvements, which is like roof landscaping. Anything else you might have. And this gives you the ability to do six capital improvement projects.
We have our Annual Cash Flow tab, no inputs here. It’s just a roll up of the Monthly tab. It again also has no inputs.
Michael Belasco (02:00):
And then the last page is the Waterfall tab. I’m not going to go through it on this video because this is the same waterfall model that we use in the condo development model. And so if you want to learn about it, you can just click the link that’s up here in cell E3. I added a couple of new things here. I added the GP fee section. Actually, I think that might be it. And that flows in right here. So again, a couple of inputs here to dial in all your partnership splits and how you want to structure that.
Summary Tab
Michael Belasco (02:39):
All right, so let’s head back to the Summary tab and we’ll go through it in a bit more detail.
So starting at the top left, you have your general info. This is where you put in, if your property has a name, you can put it in the project name, address, city, state, and then the version. If you’re sharing this multiple times and updating you’ll want to keep track of the version that you’re sharing.
We have our timing section in which you have your acquisition start date, which you can put in your month and your year. You have your hold period, and you can hold up to 10 years in this model. We then have our exit date and then below that we have our renovation. It pulls out the minimum date between the unit detail section for renovation begin date, and the renovation begin date for the non unit CapEx and it basically just grabs the minimum of all that. And then with completions, that takes the max of date completion for all of these. And so that’s what rolls up here into the summary tab and then you get your total time.
Michael Belasco (03:58):
Below that we have our purchase price. So you’re going to manually dial that in and your immediate CapEx. So what this does is this formula basically matches anything in the Unit Details tab that matches a start date with the acquisition date. So you’ll notice here we have, right here in column L, you’ll see we have four units that are going to go under construction at the start. Each one of which is $15,000 and then the rest are $10,000, the next three as such equal $45,000, which you’ll see here. And then immediate CapEx for building non-units, there’s $40,000 for the projects that start at that time. So you’ll see here, we have two that start August 1st, both at $20,000. Now that’s how that works. If you have a bit of a nuance where maybe you don’t want to fund all of that at the time of acquisition, because what happens here really is that the loan amount includes these immediate CapEx. So if you don’t want to include that, what I would recommend in the model is just changing the start date just one month later.
Michael Belasco (05:16):
So rather than starting in this example in August, you start in September and that’ll remove that amount from that. So that $15,000 should be gone. And you’ll see there that it is. So that would be my recommendation if you don’t want to include it in the debt. And then below that we have our closing costs, which then brings us to our total.
So down to debt again, we have our loan to cost. And then this is a cool little feature for this model. So you have the ability to utilize three different loan types. So this one, which is the most typical is interest only until it’s fully funded. So you’re just paying interest only. And once all the loan amounts disperse, the loan will start amortizing.
Michael Belasco (06:09):
We have stage funding which basically the loan amortizes from the start, the second you buy the building. Let’s say, for example, it’s a 360-month amortizing loan. Well, if you’re taking out money from in the future, like let’s say the second month you take out more money. Rather than that money starting amortizing over 360 periods, it’ll amortize over 359 periods so that the max amount of time, the money’s out is 360 months, regardless of how much is being funded after the initial funding amounts. So that staged funding, and then the last thing you have the ability to just disperse it all at once. So maybe you have some type of structure where you just want to get it all out at once, and you’re able to do that and want to do that if you have that ability.
Michael Belasco (07:03):
Right below that you have the loan amount, you have your interest, your amortization period, and then you have your loan costs. Here you can just dial in whatever you want. I just did 2%, but you might want to dial in whatever you feel is appropriate for your project. Loan proceeds and then loan repayment at exit. Below that you have your minimum debt yield and your minimum debt service coverage ratio. And this is calculated down at the bottom of your Annual Cash Flow tab. So you’ll see the debt yield and the debt service coverage ratio. So that’s pulling from there. Then we have our exit cap sale price and then sell costs. And you can just dial in whatever your sale costs are.
Michael Belasco (07:47):
All right, so to the right starting column E we have our returns and we have our typical project level returns and then our partnership level returns.
Below that is our annual cashflow summary. So this takes really just the major line items from the detailed annual cashflow tab and sums it up right here for an easy view.
And then to the right here, we have our unit mixed summary here. All these will pull in from the Unit Details tab, based on what you select here in Column E. Now you have the ability to update here, studio, one bed, two bed, three bed. If you want to change penthouse to four bed, for example, we’re actually, before I do that, let’s keep it at penthouse and you can check the drop down here. You’ll see studio, one bed, two bed, three bed, penthouse. And then by changing this to four bed, you’ll see the dropdown.
Michael Belasco (08:51):
So I think that’s everything on the summary tab.
Global Inputs Tab
All right, so let’s move on. So next we have global inputs. At the top we have our global input assumptions. So here you have your average lease term. You could put whatever you want and I have 12 months here and this flows through the monthly cash flow tab. So if you have an average 12 months, it’ll assume that there’s a 12-month lease in place and then you have a renewal probability and then after that 12 months, it’ll factor in this percentage, it’ll calculate basically the probability of renewal and that gets calculated into the formula as well.
Michael Belasco (09:34):
So what I love about the Pro Forma for Multifamily Renovation is that you have the ability globally to say for everything in this box here from renewal probability, all the way down to other income, you have the ability to customize the scenarios for pre-renovation and post innovation, that’ll flow through to each model, regardless of when that unit is being updated. So if you have a 60% probability of renewal for a unit and you remodel it this year, and then your assumption is that after the renewal probability will be a lot higher, at 75%, that unit will change at that time once that unit comes back into circulation, and then if you have another unit that goes out the next year, that same assumption will apply and you just dial it in right here globally. So your renewal probability, pre-renovation, post renovation, your rent concessions pre and post, and then other income, you can change these to whatever you want. I have five options here.
Michael Belasco (10:39):
I just have RUBS, laundry, and parking. You can add whatever else you need or you can just leave it blank. And then a general inflation that you can add to the other income as well.
So below we have detailed inflation, you have rent growth, you can plug that in for each year. You have general vacancy and you have credit loss. Now with rent growth… …so what you can do in Unit Details is you can add a premium to the rent after renovation, and you can’t really do that in this model after you do a major capital improvement at a global level, but the goal obviously is to maintain the building…
Michael Belasco (11:25):
…and with some of these capital improvement projects may be with the goal of increasing rent. And so what you can do here is if you have a project that’s done in a certain year, like an example here is 2023, what you could do then is come in, in 2023, maybe what I’ll do is actually add the years here below. So 2023 is year three, but what you can do here for rent growth is maybe you go to year four and rather than it being whatever the general market assumption is, you might increase here, because what you did should increase the rent across the board and so therefore you might want to do a global rent increase. It’s a bit higher in the year after. That’s how you might want to consider that when you’re doing these global CapEx projects or consider working through that scenario for the goal of increasing rent, due to a capital improvement project, that’s global for your property.
And below that we have our OpEx inputs. Here you’ll put in your year one amount and you have the ability to grow each year thereafter, and it’s all the same all the way through.
Michael Belasco (12:38):
And so that is the global inputs.
Unit Details Tab
Next, we have Unit Details. This tab is really cool. You have the ability to add up to a hundred units and depending on what you put in here, this will open up accordingly. Let’s stick with 10. And then we’ll walk through each of this. So in this column D, I have unit number, you could have, if you have a couple of buildings, maybe you have A1, A2, A3, B1, B2, B3, or however you want to name them, if you want to name them, you can add that here for the unit number. And then beds, we went through. This is the dropdown. Bath, there is a dropdown if you want to get in more details, you can put whatever, how many baths are each of these units, square footage. And then we have a model unit.
Michael Belasco (13:37):
We have this both for before and after. So I’ll show you real quickly if we go to the monthly cashflow tab and let’s actually go to unit 2, because you’ll see we have before renovation, the unit comes out, we have after renovation, it comes back in now. Let me show you what happens if we say this is a model unit. If this is a model unit, that means there’s no rent coming in. It’s just there for show and display. And so you’re not getting any of that revenue. So it all turns off. Now the rent is still coming in after innovation, because this becomes no longer a model unit but if we did want to make this a model unit after, you’ll notice it all turns off.
Michael Belasco (14:21):
So there’s no revenue coming in from that model. So maybe the likely scenario is you do a renovation and then you want that one to be a model unit. Then you can say yes that this is a model unit and that’ll work that way for the after. Plus we’ll leave that as is. Next is in place rent, and this is a global thing. Are you remodeling this? Yes or no? So if you click no and maybe what I’ll do later is do a conditional formatting, that just blanks this all out or just puts a black bar over everything. So if you say no, what will happen is the formula will ignore everything here. And it’ll just focus on what’s here and what’s in your global inputs and it will grow continuously. So you’ll see through, it’s a six-year hold, so it shouldn’t be that far. There’s no spike. It’s just a regular rent bump with all the assumptions in there. This will put that back to yes.
Michael Belasco (15:30):
Then we have our renovation begin date. How long the renovation takes, costs. Again, this is a model unit which we talked about? So you’ll see $15,000 here. You’ll see it’s over two months. So you’ll see that broken out into two and the monthly cashflow. So let’s go, we should go down to CapEx units, and you’ll see here, it’s broken that unit down to two months of paying out for the unit renovation and then a lease up after completion. So you’ll see there, it’s been a total of four months that this unit is out of circulation. Let’s put this no. And the reason is I want to show you that this indeed matches four months.
Michael Belasco (16:18):
So you’ll see, it starts in May 1, ends in August 1, and then it comes back on in September and so you will see this comes out May 1 and then the lease starts in September.
Michael Belasco (16:37):
Then we have our premium added due to the renovation, and that should be $250 and it also captures inflation. So it’s basically in today’s dollars. So it will still be $250 solid, the $250 doesn’t grow with inflation, but the rent may grow. So you might take it out at $785 and then there’s an inflation period, the rent will grow a little more than $250. And the last column is rent per square foot post renovation. So you have pre and then post renovation, okay.
Non Unit Cap Ex Tab
Non-unit CapEx, again, you have up to six projects you can work on and it’s fairly straightforward. Your project name, begin date, end date, and then cost. And here’s where I talked about premium added to your renovation, use the rent growth section in the summary tab which we talked about previously.
Annual Cash Flow Tab
And then the next section here is the annual cash flow tab. And you can look through this. You’ll notice gross rent, we have concessions and here’s our other income with the five line items.
Michael Belasco (18:03):
We have our effective gross revenue, general vacancy, and credit loss. And then we have our expenses control on fixed, NOI, and here’s our major CapEx projects. There are units and non-units, cashflow from operations, and debt service, cashflow after financing, we have the loan proceeds and loan repayment, and then disposition details. And then below that we have our unlevered and levered cash flows, and then some metrics: free and clear, cash on cash return, debt yield, debt service coverage ratio. And then below that we have our LP and GP cashflow. Alright, monthly, this gets into a lot of details here. So I’ll just open everything up and you can see it’s been a lot of work to get this going, just to be able to get the nuance of taking units in and out of circulation.
Michael Belasco (18:57):
So I hope this really adds value to some of your guys’ projects. You know, for me, I find this incredibly helpful. So again, I won’t go through all the details here, but you know, you can pile through. Some of these formulas are rather large. So if there’s anybody out there that wants to get creative and think of ways to shrink these formulas down as well, happy to entertain those and update the multifamily renovation model with those ideas.
Waterfall Module
And the last piece you have is the waterfall.
Wrap Up
So that’s really everything. That was hopefully about 20 minutes. So hopefully it wasn’t too painful and yeah, I hope you enjoy the Pro Forma for Multifamily Renovation. That’s really everything. And please feel free to leave comments or better yet try to get in touch through the website adventuresincre.com. And I hope this was helpful. I will see everyone next time. Thanks a lot.
Update v1.1
Download the A.CRE Pro Forma for Multifamily Renovation
To make the Pro Forma for Multifamily Renovation accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – similar real estate development models sell for $100 – $300+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update this multifamily renovation model (see version notes). Paid contributors to the multifamily renovation model receive a new download link via email each time the model is updated.
Version Notes
Version 1.0
- Initial launch
Version 1.1
- Added the ability for interest only debt
- Added draw schedule for loan disbursement
- Added interest only period section in Summary tab
- Corrected Error in Unlevered Cash Flow Formula
- Made backend debt calcs viewable for users in Monthly CF tab
Version 1.2
- Cleaned up Summary tab and removed scrap work on the bottom
- Fixed error of missing 2 months in Year 11 on Monthly CF tab
