Expense Reimbursement – ORI calc tab

[Anonymous]

5 years, 6 months ago #2486

Hello,

Thank you for developing such a detailed model! This has been super helpful for my underwriting and has already taught me a lot. I noticed what I believe may be a bug on the ORI calc tab/ In the expense reimbursement section, row 11 refers to Q11, which is the total row for 1st generation lease rent detail. The formula looks to be saying IF the input from row 11 beginning in column Q> start of future lease dates, give me the reimbursable % of pro-rata reimbursable operating expenses. Otherwise, give me the recovery percentage of pro-rata share of operating expenses.

The problem is that row 11 in the calc tab of the model is blank for the 1st generation lease rent detail section. Have I made a mistake / am I missing something? Also, could you help explain the logic of the formula?

Thank you,
Tim

[Spencer Burton]

5 years, 6 months ago #2906

Tim,

In version 0.6.4 (August 25, 2018), I made a major change to how ORI rent bumps were calculated. In previous versions, ORI rent increases at the tenant level occurred on the yearly anniversary of the analysis start date. This was a major limitation in the model, and had originally been modeled this way only because when I first built the ORI module, I wasn’t aware of a simple way to model rent bumps on the anniversary of each leases start date.

In late 2017, I developed a relatively simple formula to find the anniversary of a lease’s start date. However, adding that formula in the All-in-One wasn’t a simple task, since it required finding and replacing the old logic with the new logic throughout the ORI-RR and ORI-Calc tab. I eventually found the time to roll out this update, which as I mentioned occurred in August 2018.

However, making this change left some superfluous logic in various formulas. Most of that logic I found and deleted, but apparently not all of it as you have found more! Keep in mind, I say superfluous logic because whether this particular logic is in the model, or not, doesn’t matter. It has no impact on the calculations themselves.

For example, in the case you point out (cell Q12 of the ORI-Calc tab). The 2nd nested IF statement in cell Q12 states [IF(Q$11>$J12,P12,)] or in other words: If Q11 (which is now blank and no longer used), is greater than J12 (1st gen lease end date), then return the previous year’s rent (P12), otherwise continue with the formula. The balance of the formula then returns the 1st gen contract rent for that year, including any rent bump as of the anniversary of the lease start date, per the new logic I developed. I’d inadvertently failed to delete that old logic, nonetheless that old logic is such that it would never be true and thus, never realized.

Thanks for pointing out the unnecessary logic! I’ll clean it up in the next version release.

Spencer