Forum Replies Created
-
Posts
-
Hi Maria,
The download link for the file is shown on the summary screen of the quiz. Unfortunately, if you click ‘Start’ the download link disappears and the only way to find it again is to end the quiz and start over.
To avoid having to start the quiz over again, here is the direct link to the file:
Good luck!
Spencer
Hey Chetan – great question!
What is the difference between reimbursable and non-reimbursable expenses?
Reimbursable operating expenses are those expenses that the landlord expects to be able to recover from the tenant(s). Non-reimbursable operating expenses are those expenses that the landlord does not expect to be able to recover. So in a fully occupied building where the property is well managed, Reimbursement Income should be approximately equal to Recoverable Expenses.
Now what’s interesting about the example detailed income statement that we used in this course is that the landlord was unable to recover most of the “recoverable expenses”. The detailed income statement we used is from an actual property – so this scenario does happen.
In this particular case, the reason that the landlord was unable to recover most of the recoverable expenses is that the building isn’t fully occupied. So there are recoverable expenses, but no tenant(s) to reimburse for those expenses. But once the building is leased back up to full occupancy, the expectation is that the ‘Reimbursement Income’ will match the Recoverable Expenses.
Let me know if you have any follow up questions!
Spencer
I’ve started playing around with XLOOKUP(). I find it intuitive and effective.
The problem with any new Excel function though is compatibility. If you use a new Excel function in your model and then plan to share your model at any point (such as to clients, investors, lenders, etc), you run the risk that the person you share the model with is using an older version of Excel.
We run into this with our Library of Excel models on A.CRE. Our models are largely only compatible with Excel 2013 and later since we use functionality in our models that was not available prior to the 2013 version. We do our best to communicate this fact to our readers, but we still regularly get messages from people saying “the file is corrupt”. When in reality, it’s an incompatibility issue.
So I recommend learning new functions such as XLOOLUP() and even using them when your stakeholders are all using the latest version of Excel. But if you plan to share the file more broadly, I recommend adding “backward compatibility” by sticking to the legacy functions.
Thanks for the great question!
Hi,
Great question! The broader answer to this question is that it depends on the wording of the JV agreement. But in this case, either way will work. Allow me to explain why.
Unpaid Preferred Return Accrues
If you recall, the partnership agreement reads as follows: “Partners to be paid an annual 8% preferred return on the current balance of each partner’s capital account, distributed equally per each partner’s ownership share; any unpaid preferred return to accrue to the partner’s capital account”.
The key part is “any unpaid preferred return to accrue to the partner’s capital account.” So in periods when there is insufficient operating cash flow to pay the full required preferred return, the capital account of the partner grows. At the capital event, that shortfall is paid by capital event funds prior to any capital event funds going towards promote.
Modeling this in Practice
The practical impact of this, from a modeling perspective, is that we can model contributions and distributions from either the ‘Before Tax Cash Flow (Capitalized Expenses and Operations Only)’ line or the ‘Net Property-Level Cash Flow (After Debt but Before Taxes)’ line and the result will be identical. Allow me to prove this out.
Attached find a modified version of the ‘Cash-on-Cash Hurdle’ exercise file from the course. Highlighted in yellow you’ll find the modifications as follows:
1) Row 46 (LP Distributions) and row 53 (Sponsor Distributions) now pull from row 37 (Net Property-Level Cash Flow) rather than row 35 (Before Tax Cash Flow).
2) Cell R90 on the DCF tab set to 0, to assume a scenario where there is 0 operating cash flow in year 10Click here to download the modified version
Click here to download the original, but with 0 operating cash flow in year 10If we then take the original method, and set operating cash flow to 0 in year 10, we get the following returns for the LP and Sponsor:
• LP Profit: 22,186,612
• LP IRR 11.76%• Sponsor Profit: 10,605,622
• Sponsor IRR: 28.58%In the modified method with the above changes instituted, we get the following returns for the LP and Sponsor:
• LP Profit: 22,186,612
• LP IRR 11.76%• Sponsor Profit: 10,605,622
• Sponsor IRR: 28.58%Digging into the cash flows, what we see is that the unpaid preferred return in the original version is paid by capital event funds as part of the preferred return hurdle. Whereas in the modified version, the unpaid preferred return is paid as part of the ‘Net Cash Flow at Capital Event’ hurdle. But the end result is identical.
For Consistency the Modified Method is Better
With all of that said, and the reason I spent so much time talking through this, is that the modified method you suggest is better. That’s because, as you rightly point out, the Preferred Return hurdle is meant to model cash flow during operation. Thus, it makes sense not to pull capital event cash flows into that tier, but rather pull them into the ‘Capital Event’ tier and make up for preferred return shortfall there.
Now again, this is largely semantics since the end result is the same. But for consistency, the modified method is better.
Thanks again for bringing this point up!
Spencer
Glad it made sense.
And you make a very good point about understanding the concessions before determining whether they’re persistence. The best way to determine this is to look at the history at the property as well as comparable properties in the market.
If the subject property has had concessions through multiple generations of tenants and/or the competitive set of comparable properties includes concessions that have been persistence across multiple generations of tenants, you can safely assume that concessions in this market are persistent. Or at least, the effective rent net of those concessions is persistent.
Hi Nate,
Thanks for the great question! In fact, I was wondering if someone was going to notice how we treat concessions differently in the Direct Cap valuation vs the DCF valuation of The Foles.
To you question: “Considering the broker phases these out in two years, these would be included in this Direct Cap model specifically, or if they are to be included if there is a general rule?”
First for those who don’t recall. In The Foles Direct Cap pro forma, our boss’ guidance was to underwrite concessions at 2% of gross potential revenue.
However in The Foles DCF, our boss’ guidance was to underwrite concessions at 2% of gross potential revenue in year 1, 1% in year two, and 0% thereafter.
The two are actually inconsistent. And why? Well remember the first rule of the direct cap pro forma: only cap cash flows that are stable and perpetual.. Or in other words, if a cash flow line isn’t expected to persist perpetually, than that cash flow line shouldn’t be included in the direct cap pro forma.
By burning off the concessions in The Foles DCF example, our boss is communicating to us that he believes the concessions are not durable. Nevertheless, he still asked us to include the concessions in the direct cap pro forma in spite of that fact. Why?
It really comes down to how aggressive one gets in making underwriting assumptions. Excluding concessions from the direct cap pro forma would lead to a higher valuation, which would be more aggressive underwriting than modeling something for concessions. And considering that concessions are common in many markets and often persist, it’s not unusual to underwrite concessions into a direct cap pro forma.
Likewise, the direct cap pro forma in this case drove our offer price. Thus, we might have overpaid had we completely excluded concessions and then it turned out that some concessions persisted long term. So to be conservative, especially early in the analysis process, our boss chose to underwrite concessions into the direct cap pro forma.
Is there a general rule of thumb for when to include concessions?
When considering whether to include concessions or not in your underwriting, ask yourself: are these concessions likely to continue into perpetuity? If the answer is definitively no, than more than likely you shouldn’t underwrite the concessions. If the answer is maybe or yes, than you should most definitely underwrite the concessions.
Thanks again for the great question! Happy to answer any follow ups you might have.
Spencer
Hi Ryan,
Great question on a concept that often trips people up. Let me first quote a portion of lecture 2.4 of this course that addresses this concept:
“For purposes of performing hold/sell analysis of an owned asset, the asset’s current market value less selling costs is entered as a sole Investment Cash Flow in time zero of the analysis.
“Now why use market value rather than actual cost basis you might ask? In the case of Lakefront Industrial I, we’re assessing two possible scenarios. Scenario one, we hold the asset for an additional 10 years. Scenario two, we sell the asset and use the proceeds to acquire a different property.
“So what’s the cost of choosing scenario one over scenario two? The cost is not our current basis, but rather is what we miss out on (i.e. the opportunity cost) by not going with scenario two. Or in other words, the net proceeds we would have taken in from selling the property had we gone with scenario two.”
Now to your question: why we would use the market value as the year 0 investment amount when in reality we would not be making this investment (since we already own the property)?
In hold/sell analysis, choosing the ‘hold’ scenario is making an investment. The investment we make is that we forego the net proceeds we’d earn from selling the property (i.e. the opportunity cost), and trade that for some period of operating cash flows plus a reversion cash flow at the end of the analysis. Thus the investment we make (i.e. opportunity cost) is represented by the market value of the property (less selling costs) in time zero of the hold asset’s DCF.
Happy to answer follow-up questions!
Spencer
Nick,
Yes, the Advanced Concepts in Real Estate Financial Modeling course has a module on doing sensitivity analysis in Excel:
Click here to view that module
Of course, the module teaches three methods to using Excel’s data table feature to perform scenario analysis. So it may be somewhat of a review for you. But I’d guess the “Use scenarios to effectively create unlimited variable, unlimited output data tables” part of the lecture will be new. That method is by far the most robust method I know of in Excel.
In terms of how others model deal sensitivity, what tools they use and how they organize the sensitivity analysis within the model. In my experience, sensitivity analysis is either done via Data Tables or via duplicate models with varying inputs for the base case, upside case, and downside case.
Check out the Scenario Analysis module and let me know if you have any other questions!
Spencer
First to your question. It really is a matter of preference. I do think it’s important to understand the difference between effective and market rent in each period. And so, this generally means breaking out concessions, non-revenue units, loss-to-lease, etc. But how valuable this becomes to your analysis depends on the complexity of the deal and the stage of underwriting your at.
Adding specificity is more important when you’re executing on a value add strategy, since understanding where your rents are relative to market or knowing what % of your units have rolled to market helps you measure success. A value-add strategy, like development, has more margin for error and so adding specificity will help you make more informed decisions.
As an example, in my Value-Add Apartment Acquisition model, I use a ‘% Rolled to Market’ line rather than a loss-to-lease line. But than I include separate income and expense assumptions for pre-renovation and post-renovation units. Additionally, I include ‘Concessions’ and ‘Non-Revenue Units’ lines to arrive at an effective rent. At the BOE stage, this can feel like overkill. But as the deal becomes more real and you start to sharpen your pencil, it’s nice to have a model that allows you to really dig into the details.
Hi Nick,
Personally I’ve never come across a situation where the analysis of a larger residential building (i.e. 100+ units) would be appreciably benefited by detailing out each and every residential unit. On smaller buildings (e.g. 4 – 10 units) however, this is quite common.
With that said, you’ll often run into situations with larger buildings where creating a very detailed unit mix is important to the analysis. Take a high-rise apartment building as an example. There’s a big difference between what a particular floor plan rents for on a lower floor versus what that same floor plan rents for on an upper floor. Plus, many cities require some proportion of urban residential buildings to have a certain percentage of units as “affordable.”
Thus, you might have three floor plans that are physically (i.e. same dimensions, size, finishes, etc) the same but have very different economics. As a result, you’d want to detail out each of those three in your unit mix. Imagine you had 12 floor plan types, but detailed out each between market and affordable and between lower floor, mid-floor, or upper floor. You could end up with as many as 72 unit types (12 x 2 x 3) in your unit mix table.
Thanks for the great question! Happy to answer any follow up questions you might have.
Spencer
– Note that this reply has been moved to a new topic –
Hi Juan,
This is a really interesting modeling challenge – we’ll likely have to go back and forth a bit to accomplish it.
First to answer your question, if the partners are contributing at different times, due to time value of money reasons I can’t think of how the IRR and EMx would be the same. Because the partners are contributing at different times, the amount of distributions to each partner would have to be different in order to hit the same IRR, resulting in differing EMx. Or in reverse, to distribute in such a way to hit the same EMx, the IRR would be different. But without the contributions and distributions being pro rata and pari passu, it would be impossible to match EMx and IRR.
Just thinking out loud, one solution would be to begin analysis when the latter partner first contributes. The former partner would have some accrued contributions (and pref) that would hit at analysis start, and then the partners would contribute pro rata and pari passu from that point, resulting in the same IRR and EMx from that analysis start date.
Perhaps if you want to send me the exact partnership terms that you’re trying to model, I could offer more feedback. If you want to keep the details confidential, send the details to [email protected].
Best,
Spencer
Hi Nick,
If you recall from course 1. The Direct Cap Method to Valuing Real Estate, the formula we used for calculating Loss-to-Lease was:
Loss-to-Lease = –([Average Market Monthly Rent Per Unit – Average Actual Monthly Rent Per Unit] x Total Occupied Units x 12 Months).
The key part here is ‘Total Occupied Units’. Loss-to-lease is meant to track the difference between actual in-place income, and the potential income were those in-place units leased at market rate. Or in other words, how much is lost (or gained) on occupied units that are leased below (or above) market.
So in this case, the total market rent of $394,440 in market rent takes into account total units, not just occupied units. When you pull out the vacant units from the market rent calculation, the resulting loss-to-lease is about $30,000.
To your second question, in forecasting LTL in a multiyear pro forma, is the best way to determine the LTL % to use to extrapolate the historic LTL % (in place rents/market rents) and carry this forward? What if you are assuming a large rent increase post-renovation, I assume the LTL would jump for a couple of years while the higher rent units are absorbed and then would normalize.
Using historical LTL is one way to forecast LTL going forward. It really depends on what you expect to happen at the property. As you mentioned, if you are assuming a large rent increase post-renovation, than the LTL will be greater as the below-market leases roll to market. Once the property has stabilized to the market rents, than the LTL will simply be a function of leases rolling to market from one year to the next.
Happy to answer any follow up questions!
Spencer
Hey Josh,
I’m glad you’re getting into the nitty gritty and building these models yourself – this is how you learn!
To your question, you state: “I understand that the terminology in the Partnership Agreement says something like “Excess cash flow will be distributed X% to the partners in accordance with their partnership percentages, and Y% to the GP as a promote until the LP has achieved Z% IRR” – and you’re 100% on target here.
But remember, what the statement is saying is that the GP is distributed X% and the LP is distributed Y% in each tier until the LP hits Z% IRR target. Or in other words, irrespective of the balance of the GP’s capital account or the GP’s IRR at any point, once the LP hits a hurdle, the distribution %s change.
It just so happens in this case, that the GP and LP are distributed pro rata based on their ownership share in the first tier, such that the GP’s IRR and LP’s IRR upon hitting the first tier IRR are the same. But as soon as the GP is distributed a share of the distributions above its pro rata share in subsequent tiers (e.g. 2nd, 3rd, etc), its IRR will be greater than the LP’s IRR.
This is the purpose of the promote. To pay the GP an outsized share of the cash flow distributions when the GP exceeds the LP’s expectations (i.e. preferred return).
In terms of the model you built.
Attached find your model with my notes and changes highlighted in yellow.
Again, the GP is not targeting a required return – only the LP is. And since the GP gets an outsized share of cash flow in subsequent tiers (ie. 2nd, 3rd, 4th), once the LP hits its required return in the 1st tier, the GP’s IRR will be higher than the LP’s as cash flows in to the subsequent tiers.
Let me know if you have any questions!
Spencer
