[Spencer Burton]

6 years, 10 months ago in reply to: Programming Style #3048

I use the IF (CELL=””, then X, otherwise Y) concept in this model to make the model Dynamic to the hold period.

The header is built such that if a specific period is beyond the hold period, then the cell is left empty (i.e “”). Then, each formula first asks whether the period is empty (I.e. beyond the hold period), if so leave the cell empty, otherwise show the Cash flow.

[Spencer Burton]

6 years, 10 months ago in reply to: Physical Occupancy #3046

On the ORI-OpSt tab, you can model general vacancy. So if you want to keep the property at say 70% vacancy, set your general vacancy to 30%.

[Spencer Burton]

6 years, 10 months ago in reply to: Two questions #3044

To your questions:

1) The current version of the model assumes 100% fixed expenses, but adding expense variability is actually a pretty simple thing. I’ll go ahead and add that to the feature request list and get it included soon (perhaps as soon as the next release).
2) You are correct, the user can custom model rent changes and/or reimbursements on a deal-by-deal basis by overriding cells/formulas on the ORI-Calc tab. The more proficient one gets with the model, the easier this will become and the more precise the cash flows would be.

[Spencer Burton]

6 years, 10 months ago in reply to: What is LC on ORI-Rent Roll tab #3042

LC means “Leasing Commission” and is a percentage of total rent of a newly signed lease. New LC would be the leasing commission percentage on a new tenant while Renew LC would be the leasing commission on a renewal tenant.

[Spencer Burton]

6 years, 10 months ago in reply to: How to Show Retail Analysis Tab #3040

Make sure the property type (Summary F7) is set to Retail and the Reports tabs (Summary F34) is set to show.

[Spencer Burton]

6 years, 10 months ago in reply to: How to treat land #3038

Regardless of whether you own the land or not, you’ll want to apply some value to the land in your budget calculation. Then when you’re looking at your required equity contribution, the attributed value of the land can be deducted from the equity required to understand your cash requirement.

And the same goes for LTC calculation. The value of the land is the value (unless the lender requires you to contribute the land at your cost).

[Spencer Burton]

6 years, 10 months ago in reply to: Modeling Equity CF #3036

I would consider it a capital cost (additional investment cost) and use the development model to model a value-add ; assuming you have an unstabilized redevelopment period funded by construction debt. Make sure the dates on the timing section line up with your projections, and that the loan amounts for construction and permanent debt are correct. The Equity CF will then automatically calculate for you.

[Spencer Burton]

6 years, 10 months ago in reply to: Modeling Equity CF #3032

Thanks for the question. Let me first explain how the model is handling the cash flows and perhaps that will help answer your question.

The model converts the construction loan to permanent debt in the stabilization month. Prior to stabilization, capital costs are funded first by equity on a pro rata basis between GP and LP and then by construction financing (See Sources and Uses Tab for those inputs). Once the stabilization date is reached (set in Cell M17 of the Summary tab), cash shortfalls in any period (e.g. due to operating shortfalls or post-stabilization capital costs) are covered by equity alone on a pro rata basis between GP and LP. Those calculations are automatically made on the Equity CF tab in periods where Levered Before Tax Cash Flow is negative. The relevant amounts are automatically added to the GP and LP’s capital accounts (Rows 45 and 54 of the Equity CF tab).

[Spencer Burton]

6 years, 10 months ago in reply to: Modeling 1st Gen Leases with More Precision #3030

In terms of your question about 1st generation rent and precision. The model offers a greater level of precision on 1st generation leases then most Excel models, but it lacks the precision of non-Excel solutions in a couple of important ways:
1) Rent escalation assumptions are made through annual periods, and as a result lacks the precision that comes with monthly (or even daily) escalation assumptions.
2) Expense recovery is an approximation, and is calculated using an expense recovery percentage per year rather than attempting to model the various expense recovery methods common in the industry.
To why I chose to model 1st generation leases this way. It comes down to complexity, model size, and memory resources. The more complexity you add to the model, the larger the file and the more processing power is required to handle that complexity. Plus, the time it would take to model to the same precision as ARGUS or other non-Excel solutions just wasn’t worth the marginal benefit, it in my view.

[Spencer Burton]

6 years, 10 months ago in reply to: Recovery Calculations #3028

Yes, it would require a more comprehensive recovery module. As currently modeled, the user only has the ability to underwrite/forecast the expense recovery percentage for each tenant.

[Spencer Burton]

6 years, 10 months ago in reply to: Equity CF Tab #3026

1) This is a good comment. The “Error” message is poorly worded. It actually should read: “Insufficient Cash Flow” to return all capital to LP/sponsor including accrued pref. I’ve gone ahead and rewritten the message to avoid confusion. The spirit of the cell remains however, which is to let the user know whether the investment as underwritten provides sufficient cash flow to return 100% of the LP and sponsor’s capital plus accrued preferred return. You can test this by simply changing the Preferred Return (cell D18 of the Equity CF tab) to a percentage less than the property-level Levered IRR.

2) Again, because the investment as currently underwritten has insufficient cash flow to cover a return of capital plus pay the required pref, their respective capital accounts are positive at the end of the analysis period. The LP and Sponsor capital accounts are left unpaid and that amount is not figured into their respective returns.

[Spencer Burton]

6 years, 10 months ago in reply to: Drop-down Menu Yes/No #3024

Cell Summary!F30 is a calculation cell (black font) and not meant for user input. However, I see the confusion – with the letters being small it’s hard to tell the font is black plus, with a drop-down menu in the cell, it appears to be an input cell. I’ve gone ahead and removed the drop-down menu to hopefully remedy the issue.

[Spencer Burton]

6 years, 10 months ago in reply to: #Name Error on Budget Sheet #2812

Working on my end – sounds like it is incompatible with Excel 2007. I wonder if the NORM.DIST() I use in the S-Curve calculation isn’t supported by Excel 2007? If you have a second, open your Excel 2007 and type in =NORM.DIST and see if it shows up in Excel’s formula list.

[Spencer Burton]

6 years, 10 months ago in reply to: #Name Error on Budget Sheet #2808

I don’t believe your Excel version is the issue, although I’d suggest you upgrade at some point. Looking through your file, you added a few new rows on the budget tab under Soft Costs. I believe this created the error as the S-Curve calculations are done in a separate tab (‘Dev-SCurve Calc’). Without adding new rows to the Dev-SCurve Calc tab to match the new rows on the Budget tab, the model will error out.

I’m developing instructional videos for each tab and will be sure to mention this issue when I get to the Budget tab training. I might also add functionality to add more budget rows, but for the meantime I’d suggest you move a few of your items such as Insurance and Misc. Costs to the ‘Other’ section of the Budget.

As a side note, your model also helped me identify another small issue. Your construction period is starting a couple months after the start of your analysis period (March vs May) creating two months of zero unlevered cash flows. This is causing the Avg. Free and Clear return metric to error out on the Summary tab (cell T8). I’m releasing a new version with a host of bug fixes today (I’ll shoot you a copy when it’s done) and I’ll include a fix for that one as well.

[Spencer Burton]

6 years, 10 months ago in reply to: #Name Error on Budget Sheet #2804

Thanks for the heads up! It probably is a simple fix but tough to say without seeing the Workbook. Would you shoot me over your file and I’ll take a look.