Create Your Excel Pro Forma Template for NPOs Effortlessly

Excel pro forma template for NPOs

hi there this is AI Coffman with tactical real estate solutions and today I'm gonna show you how to use our multifamily development pro forma you know I really built this tool for real estate developers that maybe don't have the most extensive knowledge in Microsoft Excel my hope is that you're able to model a development opportunity in about 15 to 30 minutes now I've worked with some prominent developers here in the Twin Cities I've gotten their feedback on the tool and I really like where it's at I think it's intuitive I think it's easy to use and most importantly can be recycled so you're not just going to use it on one project but you're gonna be able to continually use it and get better get more comfortable with it and really have something that you can trust and something that looks good and then you can present to investors or or your partners and feel good about it so with that being said let's hop right and do it here the first tab that requires any assumptions is called the project tab now I just want to digress for a quick second there are circular references in this workbook so the first time you open it up you might get a bunch of errors saying that they're circular references and you'll see a bunch of crazy or arrows pointing everywhere if that's the case let's go to file options formulas and check this enable iterative calculation box here and then click OK and everything should be good I don't want to get into the weeds of you know what circular references are and why iterative calculation solves a problem I do have a blog post linked on this page that explains it much more in-depth and and why it's relevant and especially in these in these real estate new development deals what some of the sources for these circular references and how we get around it like so gets confused but I really just want to focus on the functionality of the model and what you need to do to put an analysis together quickly so with that being said let's get started you can see that top we have the general property information here so this is where you put in the project name the acreage the project square footage just a reminder any cell that has black text is either a formula or label you don't need to do anything with that the brown text is what you need to alter so you can see like rentable square footage here in units that's actually populating that unit mixed AB we'll get there shortly but you you would leave that alone for now you know you plug in the address the city the state how many parking spaces there are and then the analysis start date that's typically going to be when you're closing on a loan or or closing on you know the equity proceeds you're raising from investors and hopefully starting the construction soon thereafter and then finally you're going to put in a construction period in terms of months so I have 14 months in there typically I'll do anywhere from 12 to 16 it's really the developer I'm working with their feedback but we'll stick with 14 for now and it will hop over to construction financing I already have this model fully populated so when you're getting started a lot of these cells would likely be firing errors at this point but in the construction financing part you just need to put what your your loan amount would be so in terms of LTV 80 percents pretty common for these types of new development deals that I've been working on and then the interest rate of that construction loan and then the big piece here that's getting solved is the cash required how much equity will you need to make this development happen so it's about 6.7 million again the total cost that's gonna be that's gonna be populating from the budget tab and then the loan amount is obviously just just backing in with with the LTV here if we hop over to the permanent financing the first assumption here that you need to make is is the refinance month it is a brown cell but notice it is linked to the stabilization date you're probably not going to need to alter it but I just wanted to give you flexibility in case for whatever reason maybe you wanted to roll with the construction mode a little longer most people you know as soon as the property stabilizes they're gonna instantly blow up that construction debt and and get the permanent financing in place but just in case that you didn't want to do that I left it flexible in case you wanted to do it a few months later or whatever the case might be you need to come up with a cap rate for your refinance and obviously when you get your permanent loan the lender is likely gonna have the property appraised and they're gonna take that stabilized Noi and cap it and that's really gonna come out with what your your match loan proceeds are you're gonna choose a loan to value I'm using seventy five percent here you're going to come up with your permanent financed interest rate amortization I just remembered II mindful that this permanent financing is two years plus out so be conservative before the interest rate you're using some of this stabilized Noi stabilized that service coverage ratio will populate automatically and then you and need to enter in the cost of refinance I'm just doing 1 percent times the loan amount and then the monthly payment and the annual payment for the printer financing will populate as well so then if we slide over to the least summary we need to make some assumptions here about how the lease house is gonna go so I'm assuming that at co the property would be 25% pre-leased and then from there I'm assuming that 15 units will be leased every month I know this is maybe kind of hard to visualize at this point because we haven't gotten to the you know mix but it's a lot easier to fill out once once you're kind of plugging in the information of different tabs and then stabilized occupancy is the next assumption so when do you think this property what what occupancy would you say that this project is stabilized usually ninety five percent is common maybe though you wanted to put in ninety two you can see that if you do plug in a smaller occupancy a stabilization date will move up in the stabilization month will move up just go back put a 95 to show you that again and that all again like I said before ties into the refinance if you try to refinance this thing when it's 92 percent you're gonna be able to blow out that construction loan faster so just be reasonable with this stabilized occupancy you know 95 is probably about right but I won't go any less than 90 and finally the last assumption you can make in the lease of summaries is regarding concessions so we're targeting a seven eighteen hundred and eighty one dollar rent depending on the sub market though there may be concessions you may need to give away a half month a month free a month and a half whatever the case might be so if that was the case with where you're building you could enter that key in here it's in terms of months so you can see then that the net rent would be adjusted and this will then flow into your pro forma these these concessions would be a negative line-item as you're leasing up it is important to note though once you do get stabilized these concessions will fall off this is strictly talking about Lisa getting people to move into their their first-generation lease what would it take to get them into a unit there's a separate assumption you can make if you did want to have concessions in the pro forma when it was stabilized and then the last assumptions on these tabs have to do with the residual sales value actually so the first thing that the model asks is for a state last capra today I've been there five percent and then an increment per year and I have 10 basis points in there so what this saying is you know this model is going to show you how your returns would look if you build this deal you stabilize it you operate it for somewhere between call it three to ten years and the cap rate we're entering here and they prevent per year this is the residual cap rate that will be used in the discounted cash flow to come up with your residual sale proceeds so the higher stabilized cap rate you're using today on the higher increment the more conservative you're being so just be realistic you know if the property you're thinking about doing and you know whatever part of town you're planning on building it if it was 95 percent occupied no concessions and hit the market today what cap rate do you think that would be if it's a foreign 3/4 cap there's a cop that sold that a foreign 3/4 cup you know I may be entered at it but just make sure that you're being reasonable and always erring on the side of conservative and then the final thing here is called reboot hopefully you never have to do anything with this but because of the circular references I alluded you alluded to earlier sometimes things go haywire in this model like for example if if you're gonna change the LTV and you actually type in a letter here Oh typed in yes instead okay there's a bunch of errors right so now we go I'm just gonna go to another tab here oh no there's errors everywhere that's alright let's go back to project unless just hit back so 80% comes back it doesn't say yes anymore but notice they're still errors everywhere unfortunately I have to learn this the hard way and you know you have to close the workbooks start over and you like for me I mean I've never I never tend to city of anything so I have to start from scratch so what this reboot does when it turns pink you just need to go like this you need to hit yes it will stay pink notice the data comes in again and then just go back to no and then once it's no longer pink you're good to go so now that's six point seven million cash required numbers back everything's good so anytime you're getting a bunch of errors and you can't seem to get the model back on track you just need to use this reboot reboot flip it from no to yes and then back to no and things should get back in order I don't want to get in the weeds again about explaining why it works this way I do in my blog post that details this financial model but hopefully this is a solution and you never have to lose work like I did so many times so now let's get to the budget and draw tab so this is where you're going to type in all of your different budgeted costs so you can see here now a different land soft costs hard costs FF&E impact fee finance and title you can call them whatever you want you know this is like the specific cost and in what the budget amount would be it's part of no capitalized interest will always be calculated automatically on a model so that that's actually one of the causes of the circular references right because because you're gonna need to try to because you need to figure out how much money you need to borrow to pay your interest expense off each month but how much you borrow is also dependent on what your interest expense is each month so you can see it's it's circular in nature and that's what my mama in this model particularly that's that's what's causing the circular reference another common thing that can can cause the circular reference error and Excel is is the developer fee right because most developers will charge a developer fee as a percentage of total project costs but the developer PS developer fee is a part of that total project so Excel into a loop so I mean me personally I don't actually use a formula that for the developer for you can see here most developers I talk to our just solving where is it that let me click on the arm on it most developers are solving for you know a million bucks or a hundred grand and so usually around number of there really using like a strict percentage of the total development cost so that's what I try to do but if you did put a formula in here like like three percent of the times the total project cost you know that would be fine you can do that in the model to be able to handle it you can you can see that the cell I grabbed down there I hit back put it back in a million this is just pulling all the other land south hard costs and all the different categorized expenses from the list here I have it in a nice layout I think for copying and pasting usually someone will send me an excel and I just dump it right in here I don't need to do much else then I'm always just kind of looking at the per square foot and then the per unit cost and just making sure it's in line with other stuff I've worked on in the past there is a drop-down menu for these items and how you want to classify them if for whatever reason you didn't want to add a few or change them or delete some you can do that on this lists an amortization tab this is where the drop-down is right here and if you wanted to add them you can just keep writing new ones down below or you can just overwrite them here let's go back to this tab and then we need to come up with what our draws actually would be so again there's another drop-down list so these are the same light items that we dropped over to the left and then there's a method a method sell in front of to the left of each of the items that you'll need to choose you can either these expenses will either be drawn upfront evenly as a bell curve or custom so up front is you probably guessing you know with your live acquisition typically you're gonna pay that ultra it's not going to be something that gets paid slowly over the course of construction evenly it's just straight line you're gonna pay the same amount every month through a certain expense but bell curve you know typically these are your hard costs they kind of ramp up is you get more to the middle of the construction period and then they'll line down towards the tail end of the construction period and then custom custom if you call it if you select custom it's going to zero out everything and you need to manually enter in when you want to draw on that budgeted expense so for example I'm using custom for some marketing stuff for signage I think this is signage right here I'm just gonna draw that in the very last month of the construction period in month 14 I'm gonna do the same thing with the hundred grand which is which is operating capital and then the final expect and then the final custom thing the soft called the marketing costs now I'm going to assume I'm not going to draw that at all during the construction period so you can see then there's a balance a $300,000 balance because I never entered in during months 1 through 14 where I wanted that money to be spent if the total in column aw doesn't match the budget amount in column L which you stipulated earlier the seller turned pink sometimes that's normal that's what you're gonna want but it's just so you're cognizant and enable to double check ok am i entering everything I should be doing so in this case yeah I wanna I want to kind of I'm wanting to carry that $300,000 over that's kind of like my operating it's kind of my operating reserves and marketing budget when Lisa is just starting and perhaps the revenue that the property isn't yet covering the expenses you know if you do have more months if your construction pure ADA's is 16 months or 18 months grab the cases I'm just hiding I'm just hiding these cells so it's just more for presentation whatever your period is you can just hide everything to the right of your specified construction duration so if we Center this thing back up again the capitalized interest that calculates on its own it's always the model is always gonna assume you draw on your equity first right because that's the most acosta cost-effective way to do things so you can see here there's a nice bell curve you know you always gonna have your upfront cost which is kind of a big outflow right away and then with the hard cost it really builds up months six seven and eight of the construction period and wines back down you can see that same graph but just if whether it's equity or debt and then just the final graph is just kind of depicting what your total debt cost is and what the interest expense is every month throughout the construction period one other small thing I want to note here you do have some control over your bell curve I have it set at two and a half if you want to flatten it out you increase the number if you want to increase it you go last so if we put it that to that to make it steeper yep so there's some flexibility there I always I've never adjusted it from two and a half but I just know some developers are so dialed in they know exactly how it's gonna look they might want to tweak that a little bit and make it and make it as accurate as possible so if we slide back over there's just a couple two other things I want to talk about here operating shortfall when at zero like this that that's a good sign um it means that there's never a point when you have negative cash flow after debt service that wouldn't be covered by the construction loan so sometimes you'll get a negative number here if if the construction loans all drawn maybe you have a slower lease up and you'll have four or five months where the revenue is not covering the expenses and then you'll need to decide well maybe we're not maybe we need to bring some more operating capital into the model you know just to cover us if the lease of course lower than normal you know if this was a smaller number like negative five thousand or negative ten thousand I probably won't worry about it too much but it's just something to keep an eye on make sure that you're thinking about how the lease ups gonna progress you're looking at your expense load and understanding that if there's a shortfall in cash flow you need to come up with a way to cover that this current model it's good and then just the final construction loan balance it's telling you with all your drawers how much of the total loan balance you'll be using so in this case it's it's twenty six million nine hundred sixty five thousand which is pretty much almost all of the loan proceeds that the bank gave us in the first place next tab is unit mix so pretty self-explanatory this is just where you're going to put in your different floor plans I'm going to units are the square footage and then the rent that's being targeted to the right here a lot of municipalities have a mandatory exclusionary zoning requirement so if in order to get approval to build a deal sometimes you need them to keep a certain amount of units affordable if that's the case you can do that here so in this particular development I am taking eighteen one-bedroom units I'm keeping them I'm holding them at the 60% a my threshold I got this from the internet below I can see that read limit 4 4 1 bedrooms is 1124 so that's what I'm holding it to and then you can see it calculates automatically what that loss would be and that's spread over the entire right or also this do is targeting um you know 1843 dollar market rent but due to the exclusionary zoning it's going to get taken down a bit to 1781 and this is the number here that would flow into the pro forma if you didn't have any inclusionary zoning requirements you could just your these out you won't have to you won't have to do anything else some graphs down here that populate automatically as well just with unit mix and the average square footage for the floor plans all right so now we get to the stabilized operation stamp so this is where you know we already have the rents you can you can see that the average monthly rent that's a black text cell because it's flowing in from the unit mix tab but now you're gonna have to come up with assumptions for gain or loss the leaves concessions vacancy last bad debt and you employee discounts or model unit loss all these assumptions are made as a percentage of GPR once you get into some of the ancillary Aiko items that are brown text you'd enter and how many diff what the quantity is of you know storage units or how many units you think will have pets or parking spaces you put in a vacancy percentage and then you put in what the average monthly cost would be so for example this property there's 50 storage spaces we're assuming that will at least 95 percent of them at a 75 dollar charge per month and then the monthly stabilized will populate the annual stabilized will populate and then the annual per unit stabilized will populate to the far right um sometimes it's hard to quantify like for example on like application fees and damages and move out break lease fees things like that so what I typically do is I'll just put the quantity at the number of units there are and I'll just kind of solve for a plug in an average monthly cost and I'm really just looking at the annual I stabilize I know that 32,000 a month in these various fees here in Minneapolis is reasonable so you know if I thought it should be a little higher I'll plug in 20 or 25 and see how that affected it and just kind of bad - what I want that annual a stable has never to be by using an average monthly costs allocated over all 180 units there's some other line items here to you that you can use if you need them anything you don't need you can hide or delete you can even delete the rows that have black text it's not gonna affect the model adversely I would suggest is hiding them but it's totally up to you beside that there and then expenses so we get into the expenses you make those assumptions on a per unit basis I don't know if I've met a developer that has done it differently the only thing that is not gonna bring in a basis is the management fee this would be as a percentage of effective gross income and then the per unit would be solved right next to it it's black text and then the net operating income will populate automatically can make a reserves assumption if you want to do that I'm doing two hundred per unit and then the nmi after reserves will populate one thing to note with real estate taxes is it has an asterisk next to it you need to enter in when what year you think it will be stabilized remember these these developments take some time and just depending on what state you're and it's gonna take a long time for the valve to fully assess and for the full tax liability to finally hit the pro forma so here in Minneapolis I have it being fully stabilized in year three and then if I don't some shiny to make is just what percentage of that fully stabilized amount will be during Lisa and I have 20% plugged in there remember you're going to know you're gonna have a good idea of what the real estate taxes payable will be while you're constructing the loan and you'd essentially borrow that money from the bank and you'd be paying it with withdraws but you need to make an assumption for when you find you're slowly building up the resident occupancy at the property but will the Assessor peg you for when you're not yet stabilized another thing to point out there's just some key dates up here Mac dates key numbers just telling you just from the assumptions we've entered in so far when the co month is when the stabilized month is when the refi month is usually stabilized month and refi month will be the same just depends on your preference and then the monthly cash flows will start phasing in to the right side here so you can see here we have a 14 month construction period and then of 15 you know remember we put in the 25% pre-leased assumption and then we release in 15 a month so you can see the occupancy goes from 45 to 60 to 60 to 75 and every the occupancy numbers are calculating automatically the revenues are building up the expenses are getting phased in as well and then once the property is stabilized this will cut off and then it's just going to start factoring in to stabilize monthly assumptions so if we scroll even even further to the right this will all be summarized annually and notice up here you can hide these monthly cash flows I usually do hide them but just for reference it's nice to be able to look at them if you want to see kind of the nitty-gritty of all the properties leasing up but you can you can see here in year one you know everything zero right because this this took 14 months to build so there's not gonna be any cashflow year one and then the revenue gets up to 1.9 million in year two we start loosing up in the second month of that year and then year three is like your first fully stabilized year and that's the same for real estate taxes to remember how we put that real estate tax would be 20% of the full stabilized amount while I was well the property was leasing up at seven hundred twenty nine thousand then it jumps up to the fully assess amount of seven seven hundred and seventy four thousand that we stipulated over here to the left so once the property stabilized you can control the line of line items with by making your assumptions up here in this gray box so I have rank growth of 3% there's no concessions in the model most you know most people that I work with that they might have a half a month or a month of lease up concessions but they're not usually modeling concessions once the property is full here's your vacancy loss assumption and 5% of GPR your bad dad assumption of your other revenues and then an expense escalator as well so everything is pretty steady at everything steady at 3% now one thing I did want to point out if we go down here you'll notice there's a row called construction dried Co so what this means is that there's a couple months where the revenue from the property wasn't covering the expenses so the loan is being drawn on to cover that so if we go back let's open up our monthly cash flows again you scroll back over I track where a track the construction loan is well so you can see the construction loan balance is building off as we're making draws and then once we get in a monthly fifteen here the Noa after reserves is negative there's a construction loan payment so the operating cash flow is negative 100 negative hundred eighty thousand so one hundred and eighty thousand is being drawn on the loan to cover that operating deficiency so there's still two hundred and eighteen thousand dollars remaining on the loan and then the total loan balance has stated below that so those initial months when we release enough we're trying to get people in there to cover the expense load there's gonna be some some months of negative operations and the loan drawers are covering those and then finally once the operating cash flow goes positive there's no longer any need to drown the loan anymore and the property would be in the clear again that's where this is being calculated when when we asked if there's any operating shortfall in this instance there's not well there is but it's being covered by the construction loan sometimes of modeled deals where the construction loan runs out and the cash flow is still negative and that instance might need to bring an extra 20 30 grand into the deal so just wrap this tab up all of your operating assumptions will be what annualized here three years one through eleven and then we get into the return summary and this is really what it's been building up to you you want to see what the IRR is our what the project multiples are so you know the initial equity investment was that negative six point seven million number and year one everything is zeroed out this was during the construction year the end of year two you'd be refinancing this deal so you can see your blown out the construction loan you're putting on this permanent financing you have a nice little sum of proceeds here from the refi and then the total project distributions will be summarized below so what's cool about this model is you can really look at yours two through ten and figure out what your IRR while your IR would be and what your equity multiple would be really any year that you decided to sell so these are your residual cap rates you remember on the project tab we specified that we wanted we thought this deal is a 5% cap today we're doing one and we're doing 10 basis point increments every year and a 2% cost of sale so that's all being factored in below here so in year 1 its 5% plus 10 basis points your 2 5 plus 20 basis points and then that's it's taking the NOI from above here dividing it by that residual cap rate factoring in the sales cost loan pay down and giving the net sales proceeds so typically with these deals is it's common that you know if you're cooking and L are in IRR there's gonna be a big benefit of building it stabilizing it and selling it as fast as possible right because the IRS time sensitive so the faster you can get in and get out you're gonna see a bigger boost ir but it's just really a question of preference is it more beneficial for your investors or you to to get out quick and hit a higher R or hold it longer term but have a more moderate IRR and but a higher equity multiple so that's just cool at this time you can really see the investment outlook for multiple years this listen amortization tab we already around here briefly I've just shown you that you can adjust the drop down menus in the budget and draw tab and then this is just a summary of financing so the construction loan and the permanent financing showing you what the payments are the interest remaining balances what not there's nothing you need to do here this is just something you could maybe print out and have for your reference or if you wanted to show a partner investor it's just a nice summary that that shows you kind of logistically when the construction mountains getting blown out what the permanent financing comes in and what that what the monthly payment is here in the far right column real estate sensitivity I wrote a really detailed blog post about this that's also linked on this page and in my experience a lot of developers forget have to develop a deal and they sell it and they make a lot of money and they sell it because they sell for a lot more they developed it for well the next buyer is going to have a significant amount of property tax liability so this amount is just really factors in looking at what that liability might be so you're taking the current tax assessment you're looking at the reassessment post-sale and you're comparing what the tax adjusted cap rate would be so these are the IRS that we solved for on the previous page these are just on the return summary 7156 47 7156 47 well if an investor comes in and says yeah we're gonna pay a 5 to cap but a tax adjusted 5 to cap because they know there's gonna be a significant amount of real estate exposure they're gonna pay you significantly less there I'm gonna pay you 46 million versus the 47 7 that they had that we had in this model so it's just important to state to be cognizant of the fact that the next buyer is gonna have real estate tax liability and I always figured I always thought this has been kind of a blind spot developers eyes not really taking that into account for their own pro forma they're just really thinking about their hold they're not thinking about the next group but the next group that buys that we're not gonna be able to pay as much if there's significant amount of tax liability so really what happens then this is what would happen to the returns and the equity multiples if the buyer was looking at the real estate tax liability and adjusting that cap rate to factor that in again you can see how that cap rate would be affected over the hold period typically the longer you hold it the less real estate tax law I believe there be and you can see how that translates then to net proceeds and then there's stress tests I have them off right now because these can slow down the model briefly turn them on here always got to stress the model see how the numbers look in different scenarios so the first stretch stress test it looks at what the DSC RA should be with permanent debt coverage so the the baseline assumptions are what's in the model currently resolving for a stabilized occupancy of 95% so 5% vacancy and we're targeting that 17:81 around so me stabilize the deal get permanent data it's covering at about 1.2 which is is where you'd want to be on the theory you know I might feel a little better if this was closer to like 1 to 5 so maybe it's something you test you you adjust the leverage we're at 75 percent on the permanent debt maybe you go to 17 see how that affects DSCR I was like to look at the refinance proceeds so in this instance when you look at the eg is compared to the cap rate use that refinance you can see what percentage of the initial equity is refunded 100% you get literally every dime back and a little more of that initial initial 6.7 million in equity just wanna see how that looks on the model here yeah so you can see that the construction line is getting blown off Department of financing I was 34 million after you pay the financing cost you get six point seven nine eight million Fester's already put six point seven four million into the deal so it's important to stress that you know refinances are such a big part of these new development deals and making sure your investors hit certain return thresholds so always just make sure that your egi is vetted and you're looking in this instance you know I'm growing it at 2% on the right of our baseline assumption and decreasing at 2% and just seeing how that affects refinance proceeds at different cap rate levels now the cap rate we don't have any control over right that the market dictates that so in two years from now you know maybe maybe a mark a deal which if you think it's a five cap today maybe it's a five five cap you know in two years so it's just important to see kind of in the worst-case scenarios what would happen in all these assumptions obviously pre wheezing is gonna have a big effect on your your overall returns the faster you can we setup the faster you can start growing rents so in this instance we're looking at the horizontal assets what the pre leasing percentage at cos we had 25 percent in the model and how many leases at least every month we have 15 so you can just see how the IRR is affected at different thresholds now for only 10% pre-leased and you get nine leases a mom the IRS twenty nine point one three percent over seven year old versus if you're forty percent pre-leased you get twenty one leases among your ir would be thirty seven percent on a seven-year hold you can just the hold period up here to see how it look gonna kind of get slow when all these data tables are on so it's just something that's why i always turn them off and i'm not using them you can see the IRS jumped considerably there and then if you're wanting to adjust the increments in both the horizontal and vertical axes you do that with with these right here so the leases are jumping by 1.5 every month and the previous percentage changes 5% if you wanted to do 2.5% we'd do that there and then the final one is the projected return on cost return on cost is such a crucial metric for most developers this is where you can look at your total project costs and the horizontal access your projected stabilized Noi and see how that would change the return on cost if you go a little over budget a little under budget miss your Noi by 2.5% over achieved by 2.5% it's important to understand construction past and Etowah projections will affect the return on cost and the final pieces of the model are just a few partnership distribution models so we've talked about the project cash flows up to this point there's some different options here for if you have equity partners so the first one the classic pari passu if you're just throwing money in with a couple different people or one other person and and you're taking an equal portion of the distributions of what your contribution was you just run the model like this so in this example if you put in a 50% contribution with your partner your partner we need to get 50% profit interest so you can see that the project metrics here to the left in the beige 42 percent IRR three point six nine equity multiple it's gonna be the same for each of you right because you're distributing cash flows evenly if you did like a profit interest where maybe you put in zero percent of the equity and you found investors and raised a hundred percent of the equity but you still maintained a 20 percent profit interest could type that in here you can see how those cash flows would look for you Porter note you know if you're not putting money into the deal technically you can't have an IRR and equity multiple right because you even have an initial investment so when that's the instance I are just say a hundred percent and equity multiple will say a hundred if you do a preferred return arrangement this isn't common for a new development but I have it included just because it's a very popular distribution arrangement for the value-add deals I'm working on you put in the preferred return right here typically groups will raise I got a hundred percent of the equity they won't put any money in and then they'll get a split once that preferred return is achieved it's really weird here and I do development because you're literally only hitting that promote when there's the refinance and when the property sold but that is an option if you wanted to run with that you can always adjust the hold period up here too to see how it affects the cash flows if you're if you're holding longer-term and so you start to get and do that beyond that 8% starting in year five and then there's more detailed summary of these cash flows breaking it down for both the GP and the LP what's actually happening what the cop balances every year what the required return is so if you're like me and you like to see really what's going on and understand it this is where you go you just gotta collapse and uncollapse this little check mark here the IRR waterfall is the next one easily the most popular model for for these new development deals so you know you put in your initial contribution you'd have your IRR ranges and then whatever the profit interest is for those ranges so zero to fifteen percent would be pari passu you know you give back what you contributed from fifteen to twenty percent IRR the 28 you split and the twenty percent plus ir would be a 50/50 split so again the cash flows are summarized here you can see what the IRR and the equity multiples are for both the cheapy or you know I use GP and sponsor interchangeably and the LP if you want more detail about the East cash flows again you can open up that check mark to the left I research a few equity waterfall models online and never really worked with my mind I wanted to do it my way that that was easy to follow and I hope that this is easier and hopefully more intuitive to think through but basically when you're visualizing this my Proform a year ago years ago from top to bottom so you can see your zero is your one your two year three the hurdles move from left to right so I first I first summarized the project hurdles in the bays here and then I get more into the you PLP specific hurdles which are color-coded of course in green and purple even further to the right and then the dark Rose these are the actual distributions being made I've just you know I've gotten so caught up in some of these other models just confused kind of forgetting even what I'm looking at so you can see in this in this example you know there's no cashflow year one so all the cash flows are zero there's a big refinancing you're too but it's still a hurdle one you're still in hurdle one in year three and year four and then during the say on your five that's where you you get out of her to want into her to hurdle to you can see the 50-50 split here in herd of three if it was a longer term hold to a 10-year hold you get into them you would get into the second hurdle in here seven and then finally once you sell your ten you would get into that third hurdle again those cash flows then all floated these summaries above and provide a pretty quick detailed accurate assessment of the effects for both the GP or the sponsor you and the LP the final model I have which is the simplest and the one I like the most and I've seen syndicators use a similar former on structures it's just pari passu you know throughout the hold so in this case the GP puts in five percent they raised ninety-five percent of the funds but depending on how the project does they'll get a kicker in the kicker's of percentage of the residual sales price so if the project achieved certain equity multiple levels that's what dictates the sponsors kicker at sell if they get above a three project equity multiplier to get an additional ten percent of this residual sales price I really like this cuz it's simple so it's just a nice it's a nice structure and it really ensures that the GP only gets paid if the LP does well on the investment and it goes well and you know they hopefully more than double their money if they're if they're doing a longer term old so that's really yet the last tab here is just a summary of these there's some conditional formatting so the darker cells or what's more advantageous for the GP or the LP and this is just something you look at to come to terms and pick something pick an arrangement pick up pick it arranged meant that works for both you and your partners and then you're limited for you as the GP and for your limited partners as well so that really wraps it up you know this this video took a little longer just because it took me some time to explain it but I've gotten pretty efficient using it and like I said I can wrap up on I can put together a pretty solid analysis in about 15 to 25 minutes assuming that they send me good information you know if I'm having to go do their own caucus and come up with my own project house and things like that it might take a little longer but overall it's really been a nice tool for me and it's been a nice tool for my clients and hopefully a nice tool for you so if you have any further questions you know feel free to reach out to me directly I have a lot of great resources on the sales page and blog posts that explain some of the fats as facets of this model more in-depth but because my goal is that you know I want you to download this and not be like ok where do I start I want you to feel comfortable and and feel confident and just know that there's resources out there that you can lean on and use and put together something that you're comfortable with and confident in and proud of so like I said have any questions feel free to reach out directly and and hopefully you can you can use this model and it adds value to your business so thanks for watching and hopefully we'll touch base soon