OTHER ASSUMPTIONS SECTION
Welcome to this video. In this video, I will cover the other assumptions section in the ARGUS Excel Model. There are three assumptions in this section: general vacancy, annual market rent growth, and total reserve. Let’s get into it.
The general vacancy provides a cushion in the underwriting to avoid over-optimistic forecasts in the cash flow. In the real world, a property normally won’t reach 100% occupancy. If it does, the rental income received by the owner usually won’t be 100% because of credit loss, unexpected costs or other reasons. The general vacancy takes these factors into consideration. I will input 8% for the general vacancy. In other words, I assume this property’s occupancy will not be greater than 92% throughout the holding period. Let’s say the occupancy rate rising from 80% to 100% in my underwriting. The 8% general vacancy is included in the 20% vacancy at the beginning. When the occupancy goes above 92%, the general vacancy kicks in to keep the occupancy at 92%. This mechanism plays an important role in making sure the underwriting is realistic.
Annual Market Rent Growth
The annual market rent growth is associated with the market rent. I will input 3%. I can see that progress bars keep popping up. The 17 dollar is the current market rent for the major tenant. Let’s say a major tenant is going to renew its lease 4 years later at market rent. The lease will be renewed at 19 dollars 13 cents, which is 17 dollars inflated by 3% four times. The annual market rent growth is a very important variable in this model and can make a significant impact on the outcome of the underwriting. It will take a while as all tenants’ rent is being inflated. I will fast forward a little bit.
The last variable is the total reserve. I will sum up reserve numbers in the sources. In my case, it is the capital expenditure reserve plus the tenant improvement reserve. Reserves are the funds put aside at the closing of a deal to pay for future expenses. Such as leasing commission, tenant improvement, or maintenance. Let’s go to the Annual Cash Flow Summary tab to see how the reserve is reflected in the cash flow. I can see the reserve are used in year 1, yea2, year 3 and year 4. It means the cash flow would have turned negative if the reserve were not set up. The unused portion of the reserve is refunded upon the exit of the deal. Let’s get into the Monthly Cash Flow Summary tab to find out how this works. In month 8, about 100,000 dollar leasing costs are incurred. The cash flow would have been about negative without the reserve, which means the revenue in month 8 is not enough to cover the expenses. Therefore, about 40,000 dollars need to be withdrawn from the reserve to cover the expenses. The reason of having this function is because the change of sign of the cash flow messes up the IRR and waterfall calculation. There are various resources online discussing this issue and I will leave a link for one of the resources below (https://www.business-case-analysis.com/internal-rate-of-return.html). I can see there are withdraws and deposits in the reserves section. In other words, I can also model when and how much I deposit additional funds from the cash flow into the reserve for a rainy day. Let’s go back to the Input tab. There are Reserve Deposits in the expense section. Click the INPUT EXPENSE button. An expense window pops up. This window functions the same way as the Rent Roll Input window in the rent roll section video. Let’s say I have a schedule to set funds aside for the reserve. 50 cents per square foot per annum. I will click Copy Down and Across. Save. Go to the annual cash flow tab. I can see that about 60,000 dollars is being put into the reserve each year. Let’s go back to the Input tab. Click the Input Expense button and zero out these numbers. Save. I would not recommend inputting a random number like I just did unless you have a clear reserve schedule beforehand. The main purpose of this reserve feature in the ARGUS Excel Model is to avoid negative cash flow. This process is also called “smoothing out the cash flow”. Because of its complex nature, I would leave this job to the computer. This is one of the most powerful features in the ARGUS Excel Model. One of the functions of the FIX EVERYTHING button is to identify the best reserve schedule automatically for me. The Fix Everything button will be covered in detail in another video later.
I finish the other assumptions section in the ARGUS Excel Model. Thanks for watching this video. I will see you in the next one.