Building financial models is an art. The only way to improve your craft is to create various economic models across several industries. Let’s try a model for an investment that is not beyond the reach of most individuals – an investment property. Before we jump into building a financial model, we should ask ourselves what drives the business we are exploring. The answer will have significant implications for how we construct the model.
Who Will Use It?
Who will use this model, and what will they use it for? A company may have a new product for which they need to calculate an optimal price. Or an investor may want to map out a project to see what kind of investment return they can expect Media Focus. Depending on these scenarios, the result of what the model will calculate may be very different. Unless you know exactly what decision the user of your model needs to make, you may start over several times until you find an approach that uses the right inputs to find the appropriate outputs.
On to Real Estate
Given certain information about the investment, we want to find out what kind of financial return we can expect from an investment property. This information would include variables such as the purchase price, rate of appreciation, the price at which we can rent it out, the financing terms available for the property, etc. Our return on this investment will be driven by two primary factors: our rental income and the appreciation of the property value. Therefore, we should begin by forecasting rental income and the appreciation of the property in consideration.
Once we have built out that portion of the model, we can use the information we have calculated to figure out how we will finance the purchase of the property and what financial expenses we can expect to incur. Next, we tackle property management expenses. We must use the property value we forecasted to calculate property taxes, so we must build the model in a certain order.
We can piece together the income statement and the balance sheet with these projections. As we put these in place, we may spot items we haven’t yet calculated and have to go back and add them in the appropriate areas. Finally, we can use these financials to project the cash flow to the investor and calculate our return on investment.
Laying Out the Model
We should also consider how to lay it out to keep our workspace clean. In Excel, one of the best ways to organize financial models is to separate certain model sections on different worksheets. We can give each tab a name that describes its information. This way, other model users can better understand where data is calculated in the model and how it flows. Let’s use four tabs in our investment property model: property, financing, expenses, and financials. Property, financing, and expenses will be the tabs on which we input assumptions and make projections for our model. The financials tab will be our results page, where we will display our model’s output in an easily understood way.
READ MORE :
- Hands-on: app lets you “Bump” smartphone pics onto your computer
- What Constitutes Separate Property in Virginia?
- Which should I buy: a PC or a Mac?
- How software-defined radio could revolutionize wireless
- OPM, Education Department CIOs resign under fire from Congress
Forecasting Revenues
Let’s start with the property tab by renaming “Property” and adding this title in cell A1 of the worksheet. By handling some of these formatting issues on the front end, we’ll have an easier time keeping the model clean. Next, let’s set up our assumptions box. A few rows below the title, type “Assumptions” and make a vertical list of the following inputs:
Purchase Price
Initial Monthly Rent
Occupancy Rate
Annual Appreciation
Annual Rent Increase
Broker Fee
Investment Period
We’ll set up an input field in the cells to the right of each input label by adding a realistic placeholder for each value. We will format each of these values to be blue. This is a common modeling convention to indicate that these are input values. This formatting will make understanding how the model flows easier for us and others. Here are some corresponding values to start with:
$250,000.00
$1,550.00
95.00%
3.50%
1.00%
6.00%
Four years
The purchase price will be the price we expect to pay for a particular property. The initial monthly rent will be the price we wish to rent out of the property. The occupancy rate will measure how well we keep the property rented out (95% occupancy will mean there will only be about 18 days that the property will go un-rented between tenants each year).
Annual appreciation will determine the rate at which the value of our property increases (or decreases) each year. Annual rent increases will determine how much we will increase each year. The broker fee measures the percentage of the property’s sale price we will have to pay a broker when we sell the property. The investment period is how long we will hold the property before we sell it. Now that we have a good set of property assumptions, we can make calculations based on these assumptions.
