The Dashboard is where you can input all the assumptions regarding the transaction you want to review. The tab is split in 5 sections:
Acquisition Assumptions:
Here you can enter a property description as well as purchase assumptions (acquisition price, closing costs, etc.), timing assumptions (purchase date and holding period), and sale assumptions. Note that you can estimate your property’s value over time via two methods:
Appreciation: the model will apply a certain annual growth rate to your property. Note that the distinction is made between inflation rate and appreciation above inflation. If you want to remain conservative, you may decide not to enter any additional growth rate above inflation, unless you operates in large cities that are experiencing high growth in property value every year and where most of the return comes from appreciation. Note that the annualized inflation rate in the US for the last 10 years (2009-2019) was approximately 1.8%. Although past performance is not an indicator of future outcomes, this should give you an idea of what to use in the spreadsheet for your analysis. If you want to know what was the historical growth rate of a particular city, Zillow does list this data (and more) on this webpage.
By ticking the “Custom Annual Rate” box, you can enter custom annual growth rates for each projected year. If selected, assumptions have to be entered directly in the “Forecasts” tab. This feature is particularly useful if you expect to have significant variations in property value in the future. For instance, if you plan to completely rehab a property in year 3, it’s fair to assume that its value will increase that year.
Capitalization: this method calculates your property value based on the Net Operating Income of your asset at the sale date. By dividing this NOI by the expected capitalization rate, you can estimate what would other investors agree to pay for the asset based on this particular targeted rate of return. Note that this method is typically used in commercial real estate, but is less common for residential real estate.
Financing Assumptions
Here you can input all the information related to your financing, including down payment percentage (typically 20% in the U.S. currently, but it can be higher or lower depending on your profile and your negotiations with the bank), loan interest rate, maturity of your loan, etc. The Uses & Sources (U&S) presented on the right side summarizes the main sources of funding and how the funds will be spent. The S&U will always balance (i.e. any expense must be matched by a funding source, whether it’s cash or debt).
Refinancing Assumptions
If you plan on refinancing your property in the future (for instance, after rehabbing the property in the context of a BRRRR investment), you can enter your assumptions in that section. First select “Yes” to activate the refinancing feature, then enter the year you expect to refinance the property. You can then enter your loan assumptions, including the amount, rate, tenor etc. Note that the amount can be determined based on either a dollar amount or a % of Loan to Value (LTV). The refinancing tool also includes inputs for closing costs, loan insurance and max LTV for PMI cancellation.
Rental Income & Expenses
2 operating modules are available to users of the Pro Version of the spreadsheet:
- Simple Module: ideal for single-family or small multi-family properties (up to 5 units). Operating assumptions are entered directly in the dashboard. Fixed and variable operating expenses are entered on a consolidated basis.
- Advanced Module: ideal for larger multi-family properties and apartment complexes. Operating assumptions are entered in the Multi-Units tab (only available in the Pro Version). Assumptions can be broken down by unit type.
Input your Gross Scheduled Income (i.e. rent to be received), estimated vacancy, and any other income (parking, laundry, etc.). This will give you your Gross Operating Income (GOI). Operating expenses in the template are divided into 2 categories: fixed and variable. Fixed expenses can be input as a hard number whereas variable expenses are input as a % of GSI.
Vacancy: Estimated Vacancy can be calculated in multiple ways in the spreadsheet: as a % of rent, or as a number of months or weeks per year. Note that your choice of vacancy rate should depend on multiple criteria:
- Type of asset: a studio or one-bedroom is more likely to experience a higher vacancy rate as potential tenants are more frequently occupying the property for limited periods of time. On the contrary, larger houses or apartments (3+ bedrooms) are more often rented to families for a longer period of time.
- Location of the asset: assets located in large cities or cities where supply is limited will face less vacancy than properties located in cities with an oversupply
- Type of rental: long term rentals, by definition, will face less vacancy than short term rentals such as Airbnb
- Property manager: seasoned and competent property managers can be expected to manage your rental turnover more efficiently
Note that the average vacancy rate for rental properties was 7% in the U.S. in 2018. A good estimate is usually to take 3 to 4 weeks of potential vacancies each year. Please refer to the Vacancy article for more guidance on how to estimate the appropriate vacancy rate for your property.
Other operating assumptions to be set up are: annual rent increase (for a conservative approach, use the same figure as your inflation rate assumption), operating expenses increase (by default equal to the annual rent increase), and Capex increase (same as operating expenses). Please see our article on how to estimate expenses for more information.
Both fixed and variable operating expenses can grow either using a fixed growth rate or a custom growth rate. If Custom rate is selected, your assumptions then need to be entered directly in the forecasts tab.
Rule of Thumb: the rule of thumb, also sometime called the 50% rule on the internet, is a way to quickly assess the profitability of a rental property without filing in all the operating expenses. This option allows you to make a general assumption regarding the amount of total expenses as a percentage of Gross Scheduled Income (i.e. gross rent). Note that capex, although not an operating expense, is included in this estimate when the option is selected.
You can choose to activate this option by selecting a percentage that is different from 0. For exemple, by selecting 50%, you’re making the assumption that the estimated operating expenses and capex of the property equal 50% of the rent received. The template let you select figures ranging from 40% to 60%, which are the most common estimates used as a rule of thumb. More generally, if your total expenses are below 40% of your rent, you may need to review the list of expenses and double check you are not missing one (or more). On the contrary, if your total operating expenses are above 60%, you may want to reassess the potential of the investment. Experience shows that using 50% as a rule of thumb usually fits most situations.
Note that when a percentage is selected for the Rule of Thumb, all operating expenses cells are now grey. This means that the model will not consider those cells when making financial forecasts and calculations, and will, instead, use the estimate you selected.
Dashboard Results
Net Operating Income & Cash Flow: the first box will automatically calculate your NOI and cash flow for the first year post-closing, based on the acquisition, financing and operating assumptions previously filed in. This will give you a immediate idea about the expected cash generation of the rental property immediately after purchase. Note that this template makes projections over up to 30 years, so your analysis should not be limited to the first year only.
Summary Results: This box displays key financial indicators and ratios that will help you assess in a second the return potential of your deal over the selected holding period (up to 30 years). Please refer to the article about the most useful financial ratios for more information. It includes key performance indicators (cap rate, cash on cash, price to rent) as well as more complex financial ratios (IRR, MIRR, EMX, etc.), calculated based on the projections generated by the spreadsheet.
Maximum Offering Price: This tool will help you quickly estimate the maximum purchase price you should offer in order to achieve certain financial targets, based on key financial ratios including capitalization rate, cash on cash return, cash flow (year 1), levered IRR and MIRR. Note that a more detailed Offering Price tool is available in the spreadsheet and described in the following article.
