RENT ROLL SECTION - ROLLOVER - PART TWO
Welcome to this video. In this video, I am going to cover the Rollover Section in the ARGUS Excel Model. I will go through how to create a customized tenant profile.
I have completed the most of rollover rent roll in the last video and only get 4 empty suites left. I am going to use the customized profile for the first empty suite. I am assuming the empty suite will be leased out in 3 months. There is 0% renewal probability since this is a lease-up and the suite is originally empty. I only need to pay attention to the assumptions about the new tenant so I will skip the assumptions about the renewal tenant. 9 dollars for tenant improvement of the new tenant. 5% for the leasing commissions of the new tenant. 2-month free rent period for the new tenant. The free rent is inside the lease. The lease term is 60 months. Reimbursement method is the Fixed Amount. Click the Fixed Amount button. 10 dollars per square foot per annum. Copy down and across. Save. Going back to the Rollover button. Click it. Input 20 dollars. Copy down and across. The rent increases by 3% each year, Hit the first calc button. and save. The first rollover is finished. Let’s go to the second rollover.
I am going to input the same assumptions except that I also need to input renewal tenant assumptions this time. 3 months downtime. 80% renewal probability. 9 dollars for the tenant improvement of the new tenant. 4 dollars for the tenant improvement of the renewal tenant. I can see the underwritten total tenant improvement turns into 5 dollars, which is the weighted average going into the cash flow calculation. 5% for the leasing commissions of the new tenant. 2% for the leasing commissions of the renewal tenant. 2-month free rent period for the new tenant. 1-month free rent period for the renewal tenant. The free rent is inside the lease. The lease term is 60 months. Reimbursement method is the Fixed Amount. Click the Fixed Amount button. 10 dollars per square foot per annum. Copy down and across. Save. Going back to the Rollover button. Click it. Input 24 dollars. Copy down and across. Rent increases by 3% each year. Hit calc button. and save. I finish the customized profile for the first empty suite. Now I can see that I have the flexibility to either define individual tenant profile or use market leasing profile.
Scrolling to the right. I can see 4 months downtime. Underwritten Renewal Probability of 65% matches what I input in the major tenant market leasing profile. Underwritten Tenant Improvement of $22 is the weighted average number from the major tenant market leasing profile. Total Underwritten Tenant Improvement is calculated as the Underwritten Tenant Improvement times NBC Marketing Services’ square feet, which is 48,174 times 22 equals to 1,059,828. Underwritten leasing commission, underwritten free rent, and underwritten lease term all match what I have input in the major tenant market leasing profile. Keep scrolling to the right. I can see the second rollover has also been automatically assigned the major tenant market leasing profile. The downtime, renewal probability, tenant improvement, and other variables are consistent with the major tenant market leasing profile.
The Second Empty Space
For the second empty space, I will assign the minor tenant market leasing profile. I will input a 6-month downtime period to override the 1-month downtime from the minor tenant market leasing profile. Renewal probability is 0%.
Scroll all the way to the end of the first rollover. I will input 10% for admin fees. The admin fees are the additional expense paid by the tenants based on their reimbursement. For example, a tenant pays 100 dollars as reimbursement to the owner. With the 10% admin fees, the tenant will pay 110 dollars to the owner in total. The extra 10 dollars account for the cost incurred when the owner calculates the reimbursement amount for tenants and collects the payment. To keep the consistency, I am going to input 10% admin fees for the second rollover as well. Now I can see I have the flexibility to customize assumptions in the market leasing profile for individual tenant.
The Third Empty Space
I will assign the medium tenant market leasing profile for the third tenant. 9 months downtime period. 0% renewal probability. The last one is a minor tenant. 12 months downtime period. 0% renewal probability. I finish the rollover section in the Rent Roll tab.
Let’s go to the Occupancy tab. In this tab, number 1 means the suite is empty and it is also highlighted in the table. Number 0 means the suite is occupied. The table is based on the monthly occupancy situation. The first cell means the NBC marketing services occupies the suite in October 2018. I can see the KIW Group has 2 months downtime in March and April 2019, which is the period between the expiration of its existing lease and the beginning of its first rollover. This is consistent with the Medium market leasing profile assigned to it. I can also see the 12-month lease-up period for the 4 empty suites. Every 3 months, a suite is leased out. This table can provide valuable occupancy insight throughout the holding period.
Let’s go to the Annual Cash flow Summary tab. I can see all the revenue has been modeled out but there are errors in the reimbursement. This is caused by the admin fees which creates circular references in the model. A quick way to fix this is to go to the Input tab and click the FIX EVERYTHING button. I will cover the FIX EVERYTHING button in another video. It takes some time for the window to show up. For now, I will just click Okay. Going back to the Annual Cash Flow Summary tab. I can see the reimbursement is calculated properly now.
Let’s go back to the Rent Roll tab. In the ARGUS excel model, I can check out individual tenant’s cash flow. I can see a column of Cash Flow hyperlinks. They are hyperlinks to the individual tenant cash flows. If I would like to check out the cash flow of NBC marketing services. I will click on the first Cash Flow hyperlink. The hyperlink will take me to NBC Marketing Services’ cash flow tab. In this tab, I can see the revenue, reimbursement, expenses, tenant improvements, and leasing commissions of NBC marketing services. This provides insights into individual tenants. I can click on the Rent Roll hyperlink on the top left corner to go back to the rent roll tab. I can check out every tenant’s cash flow by clicking down this cash flow column one by one.
Thanks for watching this video. I will see you in the next one.