AN OVERVIEW OF ARGUS MULTI-FAMILY MODEL
Transcript
Welcome to this video. I am going to walk you through this Argus Multi-Family Model and show you how to underwrite a complex apartment investment within 10 minutes. If you are interested, please visit our website at www.financialexcelmodeling.com or reach out to info@financialexcelmodeling.com for more information. I have put a link to the website in the description below.
Instruction
In the first tab of this excel, you'll find straightforward instructions on navigating the model. Simply click the RESET button, and you'll have a clean model to work with. Since I already click it, I will go straight to the input tab and start inputting assumptions to underwrite a hypothetical apartment investment. Here we can fill in some basic assumptions. For instance, I can enter a closing date of 10/1/2023, a 10-year holding period, and designate "XYZ" as the hypothetical deal name.
Sources and Uses
For the time being, I am going to copy and paste my sources and uses, there is plenty of space that you can add whatever you need. To speed up the progress, I have already pasted all my line items in place, most of them are based on dollar amount and only 2 of them are based on percentage. Of course, if you have more percentage line items, just simply change the format and cells to meet your needs.
Debt
Here comes to the debt section. I am going to input 30-years amortization schedule which is 360 months and 6% interest rate. Apart from this, I assume that the interest rate is 10% and same amortization schedule for mezzanine. Moving forward, I’ll assume a refinancing event for both senior loan and mezzanine on 10/1/2025, using a cap rate of 5%. The LTV is 70%, 360-month amortization schedule. And the interest rate will be 7%.
Disposition Assumptions
Now, let's focus on disposition assumptions. Assuming a 5% exit cap and 1.25% disposition costs.
Expenses
Then I need to input all the expense names to activate the expense section. Click on the INPUT EXPENSE button. This window is one of the most powerful features in this model. I can define expenses per unit per month, and have complete control on how the expense is going to change. Here I choose to increase the expense by 3% in the first month of each year. And I am going to do so for all expenses and fast forward a little bit. Now I finished inputting expenses. For other expenses, 3% for management fee. 1% Asset Management Fee.
Other Assumptions
Then I link the total Reserves to the CapEx Reserve in the uses and sources section.
Waterfall Assumptions
Moving to the Waterfall section, Quarterly cash distribution to investors, 1 tier in total. Distribution goes to 8% preferred return, then return of capital, at last the first tier of 12% hurdle rate, 30/70 split; after the hurdle, 50/50 split.
Unit Mix
Going to the unit mix tab, I am copying and pasting basic information about unit mix into the model, key metrics like unit types, number of units and average square feet.
This model can calculate the additional rental income generated by renovation and relevant costs associated with the renovation. Now I am going to input the Renovation schedule for each unit type. Click on the INPUT NUMBER OF UNITS TO BE RENOVATED button, we will see similar window pops up. 13 units will be renovated for the first month of the first year during the holding period, and 14 units to be renovated for the first month of the second year during the holding period. Then I move to renovation period, I assume that it will take 2 months to renovate, so I fill in 2 in the first white box. Click copy down and across.
For rental rate before renovation, input 1,500 dollars per unit per month for the small studio, and copy down and across, I set up 4% growth each year, Click Calc button and save. Then it will automatically input the rental rate before renovation for you. Same for the rent after renovation, I am going to input 1,875 dollars for the small studio, and 4% growth rate, Click Calc button to determine timing of inflation. Then click save.
And for the renovation cost, I will input 36,403 dollars for the small studio, and 3% annual inflation. Click Calc button and Save.
And the last part of the unit mix, LEASE TO LOSS means the rental loss due to the current rent being lower than the market rent. Input 3% for the first year, then click copy down and across, and save.
VACANCY LOSS means the rental loss due to empty units, input 10%, then click the copy down and across, and save.
EMPLOYEE UNITS LOSS means the rental loss for renting the units to the building manager at a lower rent, input 1%, then click the copy down and across, and save.
Moving to Model Unit loss, I will input 0.79% and then click the copy down and across. And click save.
I have finished setting up the small studio unit type. For the timing being, I will do the same for remaining unit types and fast forward a little bit.
Now I have finished setting up all the unit types. You can also see how many types of units and the average in-place rent in the tenant summary section. This model can accommodate up to 50 unit types.
Uses and Sources
Let’s move to the uses and sources tab, this tab will show how the future renovation cost and operating shortfall are financed, and you can see this column is linked to the sources and uses section in the INPUT tab. I will choose to finance the renovation cost with additional equity and debt financing. For the time being, I will copy and paste my future sources here. Scroll down a little bit and I can see uses equal to sources. It looks all good here.
Final Check
Let’s check out annual cash flow. It looks good. Last but not least, I will go back to the INPUT tab. Click FIX EVERYTHING button, the model is checking and recalculating itself. Everything is good. Click Ok.
Executive Summary
Let’s go ahead and check out the Executive Summary tab. I see a summary of a sensitivity test of the purchase price. Uses and sources. Debt assumptions. Debt service summary. Deal level returns. General Partner’s returns. Limited Partner’s returns. Carried interest analysis.
Sensitivity Table
Moving to the Return Sensitivity Table tab, This table provides comprehensive return analysis of general partner and limited partner based on various going-in cap rates and exit cap rates.
Background Calculation
The Tenant Monthly tab shows extensive background calculations, you can check every detailed metric used in the model. There are about 1,300 rows in this tab.
The Renovation Schedule tab also shows the massive backend calculation of the model. You can see the detailed renovation schedule and renovation costs here. There are about 25,000 rows in this tab. And we are all good here.
If you are interested, please visit our website at https://www.financialexcelmodeling.com/ or reach out to info@financialexcelmodeling.com for more information.