Forum Replies Created
-
Spencer Burton
Keymaster7 years, 8 months ago in reply to: Stabilized Year, DCF and Direct Capitalization #3130Great question! I had put a lot of thought into the OpSt tabs – not to say that means the logic is necessarily right! – so your question allows me to delve deeper into what’s going on there.
To answer your first question, yes that was intentional. That is a byproduct of the logic behind the “Stabilized Pro Forma” and the “Residual Pro Forma” idea. Let me explain further, and that should hopefully answer your subsequent question(s).
Rental income is driven by the assumptions on the MF/ORI-RR tabs, and inflated by growth factors entered on the MF/ORI-tabs. When those growth factors kick in, is determined by the “Growth Begin” date in the Timing section of the Summary tab.
Leasing costs (ORI), make ready costs (MF), expense recovery (ORI), and utility reimbursement (MF) are also driven by assumptions on the relevant RR tab, but are inflated by a growth factor entered on the relevant OpSt tab (column H).
The confusion I believe lies in the concept of the “Stabilized” and “Residual” Pro Forma and how they fit into the DCF. When building the model, I wanted three valuations to occur. 1) A DCF valuation (for acquisition purposes only), a direct capitalization valuation at “stabilization”, and a direct capitalization residual valuation at sale. The first two could be used, in combination with other metrics, to assess an appropriate present value with the third being used to determined the residual sale price for DCF purposes.
So if you look at the OpSt tab, the numbers in columns K:T are the operating cash flows that flow into the actual DCF. The figures entered into column J (Stabilized Pro Forma) constitute the baseline Other Income, Operating Expenses, and Capital Expenditures assumptions, as of the stabilized year, and flow into columns K:T. The Stabilized Pro Forma is also capped (see Property CF row E) to arrive at a “Stabilized Value” as reported in cell U10 of the Summary tab. The Residual Pro Forma values are calculated by default using last year of the hold period grown by the various growth assumptions but are blue font cells, thus alerting the user to the fact that additional thought should be given to what Pro Forma a future purchaser would use to arrive at a value. The resulting value, capped at the terminal cap rate (see column Q of the Property CF tab), flows into the DCF as the residual value.
However, there are a few nuances to the OpSt tab. The first is, stabilized year is not always year 1 and so Other Income, OpExp, and CapEx cash flows had to be treated differently in these unstabilized years. So how it works is, the values in blue you enter in the Stabilized Pro Forma constitute the values as of the stabilized year. An ‘Operation Begin’ date is set on the Summary tab, before which date all values are set to zero. Once operations begin, if a cash flow occurs before the stabilized year, the model deflates/discounts that cash flow by the CAGR (row H of the OpSt tab) to reflect that the stabilized numbers are X years into the future. Thus, if you open the model anew, go to an OpSt tab, and change the default Stabilized Year (J7) from Year 1 to Year 3, you’ll see the values in Year 3 will now match the values in the Stabilized Pro Forma and the values in year 1 and 2 are 2% per year less than the stabilized year.
The second nuance is that, leasing costs, concessions, and make ready costs are lumpy and don’t necessarily always represent a “stabilized” value. Thus, if you use an inflated concessions number in your stabilized pro forma, you’ll get a less-than-market valuation. Likewise, if you use $0 Make Ready costs in your stabilized pro forma because no MF units rolled in the previous 12 months, you’ll get a greater-than-market valuation. So, while concessions, leasing costs, and make ready costs are automatically calculated for DCF purposes, they are marked with an orange (optional input) font in the Stabilized Pro Forma so that the user can adjust the default calculations to something more appropriate. The default formula (in orange) essentially takes the average of the hold period, but that may or may not be true of a particular investment – thus the optional nature of those inputs.
Let me know if that answered all of your questions!
Keymaster7 years, 9 months ago in reply to: All in One questions – MF #31285. MF – OpStmt tab- i understood that j35 is an optional input tab since it’s orange. but when i enter an amt manually, it still carries out some different values to subsequent years. Also, on columns j & l i’m getting double dipped on vacancy in that it’s inserting a large figure in j14 and then another vac figure in j25. i can’t figure out how to modify.
These are very good questions, and get at some important nuances of the model. Let me break them down by explaining what’s happening with 1) Downtime Vacancy / Loss-to-Market (Row 14), 2) General Vacancy (Row 24), and 3) Make Ready Costs (Row 35). Then, I’ll explain why the values in cells K35:T35 don’t change when you optionally change the Pro Forma Make Ready cost in cell J35.
1) Gross Potential Rent is the gross market rent as if all available units were leased at market rates. Row 14 on the MF-OpSt tab then adjusts that Gross Potential rent to account for Downtime Vacancy (based on the Downtime assumption in days set in column AR of the MF-RR tab) and Loss-to-Lease (the difference between market rent and actual rent as modeled in columns M:AE on the MF-RR tab ). Downtime Vacancy / Loss-to-Lease are common adjustments used in multifamily underwriting to help understand the difference between actual rent collection (Total Rental Revenue Row 15) and potential rent collection (Gross Potential Rent Row 12).
2) Total Rental Revenue and Total Other Income are gross income values (Row 23: Total Potential Gross Income). They assume that the property is 100% occupied – even if certain adjustments for downtime, loss-to-market, and concessions have been made. They also don’t take into account credit loss. Thus, General Vacancy is what you assume on average vacancy plus credit loss will be at the property over the hold period.
3) When a tenant’s lease expires, one of two things can happen. Either the tenant renews, or the tenant vacates. If a tenant renews, the tenant is less likely to demand concessions and the unit will require less investment to make the unit ready for the next lease. In contrast, if the tenant vacates, a new tenant may require concessions and an investment will need to be made to get the unit physically ready for a new tenant. ‘Make Ready’ is the cost to bring a unit up to physical condition for a new lease. The Make Ready assumption for each scenario is made in columns Al and AM of the MF-RR together with the renewal probability assumption in column AQ. The model then calculates the number of units expiring each year, the blended cost to ‘Make those Units Ready to Lease’, grows that value by the annual expense growth rate, and reports that amount in rows 82 and 35 of the MF-OpSt tab. Note: a similar thing occurs with Concessions in Row 13 of the MF-OpSt tab, driven by assumptions made in columns AN and AO of the MF-RR tab).
So finally, why do the values in columns K:T of the MF-OpSt tab not change when you change the optional/orange inputs in column J (such as Make Ready J35)? To understand this, you must understand the Stabilized Pro Forma (column J) and the Residual Pro Forma (Column U) concept.
For valuation purposes, the model uses a direct cap method whereby some stabilized or residual net operating income (or CFO) is divided by an assumed market cap rate to arrive at a value. At analysis start, this value is calculated by taking the stabilized net operating income found in cell J40 of the MF-OpSt tab and dividing it by the Adjusted Cap Rate in cell L25 of the Summary tab. Likewise for residual (i,e, sale) valu, the residual net operating income found in cell U40 is divided by the adjusted cap rate grown by the annual change in cap rate as input in cell L26 of the Summary tab.
The stabilized and residual pro formas are NOT part of the net cash flow of the property – that’s what is occurring in columns K:T. They are purely used for valuation purposes. Thus, you’re given the option to adjust certain values in the pro formas (such as Concessions and Make Ready) that don’t impact the operating cash flow, but insures that the pro formas are representative of a typical year. This is more important with the office/retail/industrial tenants, as leasing costs can be quite sporadic requiring an adjustment to the pro forma to smooth out the noise.
Keymaster7 years, 9 months ago in reply to: All in One questions – MF #31264. Sources & uses tab – i am getting add %’s in the loan amts after i hit the set construction loan amt. The %’s shown do not match what i calculate manually.
I don’t entirely follow. Could you email me your model and I’ll take a look?
Keymaster7 years, 9 months ago in reply to: All in One questions – MF #31243. Equity CF tab – Is there a way here or in one of your waterfall models to show a promote on operating CF in addition to sale proceeds?
The model does not currently differentiate between operation cash flows and residual cash flows when modeling the partnership waterfall. I’ve added this concept to the feature request list for inclusion in a future release.
Keymaster7 years, 9 months ago in reply to: All in One questions – MF #31222.Permanent Debt tab – cell e12 is in blue font yet has a formula in it. can i manually enter an amt or will it make the model explode? if i leave that formula there, it has the effect of lowering my loan amt as the construction period ends. i have a constr/mini perm loan and i’m trying to have the model reflect that the IO burns off and then we leave that loan in place until we sell. or we may refi, in which case, we’d expect to increase the loan amt and recover some of our equity.
Blue font cells require user input. That means, the default value in every cell that is blue needs to be assessed by the user and changed accordingly. Values entered into blue font cells generally are hard coded numbers, but not always. Sometimes you may prefer to use a formula in a blue font cell, as with cell E12 of the Perm. Debt tab, to make an input value dynamic to changes to the model. So using the example of the default value used in cell E12 on the Perm. Debt tab of the template file, rather than hard-coding in a loan amount, I wrote a formula such that the loan amount would always be equal to the lesser of 70% of value or 75% of cost. It’s up to you to change that cell, either via a formula or by hard-coding in a loan amount, to match your situation.
Keymaster7 years, 9 months ago in reply to: All in One questions – MF #3120I’ll be answering your questions sequentially and in separate responses.
1. Summary Tab – in valuation details i’m getting a message saying “rates are not accurate, set up rate matrix”. i selected my region and then added 100 bps to it. not sure what else i need to do.
The alert: “Rates are not accurate, setup Rate Matrix” that you see on the Summary tab when you open the template file for the first time is meant to alert the user if/when the Rate Matrix has not yet been setup. When the model was built, a dummy rate matrix was created with base cap rates and discount rates that are not correct for each market. It is up to the user to set locations with cap rates and discount rates appropriate for their investment platform. You can learn more about the Rate Matrix and how to set it up here.
With all of that said, it is not required to use the Rate Matrix feature. But if you plan to use the model as a template to assess multiple deals, the Rate Matrix is a great tool to add consistency to your underwriting and speed up the valuation process.
Keymaster7 years, 9 months ago in reply to: Multiple Building, Condo Conversion models #3118Thanks for the question. Unfortunately, the core of the All-in-One model is built around for-rent properties rather than for-sale properties. For what you’re doing, you might try one of our for-sale product models. Have you looked at Michael’s condo development model? You can find it here.
Keymaster7 years, 9 months ago in reply to: Renewal Income number of months #2822Thanks for pointing this out! I’m working on an update to be released this weekend, and I’ll be sure to include a fix for this bug.
Keymaster7 years, 9 months ago in reply to: Construction Loan Interest #3116Great question.
When the Development Module is activated, financing includes first a construction loan (unless LTC on the Sources and Uses tab is set to 0%), that is subsequently paid off by a permanent loan (via the Perm. Debt tab). The funding of the Permanent Loan and payoff of the construction loan occurs at stabilization. So during the period you are carrying construction financing until the permanent financing takes out the construction loan, interest will accrue. Oftentimes, stabilization does not occur until sometime after construction ends and thus you’ll see interest accruing past the ‘Development Length’ set on the Summary tab. You can change the stabilization date in the Timing section of the Summary tab.
Keymaster7 years, 9 months ago in reply to: Construction Loan Formula #2820Thanks for the comment. The formula =H14 *D7 in cell C7 of the ‘Sources and Uses’ page is correct; although I can understand how the logic can be confusing here.
The construction loan plus the construction loan interest reserve are sized using a Macro (Developer>Macro>’Goal_Seek_LTC’), which is activated by hitting the ‘Set Construction Loan Amount’ button on the Sources and Uses tabs. This is how it works: the user enters a LTC % for the total construction loan (construction loan + interest reserve) into cell E7. Then, when the user clicks the ‘Set Construction Loan Amount’ button, a Macro (‘Goal_Seek_LTC’) runs that adjusts the value in cell D7 iteratively until the value in cell E7 is equal to the value in cell E8. When the Macro finishes running, the construction loan amount plus interest reserve divided by the Total Uses is equal to the LTC entered by the user in cell E7.
This was done to avoid creating a circular reference when calculating the construction interest.
Keymaster7 years, 9 months ago in reply to: Residual Land Value and Budget Tab #3110Following up. Functionality to allow you to model additional land costs as part of your residual land cost calculation has been added to beta v0.5.1 of the model.
Keep in mind, modeling the land costs still happens on the Residual Land tab (not the budget tab). This is due to the complexity of the add line item macros on the budget tab and the residual land value macro. There are some limitations as a result, such as you can’t model when a specific item is spent . It will be spent straight-line and in the same period(s) as the land itself.
Keymaster7 years, 9 months ago in reply to: How is Residual Land Value Calculated? #3114In terms of how the residual land value is calculated. It uses a macro to solve for a given return metric. So for instance, if you set the target return metric to ‘Levered IRR’ (column F) and then set your target Levered IRR to 15% (column G) , when you hit the ‘Find Residual Land Value’ button a macro will run that will iterate through different Total Land Costs until the Levered IRR at that Total Land Cost is equal to 15%.
You can learn more about the module here:
https://www.adventuresincre.com/all-in-one-walkthrough-5-residual-land/
You can also read an earlier post I wrote on residual land value as a real estate concept here:
https://www.adventuresincre.com/calculating-residual-land-value-excel/
Keymaster7 years, 9 months ago in reply to: Residual Land Value and Budget Tab #3108This is a great suggestion. I’ll add it to my request list and make this possible in the next version of the model.
Thanks!
KeymasterAbsolutely. The multifamily module (included in beta v0.4.0+) was built for just this scenario. On the MF-RR tab, you’ll notice two columns: an “In-place Occ. Unit Rent/Mo” column (column M) and a “Market Rent /Unit/Mo” column (column Q). You’ll also notice “Roll to Market” and “Roll to market month” columns (columns O and P). These four columns allow you to 1) set an in-place rent amount per unit and 2) set a market rent to roll your in-place rents to at some future date.
Thus, for your scenario, set the in-place rent. Set “Roll to Market” to ‘Yes’. And then set market rent to the higher rent you expect to yield post-renovation. Then, model your renovation costs either using the development module (for significant redevelopment) or the ‘Other CapEx’ monthly cash flow line on the MF-RR tab.
Keymaster7 years, 9 months ago in reply to: Updates and Pay What You're Able #3074Austin, happy to hear our site has been helpful to you and best of luck with your studies! I’ve heard great things about the U’s MRED program.