Real Estate Construction Cost Modeling - How To Build Out Straight-Line Cost Schedules

If you're trying to model out your construction costs for a real estate deal in Microsoft Excel, but you don't wanna go back and change the formulas every time, stick around because we're gonna be building out our construction costs step-by-step in Excel in this video. For first dibs on all new real estate financial modeling and career training videos, make sure to hit that subscribe button and hit that notification bell to be notified every time I release a new video. If you're looking to model a commercial real estate investment with a renovation component, you're going to need to add those cash flows into your model. Now, you'll also want these to be able to be modified in a dynamic way. So all you need to do is change one or two inputs and all of your cash flows will be modeled out for you. So by the end of this video, you'll know exactly how to do just that to change your assumptions and have your model change automatically for you based on the formulas that you built. So what we're gonna do in this video is called straight line cost modeling. And essentially what that means is that the costs are going to be spread out evenly throughout the period that we specify that the construction is going to occur. So what we're gonna do is jump into Excel and work through an example of this step-by-step together. All right, so we're in Excel now and what I have here are a few construction assumptions and then I have construction expenses that we can build out all the way through 24 months. So we can build this out for a full 24 month period and then we have our construction assumptions over here as well. So everything that's in blue text is going to be a manual input that we want to be able to change the model as we change these manual inputs and then we don't wanna have to change anything else on a case-by-case basis. So I don't wanna have to change this length of construction and I've actually built out this formula already. So what we need to do here is build out our construction expenses. So we need to build out a formula that straight lines this $1 million over this six-month period starting in month five and ending in month 10. So how are we going to do that? Well, to do that, what I'm gonna do is use an if-and statement. And the if-and statement is essentially going to test if two variables are true and if so, return a certain value that we tell Excel. So essentially what we wanna tell Excel is that if we are anywhere between month five and month 10, so we're assuming we start in the beginning of month five and we end at the end of month 10, so that's a full six months of construction, then we want Excel to take $1 million and divide that by six to get even monthly costs over that six-month period. So I'm gonna start this with an if-and statement. So I'm gonna type in if and then within my first logical test, I'm gonna type in and. So and is going to check if both of these logical tests are going to be true. So the first test that I'm going to do is I need to test if the month that we're analyzing, so month one in this case, is greater than or equal to, and then I wanna select month five. So that's gonna be our month start. So I wanna select our month start and then hit F4 to lock that cell because I wanna be able to copy this formula over. Then what I wanna do is add my second logical test. So my second logical test is again going to test the month that we're analyzing, except this time it's going to test if that month is less than or equal to this time, the month end. So this is the month that our construction ends. And then I'll lock that cell again because I don't want this to change as I copy this over. Then I'll close my parentheses and then Excel's gonna ask me for a value if true. Now, if this value is true, that means that we are in this construction period. So we're in one of these six months of our actual construction. So to calculate what that is, I'm gonna take my total construction costs and lock that cell by hitting F4 and divide that by my length of construction. So I'll divide this by my length of construction, which is right here, and six months in this case. So if I hit F4, I'll lock that cell and that's gonna be my value if true. Now, my value if false is going to be zero because if we're not in that construction period, then we won't have any construction costs outside of that construction period. I can close my parentheses and hit Enter. And now I have my construction expenses for month one. Now what I need to do is copy and paste this all the way over. So I'm gonna hold down Control and hit C to copy, and then I'm gonna highlight everything all the way out through month 24. Then from there, I wanna hit Alt E S F to paste these formulas just so I don't mess up any of my formatting, hit Enter, and now I have my construction costs for every single month. Now you'll notice that between month five and month 10, we have our construction costs flowing. So if I take the sum of all of these and take a look at the bottom of my screen, I get $1 million total. So now what happens if we change any of this? So what if we change our construction costs to say $5 million? How does this actually change? Well, this is going to all change automatically, dynamically for you based on the formula that we built. Now what happens if I change my month start and my month end? Well, if I change my month start, I can say this is month one, and that's going to draw out our construction expenses from month one all the way through month 10. And then if I change my month end to say month eight, that's going to compress the time period even more. So that's how we can build a straight line cost model in Excel. All right, so now you know exactly how to build out your construction costs and straight line cost modeling in Microsoft Excel for your next real estate investment. But how do you actually build out the rest of your model to make sure that you've captured all of your cash flows and again, you're building your model dynamically? Well, to help with that, I've created a free three-part real estate financial modeling crash course, and you can actually grab that for free in the link in the description below. Now, if you like this video, make sure to hit that subscribe button and hit that like button and share it with anyone else who you might feel could find this information helpful. I hope that was helpful, and I'll see you in the next video.