Forum Replies Created
-
Posts
-
Hi Scarlett,
Thanks for bringing this up. Michael covers these concepts in more depth in his development modeling lectures, but allow me to apply what he teaches to the exercise you are referring to.
First for those reading this thread who aren’t sure of which model Scarlett is referring to. A few years back, I grabbed a Technical Interview Exam that someone had posted to Wall Street Oasis, and recorded myself completing the exam (i.e. Watch Me Build). You can find that ‘Watch Me Build’ here:
https://www.adventuresincre.com/watch-model-real-estate-technical-interview-test/
Now to Scarlett’s Question: How do we explain the difference in equity amounts (cell C26 and D84) being a $400k variance?
The equity amount in cell C26 together with the loan amount in C25 make up the total amount (i.e. Sources) required to fund the Total Project Cost (i.e. Total Uses or Total Capitalized Costs). The equity amount in cell D84 is the total equity required over the entire hold period, inclusive of the equity during construction as well as any equity required to fund shortfalls during operations. Or in other words, the additional ~$400k in operating shortfall that is funded by equity is NOT included in the project budget. This is no different than if there was an operating shortfall much later in the hold period that the partners would have to fund from equity.
Now you’re probably wondering, why didn’t I include the shortfall during lease-up in the development budget (i.e. capitalized the shortfall). The short answer is the exam was meant to be taken as quickly as possible, and so I chose to make it simple and not include an Operating Shortfall reserve; had I added the shortfall to the development budget it would have added a layer of complexity that I didn’t think was necessary.
Doing it this way isn’t necessarily wrong, so long as the exam didn’t specify how operating shortfall should be handled. But admittedly in the real world, it’s more common than not to include operating shortfall from the initial lease-up in the development budget. It just adds complexity to include the operating shortfall in the development budget and so I chose to avoid that complexity.
If you’d like to include the operating shortfall in the development budget such that a) the loan funds the shortfall and b) the equity amount in cell C26 is the same as the total equity amount in cell C84, watch the following tutorial:
– Click here to download the template file to follow along
– Click here to download the completed file from the tutorialThanks again for the great question!
Spencer
Peter,
Thanks for your patience on this one and I really apologize for how slow I was to respond to this comment.
In terms of why I use the formula I do to get the start date rather than MOD() function. The honest truth is, the first time I wrote this formula I asked myself: “what logic could I use to accomplish this task?”. At the time, and this is still the case, I’ve only used the MOD() function a handful of times and so it’s not one that automatically comes to mind when I think through a logic statement. So the formula I used is what resulted.
What I’ve found over the years is that I’ll run into a modeling challenge, come up with a solution myself, and then view someone else’s model at a later point and find a more eloquent solution to the same challenge. Sounds like this is a perfect example of that phenomenon!
So thank you for the suggestion. I’ll likely use the MOD() function the next time I perform this task in the real world. Just goes to show, you learn something new everyday!
Spencer
Hi Peter,
I’m guessing you’re referring to question 1 on the quiz: “Using the materials provided and the method taught in lecture 1.5, approximately what is the annual leasing cost reserve for this industrial property?”
So you’re correct in how to calculate leasing commission, but you’re missing the other half of the leasing cost element: tenant improvements.
Let me know if I’m looking at the wrong quiz question.
Spencer
Hi Jack,
This was admittedly a tough question and the course didn’t directly address it. Getting the question right required a bit of intuition and for you to have calculated the Operating Expense Ratio from question 9 correctly.
So to the quiz question. It asked, “Based on what you have learned thus far in the Accelerator, is this property stabilized or unstabilized?”
In terms of how to know, you need only look to the the Operating Expense Ratio. YTD, this office building had 2,982,330 in Total Income compared to 2,551,989 in operating expenses. So before accounting for capital expenditures and debt service, 86% of income is going just to cover operating expenses.
Now I recognize that I didn’t give a range for a typical Operating Expense Ratio for an office building in this submarket (as an FYI, Operating Expense Ratios for office buildings in the U.S. can range from 20% – 50%+). But without even knowing what a typical operating expense ratio for an office building in this area, if 86% of income is going to cover operating expenses we can be sure that the property is not fully stabilized since there won’t be sufficient cash flow left over to even cover capital expenditures, let alone debt service.
To your suggestion about Michael and I creating a section that discusses answers to quiz questions. That’s a great suggestion. I can see that a tool like that would be helpful, and so I’ll discuss with Michael how to make that happen.
Spencer
P.S. After looking further at this question, I’ve revised it slightly to remove one small inconsistency. It had originally clarified that unstabilized meant not fully occupied and stabilized meant fully occupied. That’s not always the case. There are situations where you can have an unstabilized, but fully occupied building. For instance, a single tenant building where the tenant is currently in an extended free rent period would technically be considered unstabilized, since the property is not yet performing to its market potential.
Hi Ben,
A really interesting question, and admittedly I’m not an expert in fund-level accounting. I live in the world of future forecast analysis, while guys on the accounting side are much more intimately involved in situations like the one you describe. With that said, I’ve had some exposure to calculating actual investor returns on real estate fund investments, so I’ll offer my two cents with the caveat that I can’t speak to whether my limited experience is representative of industry standard or not.
First to your question of whether what the Holdco does with the investor’s capital is relevant to the investor’s IRR. The answer is no, it does not matter. All that matters in calculating the investor’s IRR is what did the investor contribute and when, and what was distributed to the investor and when.
Next, in terms of how to calculate the investor’s IRR, I’ve seen this done two ways. The first method is to book the investor cash flow, whether a contribution or a distribution, in the actual day the cash flow occurred. This would be the most accurate, since you would be calculating the IRR based on the actual day the investor cash flow occured. In your Excel IRR calculation, you would than have custom periods with each period representing the actual day of the investor cash flow.
So for instance in Excel, you would have a date header row and a investor cash flow row. Starting from the first contribution, you would create a period for that cash flow, enter the date, and enter the contribution as a negative value. The next period would be the date of the next cash flow, if a distribution the cash flow would be positive, if a contribution it would be a negative. You would continue adding periods for each subsequent cash flow until all investor cash flows, with their cooresponding dates, have been logged.
Next, you would calculate the IRR using Excel’s XIRR function, using the date header row and the cash flow row.
Let me know if this first concept isn’t clear and I’ll put together a quick Excel example for you.
The second method I’ve seen makes for simpler accounting but is slightly less accurate. I’ve seen firms book all investor cash flows as of the last day of the month. Then, regardless of the day of the month when the investor cash flow occurred, it would be assumed to have occurred on the last day. This has the benefit of simplifying the process, and there are probably some other accounting reasons/benefits to doing it this way but I’m not qualified to really comment on that.
The IRR calculation using this method would also be calculated using the XIRR function with the dates and cash flows, but the dates would be consistent from one month to the next rather than irregular. The difference in IRR between the two methods would be nominal, but the first method does have the benefit of being more accurate.
Thanks again for the great question!
Spencer
Great to hear from you Josh!
First, allow me to make an important point and then I’ll answer your questions. How you model the partnership waterfall of a given deal very much depends on the wording of the partnership agreement in that deal. What makes partnership-level modeling so difficult, is that every structure is different.
Generally speaking, you can build a property-level model and it will handle most of the situations you run into. But I could show you 10 partnership structures, and all 10 might have enough nuance between them that it would require building 10 modules (or at least modifying them) to handle each structure individually. Thus, this course (and the subsequent waterfall modeling lectures in the Advanced Concept course), set out to teach you to think about waterfall modeling in terms of the timing of the contributions by and distributions to the partners. If you can internalize and master that framework, with enough practice you’ll master partnership-level modeling.
So now to your questions.
How to calculate the LP’s required rate of return.
This is the XIRR vs IRR/12 conundrum in modeling partnership-level waterfalls. Which methodology to use really depends on the language of the partnership agreement. I’ve had several GP’s email me via A.CRE over the years complaining that they’d just realized the partnership agreement they signed calls for use of Excel’s IRR/12 function to calculate the LP’s preferred return rather than XIRR. Using IRR/12 is to the LP’s benefit, as it requires distributing more cash flow to the LP to hit each hurdle due to annual compounding of IRR compared to the monthly compounding of XIRR. These GPs made the mistake of not reading and/or fully understanding the ramifications of the XIRR vs IRR/12 negotiation.
With that said, generally speaking if the partnership agreement calls for monthly compounding and assuming the distributions are monthly, you will likely be using the XIRR function. And if you’re using the XIRR function with monthly periods, you’ll need to convert the annual rate to a monthly rate to arrive at the appropriate annual return.
Simply taking the annual rate and dividing it by 12, when using XIRR, will result in an actual annual return greater than the hurdle rate. This is because of the monthly compounding of the cash flow.
You can see what I mean in this quick example I put together (click here to download Excel file). In the top example, I simply take the hurdle rate and divide it by 12 to arrive at a monthly rate. The resulting annual return is higher than the hurdle rate. In the bottom example, I convert the annual rate to a monthly rate using the (1+hurdle rate)^(1/12)-1 logic. The result is an annual return that is roughly equal to the hurdle rate.
An important side comment. Many partnership agreements will expressly call out how to calculate the preferred return, including calling out the XIRR() specifically. However, and this is the reason I said “generally speaking” above, the XIRR function is not always the methodology called out in the partnership agreement. So it’s important to read the agreement before finalizing your partnership cash flow module.
If somehow, there’s still cash leftover, do we then pay down the LP capital account in each hurdle until zero’d, before distributing any remaining cash to the GP? Or does the GP get his share too in each hurdle, and the money is distributed in each hurdle until there’s none left?
It depends on the wording of the partnership agreement. In the structure we modeled, return of capital to the GP/LP is made parri passu (i.e. at the same time). So in your example above, where there’s insufficient cash flow to return all capital, whatever excess cash flow is remaining would be distributed pro rata based on the partners’ respective ownership share.
Some structures however call for 100% of excess cash flow to first go to the LP until the LP’s capital account is zero and the LP has achieved an X preferred return, after which 100% of the excess cash flow is distributed to the GP until the GP’s capital account is zero and has achieved an X preferred return (i.e. the GP catch up).
Note that I show you a couple ways to model a GP catch up in one of the lectures in the Advanced Concepts in Real Estate Financial Modeling course.
Yes, this type of lease provision is most common in office and retail leases, although it can be seen in any long-term (i.e. greater than one year) lease. And it’s actually quite common in multi-tenant buildings.
Generally speaking, how it works in a multi-tenant building is that each tenant agrees to reimburse its pro rata share (based on occupied area) above its Expense Stop. So if a tenant occupies 10,000 SF or 10% of a building and if the building had $1,000,000 of total reimbursable operating expenses in the year, $100,000 ($10/SF) would correspond to that tenant. Further imagine the tenant had an $8/SF ($80,000) Expense Stop. The tenant would be responsible to reimburse the landlord for $10-8/SF = $2/SF x 10,000 SF or $20,000 of the tenant’s pro rata share of operating expenses.
The existence of these types of leases is one of the main reasons ARGUS has become so popular. Since each tenant generally starts its lease in a different year, and since the Expense Stop is often equal to the operating expenses in that initial year (Base Year), many tenants will have a different Expense Stop. This makes modeling reimbursements quite complex, and thus necessitates non-Excel solutions such as ARGUS.
We cover this concept in more detail in course 5. Introduction to Modeling Leases.
Hey Peter – thanks for the heads up on the typo. I’ve corrected Q7, answer 2. It should now read: “Building amenities always lose money, but are necessary to keep tenants happy.” That’s NOT the correct answer, by the way! Some amenities do turn a profit.
To your question about the Expense Stop. You can find a discussion on the concept in lecture 1.4 of this course.
But think of the Expense Stop as a way for the landlord to limit its risk to cost inflation. The landlord commits to paying for expenses up to a certain point (i.e. the Expense Stop), and above that point the tenant is responsible.
So for example, imagine a scenario where the landlord signs a gross lease at $24/SF with an expense stop of $8/SF. In this scenario, the landlord would expect to take in $24/SF in rent and spend up to $8/SF in operating expenses – or net $16/SF. If operating expenses at the property exceed $8/SF in any year, the tenant would be responsible to pay (or reimburse the landlord) for those expenses above $8/SF (i.e the Expense Stop).
Hope that helps!
Spencer
Hi Ryan,
For the academic answer, I’ll point you to a white paper that describes the difference between the discount rate and cap rate.
‘Real Estate Capitalization Rate Interpretations through the Cycle‘
The key part comes on page 12 – ‘Real Estate Cap Rate Defined’. You’ll notice that Dr. Corgel defines the cap rate (R) as the discount rate (r) minus the assumed growth rate (g).
So to answer your question, ” How come a CAP rate can be different than the discount rate used in DCF valuation of RE property valuation?”
The answer is growth. Without growth, the discount rate is equal to the going-in cap rate.
I’ve built a quick DCF to illustrate this point (click here to download the Excel file).
Notice in the attached example, the first DCF example assumes no growth in operating income nor in value growth. As a result, the internal rate of return (i.e. the discount rate at which NPV = 0) is equal to the going-in cap rate. However, when you bring growth into the equation (the 2nd DCF example) the internal rate of return (i.e. the discount rate at which NPV = 0) is higher than the going-in cap rate.
Let me know if you have any questions.
Spencer
This is an interesting observation and good follow up question. Let me first make a point that I made in a separate thread earlier this week but that bears repeating here.
It’s important to make the distinction between accounting (i.e. reporting the past) and finance (i.e. modeling the future). In real estate financial modeling, we’re modeling the future. Sure we might look to the past to make assumptions about the future, but largely speaking our job is to make educated predictions about what might happen in the future rather than simply reporting what did happen in the past. And we’re compensated quite well for developing that skill.
A T12 income statement is an accounting document. It reports what happened in the last 12 months.
So to your question. Most often the gross potential rent in a T12 is simply the actual asking rent in each of those reported T12 months. The actual rent per unit is multiplied by the total number of units to arrive at a gross potential rent. The reason for the loss-to-lease line is that in any given month, a good share of the rent roll is occupied at rates that are less than (or sometimes more than) the asking rent in that month.
However, to predict a gross potential rent going forward or to estimate a current gross potential rent for a property not yet stabilized, we make assumptions. And those assumptions must be supported. We use support such as past performance and existing performance at the subject and at comparable properties, market forecasts created by professional prognosticators, micro and macro trends underway, location specifics, and a host of other data and arguments. That data, whether past, present, or future most often comes from the sources I listed above.
A long-winded answer to a basic question, I admit! But it was an opportunity to help you recognize the difference between accounting tasks (e.g. T12) and finance tasks (e.g. DCF).
Great questions.
Sources for multifamily rent data to support underwriting assumptions.
If the property is stabilized and has operating history, there’s no better place to look for rent data than in the actual rent roll at the subject property. For instance, at a 300-unit complex with 95% occupancy, you have 285 in-place rent comps that give you a very good idea about how much you could lease up those 15 vacant units. Especially new leases signed at the property within the last three months.
However, if you’re modeling potential rent not previously achieved at the property such as is the case with a value-add deal or a ground-up development, you’ll need to look elsewhere for rent comps. The best source is always your own personal database. If you’re regularly in the market, you’ll have visibility to the actual rent rolls for competing properties. That is raw source data, far superior than any of the data providers out there. The key is to retain and organize that data as you get it so you can use it when you need it.
Besides in-house data, the brokerage community is probably your next best resource. Assuming you trust your broker contacts, they too will have direct knowledge of and access to actual data from competing properties. And so long as you have a good relationship with those brokers, they’re usually quite happy to share information; even if they’re not directly involved in that particular deal.
Next in order of quality would be the data aggregators. These would be CoStar (i.e. owner of Apartments.com, Loopnet.com, etc), Axiometrics, REIS, and a few smaller data providers specific to certain markets. The quality of this data depends on the source and method for collecting the data. In my experience this data is used as additional support, not primary support, as you can usually cherry pick the data to fit your narrative.
Does the total leasing cost reserve fluctuate year over year?
Generally yes. There’s an expectation that leasing costs will grow over time, whether due to expense inflation (i.e. TI costs) and/or rent growth (i.e leasing commissions).
Hi Wesley,
First off, congrats on getting this far. It is really quite the accomplishment and you should be proud of yourself! Now to your question:
“Why is it being done this way?”
First, keep in mind that the periods in this model are end of month periods. Meaning, ‘Month 1’ encompasses everything that happened from day 1 through day 30 (or 28, 29, or 31) of that month. This is key to understanding the methodology that I used here.
Another thing to keep in mind is that we’re assuming that a renovation takes up to, but not more than one month to complete. In practice, a unit renovation generally takes fewer than a couple of weeks to complete.
So in this particular model, month 1 begins Jan 1 and ends Jan 31. The ‘Renovations’ line is an input where the user enters the number of units to be renovated during that month. So during month 1 (from Jan 1 to Jan 31st) it is assumed that we start and finish the remodel of 10 units, such that by the end of month 1 we have 10 units renovated (or 4.167%) of the 240 total units.
The ‘Renovations’ assumption feeds into the Investment Cash Flow section to dynamically model renovation costs. It also feeds into the ‘Downtime Vacancy’ line and assumes that any units renovated in a given month yield $0 in income.
Thus, as of the end of month 24, 100% of the units have been renovated. However, because the last 10 units were renovated in month 24 and thus unavailable to be rented, the model includes 17,496 in ‘Downtime Vacancy’ that month. It isn’t until month 25, when all units have been renovated and full month’s worth of rent collected for all renovated units that we can call the building “stabilized.”
Thanks for the great question! Happy to answer any follow ups.
Spencer
That’s a good observation and an oversight on my part. The Operating Cash Flow section would need to be dynamic to the reduction in space owned. So in the quick and dirty example I gave, cash flow from operation would be reduced in months 23 and 24 consistent with the fact that you own less space in those months. I’ve updated the file to reflect that fact (find updated link in my initial response).
Also, I performed my analysis on an unlevered basis but were we to perform this analysis on a levered basis, there would be some adjustments to the debt module as well. For instance, the lender by default wouldn’t likely allow you to pay down only a portion of the loan. So that right would have to be negotiated. And it will cost you more in the form of a higher rate.
Furthermore, the pay down of the loan would need to account for the fact that there may be prepayment penalties (depending on the amount of prepay flex negotiated) and the amount of the pay down wont’ be exactly proportionate with the amount of space being sold. So for instance, if you sell 33% of the building in one month, the lender will likely require you to pay down a higher proportionate amount (e.g. 110% – 125% of the allocated loan amount or ~36% – 41% of the loan amount).
This is a great (and fun) question – thanks for asking it! Rather than respond in writing, I thought I’d build a quick Excel model and record a short video to provide an answer.
Let me know if you have any follow up questions.
Click here to download the Excel file used in this video
Absolutely. One of the reasons for this exercise, beyond getting familiarized with retail income statements, is to expose you to how different one property’s income statement can be from the next. And since generally you’re working with a standardized summary income statement in your firm’s model, you’re often making judgement calls about how roll up a property’s line items into your own.
So while the task may same tedious and somewhat arbitrary, depending on the role you have in real estate this very likely is a task that you’ll perform everyday.
But even if you’re never required to perform this specific task, the concept of using SUMIF() in Excel to rollup values is essential to real estate financial modeling. At some point in your career, you will use it to roll up monthly cash flows to annual, roll up detailed construction budget line items to summary line items, roll up detailed rent roll line items to summary, etc.
