The Dashboard is where you can input all the assumptions regarding the transaction you want to review. The tab is split in 5 sections:
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 through time via two methods:
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 this money 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).
Rental Income & Expenses
2 operating modules are available to users of the Pro Version of the spreadsheet:
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).
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:
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.
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.
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.
Net Operating Income & Cash Flow
This 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.
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.
Terms & Conditions
© 2021 RealEstateSpreadsheets.com – All rights reserved.