These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, April 12, 2012

Rental Property Analysis

While I didn’t perform work for any official business, the work performed was to address a business need for myself and my father. About seven years ago we began investing in rental properties, at the rate of approximately 1 per year. Right now we currently have seven rental properties, and we are continuously looking for the “right deal”. As a “side-business” we do not have a significant amount of time to devote to rental property analysis. However, my father, a retired CPA, and me, an aspiring business consultant, have slightly different views as to the important parts to analyze with a rental property. As such, we both run various analyses, typically in Excel with different results based on a variety of factors. To combine our analyses, numerous spreadsheets need to be created. The task can be exhausting.

Therefore, to combat this exhausting task, I decided to create an easy to use rental property analysis spreadsheet. The user interface is such that it can be easily modified with each property analyzed. It includes a number of different evaluators from NOI calculations to sensitivity analysis based on the various yearly increases in rent.

The project has one centralized spreadsheet where all calculations are combined to give an overview to each rental property considered. Following the overview, there is a tab dedicated to each of the following: sensitivity analysis, purchase price information, financing assumptions, rental income assumptions, predicted yearly expenses, and finally a tab that will bring in current rates from bankrate.com.

Based on user-entered information, the spreadsheet provides the return on invested capital over a 10-year period for each rental property. As a general rule, my father and I try not to invest in a property that has a predicted ROI of lower than 12% in the first year, and a minimum of 15% within the first four years. The ROI calculation is based on revenues, expenses, and acquisition information; all provided by the user through simple to use userforms. These userforms can be accessed from the ribbon under the real estate tab, or individually on each of the dedicated sheets.