Forum Replies Created
-
Spencer Burton
Keymaster7 years, 5 months ago in reply to: MF Market Rent vs. Actual Rent, and Vacancy #3200Two great questions.
1) The MF module uses the Loss-to-Lease concept together with Downtime Vacancy and Concessions adjustments to arrive at a Total Rental Revenue line. If your unfamiliar with Loss-to-Lease, it is essentially the difference between in-place (actual) rent and market rent. So when modeling your rent roll on the MF-RR tab, you enter both the actual rent and market rent for the model to calculate Loss-to-Lease. The model then, on the MF-OpSt tab, drops in Gross Potential Rent (Market Rent) at the top, and subtracts out Loss-to-Lease to arrive at actual rent before concessions and downtime vacancy.
You can learn more about Loss-to-Lease here: https://www.adventuresincre.com/glossary/loss-to-lease/
2) In the MF-Module, you would account for vacancy in the ‘General Vacancy’ (MF-OpSt H24) and downtime vacancy (MF-RR column AR) assumptions. On the MF-RR tab, you always enter the total number of rentable units in column G (Total Units) and the total units leased as of operations begin in column I. The model then leases the property up to full occupancy, but subtracts out downtime vacancy and general vacancy to arrive at a stabilized effective gross income.
Keymaster7 years, 5 months ago in reply to: Student Housing New Development Modeling Questions #3198A lot of great questions, let me take them one at a time:
1. You’re correct, the Permanent Loan funds per the stabilization date assumption entered on Summary tab. There is no way to (easily) change this logic. However, keep in mind that your assumed “stabilization date” does not have to match your actual stabilization date. You’ll notice the stabilization date input on the Summary tab is an orange font (optional input) cell. This is for situations just as you propose where you wish to set the Permanent Loan fund date to some period that doesn’t match the actual stabilization. So – if I understand what you’re needing – just change the stabilization date assumption on the Summary tab and it should give you your desired result.
On a related note. If you change the stabilization date assumption to a date that does not match the actual stabilization, be sure to confirm that the first stabilized year assumption (cell J7 on the ORI-OpSt or MF-OpSt tabs) – also an orange font/optional input cell – is correct or your stabilized value output will be off.
2. Sure. Just set the Construction Loan Fees input (S&U tab) to 0%, and add a ‘Construction Loan Fee’ line item to your budget with a start date of 1 and a length of 1.
3. I’ll add it to my list of bug fixes.
4. Hmm.. Not sure I fully understand. The model gives you an error when you attempt to add a line item under Land? I can’t seem to replicate that on my end. Could you send me a screenshot?
5. Not by default. This would require customizing the model.
Keymaster7 years, 5 months ago in reply to: additional tabs #3196Victor,
I apologize for the slow response – busy week before Thanksgiving! I responded to your previous post, but to be sure you see the response:
Sounds like the Macros aren’t running properly. Did you enable Macros when you opened the file initially? Try closing Excel, renaming the original file you downloaded, opening it again, and the ‘Enable Macros’ box should pop up anew. Click to ‘Enable Macros’ and let me know if the problem persists.
Spencer
Keymaster7 years, 5 months ago in reply to: Only two tabs… #2802Victor,
Sounds like the Macros aren’t running properly. Did you enable Macros when you opened the file initially? Try closing Excel, renaming the original file you downloaded, opening it again, and the ‘Enable Macros’ box should pop up anew. Click to ‘Enable Macros’ and let me know if the problem persists.
Spencer
Keymaster7 years, 5 months ago in reply to: Developer/Sponsor Fees #3194In the case of modeling a development, land acquisition and development fees would be entered in the construction budget as line items on the Budget tab.
In terms of sponsor fees (e.g. asset management fees, fees charged at sale, etc), the model does not have the option to model those. Given that sponsor fees are partnership-level expenses, rather than property-level expenses, they should be modeled as part of the waterfall (Equity CF tab).
Every partnership is slightly different, and so creating a one-size fits template for these types of situations is difficult. I’d suggest customizing the waterfall (Equity CF tab) for your specific partnership structure to account for those fees. It wouldn’t be especially difficult – just subtract the non-development fees you expect to be paid to the sponsor from the ‘Property Levered Before Tax Cash Flow’, and then add those same fees back into the Sponsor’s ‘Total Sponsor Distributions’ line.
Keymaster7 years, 5 months ago in reply to: MF Development Module Change Analysis Period from 10 Yrs #2844You’re right, manually overriding the stabilization date doesn’t fix the issue.
Yes, I uploaded the fix yesterday in fact. You can find the updated file here: https://www.adventuresincre.com/?paybox-key=bglbuz93owiqwa8r
In terms of the chart disappearing, I haven’t run into that yet. Can I ask, what operating system and version of Excel are you running?
Keymaster7 years, 5 months ago in reply to: MF Development Module Change Analysis Period from 10 Yrs #2838Thanks for pointing this out. I’d failed to make the Stabilization calc formulas dynamic to the analysis period length. Thus, any period other than 10 errors out the Stabilization date. I’ve updated the model and uploaded the corrected version.
As a fix for earlier versions, such as the version you’re working with, just manually enter a Stabilization date on the Summary tab, and that should fix the problem.
Keymaster7 years, 6 months ago in reply to: Two Properties Analyzed at Once #3338The short answer is yes – but the process isn’t especially intuitive since you’d have to use the Development Module even though the investment isn’t a development!
Here’s how you would do it:
1) Turn on the Development Module by setting the ‘Development Length’ (see Summary tab) to the number of months until you acquire the second property.
2) Set the Operations Begin Date (Summary tab) to be equal to the Analysis Start Date.
3) Set the Stabilization Date (Summary tab) to be equal to the date when the second property is acquired – this should be one day following the ‘Development End Date’
4) Go to the S&U tab, set the LTC to 0%, and then hit the ‘Set Construction Loan Amount’ button. The ‘Construction Loan Amount’ may show as some value close to, but not exactly 0 – just ignore this as it’s a rounding error related to how the Macro is written.
5) Go to the Budget tab and in column D, zero out all of the blue font cells. Then, change one of the Budget categories (e.g. Construction Costs) to ‘Acquisition’. Under that newly labeled ‘Acquisitions’ category, rename a line item to ‘Property #1’ and then set the ‘Start Month’ and ‘Length (Months) both to 1. Then, rename a second line item to ‘Property #2’ and set the ‘Start Month’ to the month when the 2nd acquisition will take place (should be equal to the Development Length) and the ‘Length (Months)’ for Property #2 to 1.
6) Finally, be sure to go to the ‘Perm Debt’ tab and set a loan amount for the loan to be taken out when you acquire the second property. This will fund at the same time as the 2nd acquisition.
7) Underwrite the remainder of the model the same as you otherwise would.Best of luck!
Keymaster7 years, 6 months ago in reply to: Rule of thumb on % of revenue/income for rent/real estate (Industrial) #3192In retail, there are a couple of metrics commonly used that gets to what you’re suggesting; ‘Tenant Occupancy Cost’ and ‘Tenant Sales PSF’. However, Tenant Occupancy Cost analysis is typically only used in retail since many retail leases include clauses that require tenants to report sales. The Ai1 does include a ‘Retail Analysis’ tab (make sure on the Summary tab that the Property Type is set to Retail and that the Report tabs is set to ‘Show’ under Naviation) that helps the user do this type of analysis.
In terms of adapting it for other tenant types, that I haven’t considered. If you wanted to take a crack at it, you should check out the post I wrote on this subject a while back:
https://www.adventuresincre.com/tenant-sales-occupancy-cost-analysis/
KeymasterAssuming the retail makes up a small share of total revenue, I’d suggest doing the following:
1) On the Summary tab, set the Property Type to ‘Apartment’; make sure the MF Tabs (under Navigation) are set to ‘Show’
2) Go to the MF-Settings tab and change one of the ‘Other Income’ categories (e.g. Parking Income or Storage Income) to “Commercial Income”. Then go to the MF-OpSt tab and in column J next to ‘Commercial Income’ enter the net income from the retail component (netting out commercial expenses but before vacancy).
3) Still on the MF-Settings tab in column J, go to ‘Other CapEx’ and make an assumption for the average leasing costs per year for the retail space.Now you’ve accounted for the retail component in a retail deal. Of course, if the retail makes up a substantial share of total revenue, this may not be the right solution.
Keymaster7 years, 6 months ago in reply to: Ai1 model not received #2832Also note, I just re-sent the link. Let me know if the email doesn’t come through. You can reach me directly at [email protected]
Keymaster7 years, 6 months ago in reply to: Ai1 model not received #2830The link should arrive within 5-10 minutes (typically sooner). If you don’t see the link come through, check your Spam folder as sometimes the emails get caught up in server-side filtering. If you don’t see the email after 10 minutes and it’s not in your Spam folder, email either Michael or I and would manually forward the link to you. You can message us here: https://www.adventuresincre.com/about-contributors/
Sorry about that!
Keymaster7 years, 6 months ago in reply to: Downloading new version #2828My guess is, your ‘Development Length’ (cell M13) is set to 0. If so, the Development module is turned off automatically, leaving the tabs hidden. To activate the Development module, set some development length in cell M13 and then make sure your Development Tabs are set to ‘Show’ under the Navigation section on the Summary tab.
Keymaster7 years, 6 months ago in reply to: Downloading new version #2824Assuming I’m understanding the question correctly, the file (i.e. Excel Workbook) you downloaded is the entire file. The Ai1 consists of 29+ worksheets (i.e tab or page), with 10+ distinct modules. Each worksheet and module is tailored to specific property and investment types, and therefore not necessarily used each time you underwrite a new deal. Thus, by default upon opening a fresh file, all but two tabs are hidden. As you begin to fill out the ‘Summary’ page, the worksheets/modules that that specific deal needs unhide and become available to you.
So in your particular situation: open the file, head to the Summary tab, and read the ‘Guide to Getting Started’ along the right hand side of the Summary page. It will guide you through the basics of filling out the Summary page. Then, as you complete the Summary page the worksheets that you need will automatically appear.
Please let me know if the above doesn’t solve your issue. Thanks!
Keymaster7 years, 6 months ago in reply to: Debt-Coverage Ratio #3168You’ll find debt metrics (DSCR and DY) on the ‘Property CF’ tab – see rows 30, 31, 75, and 76.