Collaborate on Real Estate Invoice for Operations with Ease Using airSlate SignNow

Real estate invoice for Operations

hello and welcome as you may know this weekend is the next round of the financial modeling world cup round three i think for 2022 uh and the question guide says there's going to be what looks like a complicated question on real estate modeling so that's obviously a bit of a vague hint but it made me think of this question from way back at the start of the fmwc the last question from the first round on real estate modeling and i thought i would give that a go to brush up my real estate modeling skills before i hit the round so let's take a quick look at the assumptions we're going to buy a piece of land for five million dollars we're going to develop a bunch of buildings on it with with lots of units in them and rent them out and then at some point we're going to take out a couple of loans a construction loan during the construction period and then a permanent loan later and then we're going to actually run the building incur some optics costs and then eventually we're going to sell the building oops that's that is missing but i must have moved that that's supposed to be in month 84 at the end of year 7. we're going to sell the building and then we have various assumptions here about when the rent increases and costs increase and the cap rate so let's uh let's dive on in and just set up a model oops model to be able to spell uh turn off my grid lines and then we're told we want a monthly model for 10 years so here's the model start date one jan 2021 so i'll just leave a few columns empty here so i have some space for labels and also in a model like this it helps to have a total column so let's just say we start over here for now we can always shrink it up later so that's the model start date obviously a different formula from the rest of the row so i'm going to change that color and let's just say month number one number one number i can fit in a month uh so i want to sequence 10 years 120 months oops sorry comma so that it goes across rather than down um then we can say date this one and turn that into a date and copy it across to here and i think i've mentioned before that e-date doesn't work if you're trying to do uh if you're going to jump along the last date of the month but it works fine if you're staying at the first of the month so you can see every date here is the first of a month and sure enough after 10 years we're at the or at december of 10 years later which is 20 30. so then as usual i'm going to hide all the columns that come after that i'm going to freeze my panes here so that when i scroll down i can still see that and when i scroll across to my very wide model i can still see my labels that i'm going to put here so then first thing i'm going to think about is putting in some indices so i've got a rent increase rate by year and i've got a cost inflation rate by year and i'll just quickly check in the in the notes i think that those are applied at the start of the year yeah applies every rents go up every year on jan 1. and where is costs yes costs also up every year in january okay so first thing i'm going to do is just have a i saw have a couple of things here so the first section which is just going to be indexing indices indexes i'll call it indices even though that seems a little pretentious but never mind uh so we'll have rent index and cost index and both of those are going to start off with a value of one because that's how any sales work hfc and again different formula from the rest of the row so i'll format it like that i've used ctrl shift and one or ctrl exclamation mark to format this whole row as a number with two decimal places so then i'm going to say this times one plus if month if the month we're in uh is equal to one then you know actually sorry it's going to be useful pause here and just put in a couple more helper columns like here copy that across uh and i'll i think i'll also put in and keep start you never know whether you're going to want q quarter start dates or quarter end dates so i'm going to put that as well before i get too far along and then of course i can just make this the plus one copy that across copy all those across so then i'm going to widen my columns slightly so that those all fit so now let's come back here and actually let's do let's do month as well just because that'll come up in other places so now this will be a little bit simpler i'm going to say this times 1 plus if month equals 1 then i want to look it up in this table but since this table has one thing per year i don't even need to do like an x lookup or anything like that i can just do g dollar uh where am i going sorry h dollar three minus dollar each dollar sorry dollar g dollar three plus one so that's going to be how many years have we gone on from the first year uh and sorry i need to index then so it's going to index in this set of rent increases and the index is going to be if sorry so i shouldn't have left the f over there if that's one sorry no i should have left that over there thank you pardon so i'm saying one plus if i'm in january month is equal to one then index in here to get the amount otherwise zero close out about the brackets and if we copy that across we'll see for the rest of 2021 it stays the same and then rent goes up three percent a year so we should see a jump three percent and we do and scroll along again just do one more quick check goes up another three percent and over 10 years it ends up going up 30 and remember that's nine increases so that you can see the compounding effect there because if it was just nine it was just three percent times nine you get 1.27 so that is compounding which is what you wanted to do actually if i take the dollars off the rows here since my cost inflation is in the row directly below i should be able to just copy that formula down and let's see if i copy it across yes so i've got 1.25 so that's my two indices now so i've got well let's see let's start with some uh start with some simple things like the construction budget uh which reminds me actually there are there are acquisition costs it doesn't mention it here but i think it does say it in here the acquisition costs are incurred in december i think yes land is expected land acquisition happens on december 31st so i need one more month here uh it's going to just be that minus one uh okay so then say investment uh sort of land acquisition and then i think it just calls the other things soft costs and hard costs yes actually let's be good just think here okay so then the land acquisition is just going to be 5 million investment right at the start i'm going to format that as a number no decimal places and just so i have it for later i'm going to get a format ready that has a dash for zeros because that's quite useful to have i'll widen that out all the other columns so they can fit that as well and this one is just a unique formula in the row so let's do that and now my soft costs and hard costs are spread out over the construction period which we're told is 20 months so i'm going to say if my month number and just be careful this this one on top is my month number that runs from 120 the other one is the one that runs from 1 to 12 and then back again so i'm going to the one on top if that is less than or equal to this then give me i can actually just select them both uh divided by this otherwise zero sorry i need to put minus in front of it and then just copy that across didn't like that what's going on oh yes that's because this needs to be locked in now for that across yes and then we should have 20 months up to month 20 good oh oops yeah all right fine so that's not filling out a zero the rest of the way across can i v stack two zeros on top of each other that's mostly just curiosity but a way i can output two zeros is like that okay so that's my investment costs and then i'll just put a total at the bottom of that and put that all the way across and i'll put an order above that and make it bold okay so that's the investment now let's talk about operations so we need to figure out when each of the buildings come online when we'll start collecting rent and so on so what we're told is each building comes on the first building comes along online in month 15 last building comes online in month 21 and they have an occupancy rate that ramps up as they fill up and we're given the rent per month number of units so we've got to figure out how much is available to rent now there's a an interesting kind of decision to make here um i've actually decided which way i'm going to do it this time but the the decision to make is basically which which uh which way around you want to do it in other words do i want to build a model of building one where i say you know how many units well whatever maybe month of operations then units available and occupancy uh and then whatever comes after occupancy is rent i guess or do i want to build a model where i say month of operations for building one building two etc made a bit of a mess there but anyway you get the idea um let me copy that down now there there are kind of pros and cons to building each way obviously the biggest pro to just laying out the seven buildings this way is you've got your kind of seven assumptions stacked on top of each other in a in a pretty neat way um it's easy to work with that um and it's i think from a model build perspective it is actually a little bit easier to build that way but on the other hand i think for a model reader perspective especially if you wanted to be kind of readable by someone who's kind of looking at a pdf or a printout or something like that rather than just someone who is in excel and uses excel a lot i actually slightly prefer uh the version where you lay it out by building because then you've got you know if someone wants to understand hey how does building one work then you can say here's the whole building one model and it's a little more kind of human usable that way so i'm going to lay it out that way but you know there's there's no reason you can't do it either way the important thing is if you lay it out by building then you know you've got your sort of seven buildings here and you'll have a total at the bottom uh sorry if you laid out by by like line item then you'll have you know your seven buildings and a total of the bottom if you lay it out by building then you'll need to do the total in a in a slightly different way but i'll show you how to do that that's not hard to do so let's do it that way then uh so first i'm going to just put a one here and again i'll make that red then first thing i'll do is just index on that uh so we're looking at building one so now let's go month of operations and here we'll say max of month minus index here so this is the month they start operating uh index that on this now the important thing is i am not going to lock in the um the row here because i want to be able to copy this whole model down and make sort of blocks of it i'm going to lock in the column so i'll lock it like that max of that and zero so and again i'll just take this format that i have here okay this cross so up to month uh month 15 we're nowhere and then from month 15 on we start being operational now that actually might be off by one because i think i want month 15 to be the first month of operations let's quickly double check the notes building one will be ready to rent out on day one of month 15 okay so in other words i want this to be plus one so pop that across copy it just copy it back so now i have a month of operations and i'll have units available and that's just going to be if this is greater than 0 then index over here okay so it's a little odd the way this has been laid out but it's it's fine um so if if there was any building that had both types of unit then we'd need to do this a little differently we need to lay out you know how many of how many units of type one how many units of type two we don't need to do that here we're just going to say uh you know it's it's either got 48 units of this type or 32 units of this type uh so i'm going to index on that uh my row is going to be dollar b 20 which is that cell i had before over here again i'm not locking in row 20 because i want to be able to copy down copies of this model block and then 0 gives me no i don't want to accept that go away so 0 just means that i take both columns so i'm taking some row in both columns and i'm just going to sum those uh because that's as easy a way as i need to get you know when one of the values is zero and one is non-zero i'll just do it that way otherwise again i'll zero that format carry it across so in other words starting from the first month of operation we have 48 units available to rent then we want the occupancy so here we'll say if this equals zero again zero otherwise uh index so we wanna go here the occupancy rate and we're gonna take the min of uh the month up here and uh so in other words if we uh if our month sorry h2 is wrong there thank you pardon so i don't want to take the month i want to take the month of operations for this particular unit not the month from the whole model which is a different thing so the smaller of that and uh the assumptions in other words from month 15 onward everything stays the same so we'll count one two three four five six seven eight up to fifteen and then fifteen fifteen fifteen fifteen fifty uh let's close that close that make that a percentage and again i'll just format it so that zeros are dashes because that makes it a little easier to see when nothing is happening so month one we start off at twenty-five percent then we gain five percent a month uh oops sorry wrong way over here five percent a month up until month 15 of operations and forever after that we're at 95 percent occupancy so then we'll say units rented and we're specifically told not to round this so that's what i shall do in other words just assume that if you work out that ten and a half units are rented that just means that uh you know a unit is rented for half a month or or whatever makes sense uh and then uh income just trying to think what the neatest way is to get something out of here ing to which one of these is non-zero i guess what i can say is call it base rent because obviously it gets escalated so i'm going to say let unit knows be index i'm going to index on again dollar v20 zero that will give me 0 48 for this one 32 0 for the other ones and then i can say some product this and actually it's a good point um yeah i'm over complicating this a little there's no need for that so let's just do this let's do some product this will give me the number of units so i was trying to work out you know how could i pull out just the 1600 or the 1400 ing to which of these was empty but actually there's no reason not to just multiply them together and then you'd actually have something that would work if you had mixed unit buildings as well so i'm going to take this index will give me the row of here so this might be 0 48 or equally it could be you know 20 25 anything like that it could be a mix and then i'm going to multiply each of those by the corresponding rents down here and that'll give me my base rent and that's so i was thinking about base rent per unit but that's the base range for the entire building which works just as well so then i'm going to take my base round for the entire building again lock in the column but not the road because i want to be able to copy this block down multiply it by my rent escalation index lock in the row and then multiply that by my percentage occupancy and then again i'll take this number format put it there and then we'll carry across so it's nothing for the first while and then here we get 17 000 so again it's like a little over a quarter of this because it's escalated by three percent uh and then we carry on across so once it's fully up and running you're at 67 000 goes to 69 000 when you hit a new year because the rent has gone up and so on across so that's your simple model and then then what we're going to do is just copy this and make make the rest so here i'm now going to just say equals this plus 1. and now we should be able to oh it's not happy about that why not i wonder what is it trying to put together that it doesn't like oh sorry i just hadn't calculated okay all right it's getting worried over nothing all right so that's building two and then we just keep on copying uh so if we're careful about it let's see there's eight rows so i need five more copies i need 40 rows in total so if you watch that counter up in the top left as soon as that hits 40 go out full width there we go so now i've got building 3 4 5 6 7 and we're all good so now let's do call it building summary summary all right let's call it rent summary so what do i want i'm not really interested in i guess i'm just interested in how many units are available to rent uh how many units are rented what's the rent income and for each of those i can just because i've kind of laid out identical template models the names are the same everywhere so i'm just going to go up here lock in rows where this set of labels in the same set of rows lock in is equal to this lock it over there and again i'll take the format so obviously no no units are filled at all in the first little while because nothing is up and running then from month 15 we should see the first one start to appear so we have 148 unit building 25 percent left and then the next term we get the second building which is also 48 units now we're on 96 and we'll add a quarter of that and a little more than a quarter of the other one and so on carry that across so that gives you your rent summary so you can see this way you've got sort of summary table of of everything that you need to know across all the buildings but you can also look at any one building and if it comes to like model review i personally find this a much easier format to review i can say okay so look when i'm in you know month one i've got you know i go from zero to 48 units available i start off at 25 you can see everything that relates to one building in one place but again there's absolutely no reason that you couldn't kind of pivot this basically and have you know these these items as your first field and then a list of buildings underneath just different ways of doing it so now let's move on we have operating costs so operating costs pretty simple uh off x so we're told starts in month 15 that's 100 000 indexed so i'm just going to save minus 100 000 times i always hesitate over whether to just do kind of algebra so for example to say i only want it from month 15 onward i can multiply by month number greater than or equal to 15 and so month number greater than or equal to 15 evaluates to true if the month number is greater than or equal to 15 false otherwise but if you multiply by true or false it treats false as a zero so that zeroes out the number there and it treats true as a one so that just leaves it unchanged but sometimes i er on the side of you know not everybody knows that about excel it's better to be more clear so i'm going to say if i'll leave a space to put my month when i come back to the other tab i try not to flip between tabs too much if this is less than or equal to sorry and it's greater than or equal to 15. and if what is let's come back here uh my month number says h dollar two then i want minus that times my indexation index whatever you call it anyway which is going to be h dollar 10. otherwise zero again i'll take that format carry it across so now we can see uh month 15 is in the second year so it's only gone up by 2.5 percent and then it kind of keeps ticking up once a year thereafter so that's all working fine that's then my next thing is i've got my net whoops net operating income which is just my total rent minus my opex i'm just going to add them because i've got the signs the right way around to do that and then project value and the project value we're told is 12 months of net operating income divided by the cap rate so simple very very simple evaluation formula so let's come over here uh i guess do i want to cap rate was changing at all then i'd probably put in a row to show the cap rate but since we're assuming it's five percent all along i'm just going to have that baked into the formula so what am i going to say uh just up to here where i actually did that before yeah i just took year minus first year okay so i'm going to say i'm going to say if net operating income is greater than 0 then net operating income times 12 divided by the cap rate which is going to be index and the column number i'm going to take is the year minus the first year of the model plus one otherwise uh otherwise i won't say zero so that doesn't make sense otherwise i'll say not applicable so no no evaluation is applicable come over here lock that in close it okay so let me carry across so first positive year and i'll take the number format so just a quick sense check let's call it about 4 times 12 is about 50 divided by 5 should be about a million and that works out good and then we've got bigger numbers over here so again let's just widen out our columns so that everything is visible i like to keep all the columns the same width so i carry that back across put that here as well okay so now we've got the project value so the next we've done the investment we've done the operations now the next thing is to think about the financing uh with only 1g so the first thing is the construction loan uh so we've got an opening draw down repayment closing and interest so what do we know about the construction loan uh 70 loan cost intensity in other words we can borrow 70 percent of the costs that we're incurring all all of these construction costs interest rate of 7 percent i think it says over here it's actual over 365 see yes actual over 365 interest interest paid on the last day of the month and we refinance it in month 30 there's an origination fee of one percent so so first things first uh i'm going to say sorry let's get a useful number format here for all of this and then i'm going to format this one as red because that's a different formula for the rest of the row and then the drawdown is going to be uh sorry or yes negative because our costs are negative but we're going to add to the loan so minus that times the cost intensity which is here 70 percent of that that you can borrow carry that across that's how much we're going to borrow uh oh now okay i have not included a total column although this reminds me that it would be useful to have a total column uh anyway sorry so then i'm going to have uh my repayment is just going to be it's i think it's not repaid at all until yeah not amortized it'll be refinanced in full when the permanent mortgage loan is pulled the last day of month 30. so my repayment is going to be uh equals if a month equals 30. then uh minus h t all right minus g so minus the opening balance uh sorry otherwise zero so if you get that false it means you haven't filled in the second argument carry that across and then closing is just some opening previous periods closing and then we can carry all of this across now let's see do to do to do so build up build up build up then we've borrowed a certain amount and we carry that along until eventually we pay it off and then we're back to zero so again i'll just every time i need to make the columns wider i'm just going to apply that all the way across so then interest so the the drawdowns and repayments both happen at the end of the period because that's when we make our when we make our uh construction payments so that's when we draw on the loan and it's also when it says it gets paid off on the last day of month 30. and so my interest is just going to be based on the opening balance so it's going to be opening balance and actually sorry that's going to remind me that i need to fill in a date here just so i have it so i'll go back a month from that to the end of the month before end of november and then add one to get the first day of december just get a message here okay so now i can say it's opening balance times the end date sorry lock in oops minus the start date uh plus one divided by 365. that is the one one time i do sometimes put a constant into a model is when it's actual over 365 but i guess i guess i guess a little reluctantly uh i should put an assumption in here today's basis i'll call it number and it should be 365. you may consider that overly picky and while that would be a perfectly defensible view i would not object and then the fee we think we pay right at the start but it's based on the total amount to be drawn origination fee one percent of the loan amount on december 31st when it receives the first charge so that is going to be the total amount drawn down so the sum of all the drawdowns that are yet to come times one percent which is here that's 300 four thousand that makes sense this is complete nonsense i am i haven't multiplied by the interest rate i'm trying almost ten percent interest this will add up to uh this will add up to charging uh the full loan amount in interest over the year that's really silly so good to do a sense check every now and again uh so let's actually multiply that by the interest rate it's just when i went to click on the one percent origination fee here i was like wait i don't remember clicking on that seven percent so anyway these things uh will usually become pretty clear uh when you start putting summary schedules together and realize that your interest is you know massively larger than the total amount you borrowed or something like that but good to check as you go alright so that's the construction loan and then the permanent loan so the permanent loan is a mortgage style loan so again we'll just have drawdown repayment closing interest and fee so uh again i'll just take a fixed opening value of zero and closing here's some of what's above it and copy those all the way across and now how much do we borrow well we can borrow 80 percent of the project value as of the date that we that we borrow so we're going to say if the month is equal to 30 which is the refinance month then 80 times time here project value g85 but if you're wondering why i kind of hesitate find the number and then type it in rather than clicking on here after you've been off the sheet if i uh if i click on another reference on the sheet now it'll add in the sheet name and that means that the formula will not behave in exactly the way i want it to so the easiest way to avoid that is just to type it in this case uh but there are other ways as well and i've got another message what's going on all good okay so let's see copy that across and sure enough we borrow 50 million 80 of that value which is indeed much more than we need to repay the construction loan so that's a nice uh boon for our equity holders and then we need to think about repayment so how are we going to do that let's copy these down so it's a mortgage style thing so i'm just going to fill in the payment i'll here put it here uh so it's gonna be pmt um so the rate is going to be uh four point five percent divided by twelve number of periods is going to be 360 and then the pv is going to be um i could do a lookup to find month 30 or i could just sum up the drawdowns because there's only one drawdown which is the full amount again it's included the tab name in there which i don't like i'll get rid of that and that'll tell me this is the amount of my monthly payment so now let's zip along so let's do the interest calculation now i guess opening times the interest rate now when it's uh it doesn't i don't know if it spells it out but when it's a mortgage style payment um at least if you want to use the pmt function then you've got to treat every month as equal so we're just going to do that so divide by 12 rather than doing actual over 365. um and these are obviously all the same because right now we're not paying anything off and then here we will say payment plus interest and then it's going to be the maybe a negative number so on the max of that and obviously it can't pay off more than has been more than your opening balance because the drawdown comes at the end so you won't make a payment at the end of the first month so that should hopefully give me no payment up until i have a loan and including that one and then start making payments after that and there we go so let's see after oops sorry just a quick sense check to say how far have we come after i guess seven and a bit years of the 30 we've paid off a little over 10 that seems sort of kind of right uh if i was very unsure i would kind of extend this model out another actually you know i can i can do a quick check so let's let's do a quick check i don't want to build my model out to 30 years when it's a 10-year model but what i can do is [Music] is do this so i've got 90 principal payments made so then if i say sum of principal payment with a rate of 4.5 percent over 12 and then period is going to be from 91 up to 360. so i want a sequence 270 periods starting from 91. that's my period number of periods is 360. and the pv is the starting value which is going to be the sky over here close two brackets yes and sure enough 270 more principal payments will exactly pay off that loan so that's a good check without having to build out another 30 years of model to be sure and then let's see we do have interest appearing here good and then finally the fee is going to be the sum of the drawdowns times that one percent v and again we'll format that and i'll again make these red because they're not formulas that are across the whole row okay so then i think that's everything we need to model except equity cash flows so let's model some equity cash flows oh no sorry well the sale yeah i'll do that as part of the equity cash flow equity cash flow so what are the equity cash flows let's just do company cash flows so we got the investment which is here right here turn that across so that's just for the first while investment investment uh we've got our net operating income which is the rent and the opex so again nothing for a while and then we start losing money and then we start making money all fine then we've got everything to do with the first loan so i'll just make that a category construction loan um so we've got drawdown repayment uh interest and fee so the drawdown is here that's negative so that's positive cash flow repayment is here and that's negative cash flow and actually this is where i really do need to have a total column here so i use this column only for base random right so i'll use this column my totals and sure enough those two cancel each other out nicely then my interest is that guy and i do need to put a minus sign on because that's negative cash flow and then my fee and again that's negative cash flow um so that's construction loan and then the permanent loan loan we'll do the same thing draw down repayment interest and fee so the drawdown is here and that's the 52 million as expected uh the repayment is here now you'll notice i'm only doing the sort of gradual repayments i have not yet done the the sort of disposition so the the disposal where we get sale of the property and uh and pay off the loan i'll i prefer to model that a little separately um so basically what i have you know everything down as far as here is going to be you know a model of the company uh as something that continues to exist and operate for for 10 years or more and then i'll down below here i'll do this sort of equity view where you exit after seven years uh so that's the drawdown that's the repayment uh the interest is up here somewhere here and again i gotta put a minus sign on that cross and the fee is again got a minus sign sorry it's here okay so that's all fine uh so then oh do equity cash flow before disposal and that's just gonna be the sum of all of these uh and then disposal cash flows so uh mortgage repayment uh sale proceeds and there's a sale fee disposition fees disposition fee okay uh so then the mortgage repayment is gonna be if the month oops is equal to the disposal month which i'll go get in a second then minus closing balance of the mortgage which is up here somewhere permanent loan closing yes otherwise zero and the reason i'm doing this thing of indenting a line and grabbing that is basically i want to get all the references on this tab before i go off to another tab so i don't have to deal with the thing of it putting the tab name in there so if g2 is equal to the disposal month which is 84. and so it was belong to month 84 and there it is boom pay off the mortgage uh then the sale proceeds i think we just sell it for market value right let's see a sale price is property value yeah so we just sell proceeds it's just going to be if i'll copy this down if the month is that then i just want the valuation from up here project value otherwise zero copy that across and again let's see in month 84. there we go 87 million good then the disposition fee is just gonna be minus that times one percent so then i'm going to show equity cash flow including disposal and that's just going to be equals if month is less than or equal to my exit month then g129 plus i should have put in a tote for those which in a second let's just say it's here g135 otherwise zero total here so now you can see everything disappears after month 84 and you get this big cash flow in month 84. uh so let's see how am i doing for time do i want to no it's already been a pretty long video i was contemplating whether i wanted to go through and do the questions see if i got them right but honestly this has already been a pretty long video because this was a pretty uh pretty long question i think they've learned to calibrate things a little bit better since uh since that first exam but this one was pretty brutal oh yeah the one thing i will do i'll just put in the irr uh so i'll do x irr use the equity cash flows these are the all cash flows happening quarter end dates so just do that and boom we've got a 44.7 equity irr uh and then there was also something about doing a sensitivity on that as the disposition month changed but again let's just kind of do a data table on this uh pretty pretty straightforward okay that's all i've got for you today i know i i promised i was going to have another piece of the afm prep which i will come back with next week but you know i'm getting ready for uh getting ready for the competition weekend so that distracted me um but yeah i'll be back with that next week and uh and if you look at this before uh you do the fmwc then i hope it helps you and good luck and i'll see you next time thanks