Forum Replies Created
-
Posts
-
Kevin,
Sorry about that! It should be fixed now. Give it a try and let me know if you’re still encountering the issue.
I’d recently updated the video and apparently dropped in the wrong link!
Thanks for the question and it’s a really good one. At a high level, the answer is you need to input the circuit breaker formula wherever there is a circular reference until they have all been eliminated when the circuit breaker turns on. Below is how I go about the process of finding these cells in a sea of cells with errors. I hope this is helpful and if any of the video is unclear, please let me know.
Thanks,
MB
No worries. You are extremely close. Just the difference between a letter and a plus and minus sign, see below:
——————–
=(NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(L$9-$E20+1)),$G20/2,$H20,TRUE)-NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(
L
$9-$E20
–
1)),$G20/2,$H20,TRUE))/(1-2*NORM.DIST(0,$G20/2,$H20,TRUE))*$C20
———————
The L that is separated should be K referring to the previous period and the negative (-) sign should be a plus (+) sign.
Let me know if this solves the issue.Hi Scott. It’s a little hard for me to tell you exactly what’s going on without seeing the formula, but short of posting the formula, one thing you might want to do that I frequently do with long complex formulas that are giving me problems, is I like to first copy the formula from one of the cells and then go to a blank cell and first put an apostrophe (‘) in front and then paste the formula in and hit enter. Putting the apostrophe in front prevents the formula from calculating and allows you to see the formula laid out as text so you can analyze it.
In this instance, what you can do is in the cell right above or below it, put another comma in and paste the formula as displayed in 2.4 and which I copied here:
=(NORM.DIST((AND(J$9>=$D10,J$9<=$E10)*(J$9-$D10+1)),$F10/2,$G10,TRUE)-NORM.DIST((AND(J$9>=$D10,J$9<=$E10)*(I$9-$D10+1)),$F10/2,$G10,TRUE))/(1-2*NORM.DIST(0,$F10/2,$G10,TRUE))*$C10
Look closely at this formula and yours side by side. Pay close attention to dollar signs, commas, etc. and try to identify any differences. Also, as a further hint, in this formula, row 10 is referring to the budget line item, while row 9 is the row that contains the fiscal month.Why don’t you start there and let me know if you are able to resolve. And if you are continuing to have problems, post your formula and we can take a look.
Just to add to this, one difference between this Sample Report I linked here and a real report is that on a real report that you purchase, between the Classic and Help tabs, there will be a Response tab that lists out your competitive set with high level data about the set including class type, open date, room count, if there was a change in room count, and a verification as to whether they did or did not report their data for each month of the report.
Hi Scott,
STR (pronounced “Star”) is an incredibly valuable and unique company to the hotel industry to which thousands of hotels around the world voluntarily report their data and to which anyone can purchase their various products that report on this data.
If you are looking to find out what the RevPAR is for a competitor, you can purchase a STR Report and select the particular hotels you want reported. Outside of that, it would be hard to calculate a competitor’s RevPAR unless you were keeping track of their ADR and had access to their occupancy data (RevPAR = ADR * Occ).
Below is a link to STR’s product page so you can check out their various products, many of these have samples for you to download and check out at no cost:
https://www.strglobal.com/products/industry-partners
Also, here is a sample Trend Report from their website that reports historical Occupancy, RevPAR, ADR, supply, demand, and revenue monthly over the last six years on a particular competitive set of your choosing.
Hope that answers your question!
Hi Brett,
I can’t speak to the apartment development model as that is Spencer’s and I am not as familiar with it. As for the hotel acquisition model, I don’t believe that is a great place to start for a customization to a development model mainly because there are a ton of Macros in it that would make customizing it in this way difficult, plus there is probably a bit too much optionality in it with regards to operating detail that may be overkill for building a brand new hotel. The model was really built to give a user the ability to work through a repositioning.
Spencer will have to chime in on his thoughts about his apartment model.
Hi Brett,
Thanks for the question. You are correct in that you would model it the same way you would a multifamily or any other development deal; as the goal of building out a development cash flow for any asset, hotel or otherwise, is to try to mimic the anticipated development schedule, and thus, the funding requirements over time. In that respect, there is no real difference to how we would model hotel development cash flows. There are, however, unique budget line items and considerations for hotels, which is a different question.
And if you are using debt, you will likewise build out a draw schedule the same as you would for any other asset type to properly allocate these costs between equity and debt (draw schedules are covered in Section 7 – Intro to Real Estate Debt).
To answer your second question, we unfortunately do not have a hotel development model, yet. In the queue…
Hope that answers your question and best of luck with this opportunity!
MB
Thanks for the note. And really glad you enjoyed the course!
Yes, you are correct. In question 3, the prompt did not tell you to grow your exit cap rate in tandem with the going in cap rate. Thanks for catching that and it has been corrected.
About the loss-to-lease, the template has this assumption, cell C20, highlighted in green upon downloading with the formula already contained within it; which green cells, as a general modeling convention, are an indication that the input is not hard coded and that there is a reference to another cell, or cells; and that the assumption generally should not be altered or changed, but will change as the result of other cells in the workbook changing.
Blue cells, on the other hand, can and should be altered at the source. So following that convention, the loss-to-lease assumption in this DCF model should always be the result of the formula that was already in it and not be altered, unless you want to change the assumption’s methodology as we do in quiz 2. One could also argue that this cell’s text color could be black because it is a formula. Spencer may argue this point because his methodology is to make text green only if it is referring to cells in other sheets.
For further reading on text/cell colors and general modeling conventions, Spencer actually wrote a great post on this topic, which can be read here: https://www.adventuresincre.com/re-modeling-best-practices/
Let me know if that clears things up or if you are still having further issues with this.
Thanks,
MB
To follow up a little bit more about options and address your thinking about land as equity in this scenario; with option agreements, the landowner is not actually contributing the land as equity to the venture, but is really agreeing to sell the land at an agreed upon price at a certain date in the future, while giving the potential buyer the exclusive right to lock up the property for that period of time so that no one else can come along and buy it. This allows the potential buyer to go through any number of processes that will both remove risk and also justify the purchase price. In many cases, the process might be getting entitlements or reaching a certain amount of pre-sales or securing a tenant, for example. If the potential buyer fails, then he or she can simply walk away and not purchase. Options are always negotiated, so there can be costs associated with them where the buyer will have to pay the seller fees for the option regardless of the outcome. Free options are also common that have no costs associated with them.
Hope that adds further clarity.
Hi JP,
This is a great question and also a somewhat common approach to land development that, as you said, reduces the dollars at risk to a developer. In these types of scenarios, the more common approaches would be (1) for a developer and land owner to enter into an option agreement whereby the developer locks up the exclusive right to purchase the property upon reaching a certain milestone (in your case, completion of pre-sales) or (2) the developer and land owner reach an agreement on land value and the land owner contributes the land to the partnership with an account credit equal to that price and rides along for the development process to get a return at exit based upon the agreed upon partnership structure. The second scenario, like any development JV, is subject to endless scenarios that can be negotiated about how this would actually work between the partnership.
For your specific example, unless I’m misunderstanding, it sounds more like scenario 1 above because there is a set purchase price paid to the landowner upon reaching a milestone and they are not participating in any further upside. In that case, I would most likely not consider the land owner an LP and would treat the land purchase as just that. So my thought would be to put the land purchase price in the budget section of the model and in the Land line item in your cash flow projections create a formula that triggers the purchase price to hit upon reaching the milestone. In this case, it would refer to completion of pre-sales, so the formula logic would be something like ‘if condo pre-sales equal 100% in this period then give me the land purchase price, if not, then give me zero’.
Hope that answers the question and let me know if you have follow ups.
MB
Hi Nick,
Thanks for the question. Although they may exist, I am not aware of any widely marketed databases for operating data. However, I don’t think that will be an impediment to helping you come to reasonable assumptions for operating projections.Since operating budgets are first and foremost, property specific, and secondarily market specific, I would say that the best resources for getting comfortable around op ex projections for a specific property are through the property’s historical operating statements in combination with sourcing further market data and intel from the broker and/or the local broker community, other OM packages of similar asset types, and definitely local property managers, if they are a resource available to you. Triangulating between these sources should be more than sufficient to get you comfortable around your operating projections. I will elaborate on each resource a bit more below.
Historical Operating Statements
Since every property is unique unto itself, the property specific historical operating statements are actually going to be the best resource and starting point for understanding what it takes to run the property and what future costs might be. Using these statements in tandem with learning how the day to day operations run and who is running them, can help you understand if the asset you are looking at is operating efficiently or if there are specific areas where you may be able to decrease costs while preventing a decrease in revenue. If you are unfamiliar with what you are looking at and if the historical operating statements seem unusual, then this is where the broker and/or a local third-party management company may be a great asset to tap into.
The Broker
To your last statement, yes, part of a broker’s job is to sell the upside scenario or most optimistic (and yes, at times, unrealistic) outcome of a property to achieve the highest purchase price possible for their client. I wouldn’t necessarily say their pro formas are unreliable, but more often an overly optimistic starting point and it’s our job as prospective buyers to check the likelihood of it and bring things back into what we would think is a more likely future scenario.
However, a reputable broker can be incredibly helpful as they see tons of volume, which gives them lots of data points; they know the market; and can actually guide you in the right direction by talking with them and asking them to walk through their proforma assumptions with you. Often times, it is during these conversations that you can suss out great intel or learn quickly if they are blowing smoke with their projections. Point being, that talking with the broker will be an important and critical step to dialing in your operating assumptions.
As a side note worth mentioning: brokers will be one of your most important contacts in the industry for both opportunities and data. Learn who the reputable brokers are and get to know many of them. Take them out for drinks or lunch! When you develop relationships and start doing business with them, they can be an incredible resource to lean on and many are happy to provide intel whether you are working with them on a particular deal or not.
Historical Op Ex From Similar Asset Types
Historical Operating Statements from comparable deals will be excellent benchmarks to help you assess op ex for the property in question.
Third Party Operating Company
A local property management company that works with similar assets in the same market would be a fantastic resource to vet through the historical operating statements to see if they notice anything that is out of whack or areas of opportunity. However, unless there is an existing relationship, you are potentially offering them an opportunity to manage the property if you purchase the building, or if they already do business with you; it may be difficult to get them to provide input.
Let me know if you have further questions on this.
Thanks again!
MB
Q3-a – Lastly, on TI, instead of allowing for $65/RSF, if we are able to lease it out before the project construction even starts, can’t we save on that by fitting-out the space during initial construction?
Having a tenant on board early enough, although not common unless it is a build-to-suit or you are in an extremely supply constrained market, would be ideal as you can coordinate with them on base building components and make design decisions that would better accommodate the tenant’s needs and TI desires. It would help significantly to be able to coordinate this on the front end rather than post design or construction, which is more the norm. Keep in mind that ‘before project construction even starts’ could still be too late if the design process is complete or close to it and the project has been bid out. It is extremely time intensive and costly to change a design and all the design drawings if the A&E team is past the schematic design phase, which can be months before construction. Even in the SD phase any changes could be extremely costly.
However, to your main question about saving on TI’s, perhaps a good way to think about TI components is as items that are put in place on top of the base building that are unique to the tenant. So despite us possibly having the ability to better accommodate the tenant by making some modifications to the base building, the TI’s are still considered additional scope items. With all this being said, TI’s are first and foremost a negotiation, so nothing is set in stone on this topic. As an example, you could in fact come to an agreement that any modifications that increases costs to the base building come out of the TI allowance. Additionally, what specifically constitutes TI and Base Building items when having a tenant on early enough in the design process is an important topic to get very specific about. For example, is the elevator finish a TI or base building cost?
Q3-b – Also TI happens in Month 39, which is the same month the tenant’s lease starts. In reality, wouldn’t there be a sort of construction downtime, I can’t imagine a tenant agreeing to pay for a space when it’s not quite ready to move in?
Correct. In our model, we give our tenant three months of free rent, which is two extra months after TI’s are complete. However, this is a point of negotiation in a lease as well. As an example, let’s say a TI build out for a tenant is unusually extravagant and will take longer than a typical TI build out, a Tenant may have to eat some rent before finishing the TI build out, especially if there are other tenants in the market that will pay the same rent and start earlier. The market, tenant, building quality, location, lease term, etc., all play a part in the larger context of rent payment and timing. All these things impact each other.
Josh, thanks again for these questions and I hope I’ve answered them sufficiently.
All the best,
Michael
Q2-a – Which brings me to my next concern of construction. In the budget breakdown, construction costs account for almost 60% of the whole project, this seems like a lot of risk riding on how successful the construction stays on time and on budget, and only allowing a 5% contingency (later reduced to 3% to make us competitve) sounds even more scary. In reality, do you have a consultant advising you on the construction costs and schedule for the model, or is it perhaps in-house experience from the partners? I know we just assumed $300/GSF hard costs, but every project is unique and has it’s complexities I’m sure.
Another great question. Some groups may have in-house expertise, but even with that, a smart and cost-conscious approach would be to meet and network with local General Contractors (“GCs”) in the market. If the deal you are looking at is of sincere interest, reaching out to grab a coffee or lunch to both connect and discuss the deal with a few general contractors is a great idea. It is ok to ask them to put together a preliminary budget (at no cost) as this is also a way for them to potentially secure work and they may have already done it for another interested party. Getting pricing from a few local and experienced GCs can help you hone in on a good budget estimate. However, it is not advisable to approach the GC community to ask for a preliminary budget or cost info if you are not seriously pursuing a deal or considering them for the project as you need to be considerate of their time and do not want to sour an important relationship.
To further discuss your question on contingency, the construction hard and soft cost numbers you get should always have further contingency line items baked in as well, most commonly for cost escalation and estimating inaccuracy. Review any detailed construction budget for these line items to ensure what types of contingency and how much is included. So while you have an overall project contingency line item, there should always be more hidden within your construction costs.
Contingency in a budget is always a balancing act. Too much and it can make you non competitive in pursuing a project, too little and you are at risk of busting your budget.
Q2 – b – Is there something like error insurance that developers can protect themselves with?
In terms of risk protection, i.e. insurance, there are many layers.
– Partnership Provisions. There are usually detailed clauses in the partnership docs that outline who’s responsible amongst the partnership for cost overruns at the partnership level.– Construction Bond. Developers will require GCs to put up a construction bond that guarantees that the GC can do the job as outlined and will compensate the owner for major defects or large GC caused disruptions. (should be included in construction budget)
– Liquidated Damages. There will usually be a section in the GC contract on Liquidated Damages detailing out the day for day cost for GC caused delay in construction completion that is owed to the owner from the GC.
– Insurance. There are a few common insurance policies to consider as well. Subguard insurance, liability insurance, and builder’s risk. (should be included in construction budget)
Joshua,
Thank you for such great questions! I am going to answer them in multiple responses as they will be lengthy.
Q1 – I noticed that we stopped forecasting taxes after Jan 2022, because our assumption is that we sell the property in Dec 2022. Does this mean that we are essentially offloading the tax liability for Year 2022 on to the the buyer, and if so, wouldn’t they factor that into negotiating the sale price down? Also if there’s even a small bump in our construction schedule and we don’t get to sell the property at the end of 2022, wouldn’t we be liable for paying taxes on the property for the year 2022?
This is a great question and is a nuanced topic per deal that could result in many different outcomes of who owes who what.
If the scenario was indeed as you described, you could customize this model to account for the tax as a credit to the buyer, or a decrease in the purchase price. However, another possible scenario here is that the seller did pay the tax obligations for 2022 and the buyer actually owes the equivalent of 1/12 of the taxes to the seller assuming they take ownership on 12/1/2022.
How taxes are determined and trued up between buyer and seller vary enough everywhere that I wanted to provide a general foundation for how to think about modeling it out, rather than discussing the numerous and nuanced ways one may have to account for this in each deal. However, it is an important topic and since you are asking about it, let’s further elaborate on it, below are some further considerations:
Reassessed value at occupancy or sale. Once the building is occupied by the tenant, it is not uncommon for the tax assessor to reassess the property using a different methodology known as the income approach rather than the cost approach, and taxes would be owed based on this new assessed value, which is usually a substantially higher value. Furthermore, the sale itself could trigger another appraisal and the property could be reassessed using a sales comparison approach to determine the assessed value.
The tax fiscal year may be different than the calendar year. A tax fiscal year for a certain municipality may not be a calendar year, so payments and determining who owes what will alter based on the fiscal year rather than the calendar year.
Payments may partially cover periods that span to both the future and the past. For example, you may have a fiscal tax year from July 1 to June 30 and pay two installments. The first installment could be due in October 1, while the second payment would be due on April 1. The October payment covers July through December, while the April payment covers January through June. So in this example, if a buyer purchases on November 1, he or she would owe the seller for two months (seller already paid for November and December) and if the buyer instead purchases in February 1, the seller would owe the buyer for one month (buyer will pay in April for January through June).
So with all of these nuances, you can see that the ultimate answer here to your question is ‘it depends’ and that you will almost certainly need to dial in your model for your specific project.
With that said, however, in the early stages of underwriting a development deal, in most cases, you can think of property taxes as a budget line item that won’t be a major driver of the valuation and so as long as you are roughly capturing the cost, you should be fine. As the project evolves, however, and you are ‘sharpening your pencil’ on the deal, this could be an important component to refine and get right.
