Building financial models is an art. The only way to improve your craft is to build a variety of financial models across a number of industries. Let’s try a model for an investment that is not beyond the reach of most individuals — an investment property.

 

You can follow along by downloading the investment property model.

Before we jump into building a financial model, we should ask ourselves what drives the business that we are exploring. The answer will have significant implications for how we construct the model.

 

For example, a financial institution such as a bank is driven by its assets (investments, loans, mortgages, etc.), therefore we would probably be better off forecasting the growth of the company’s balance sheet. A retail store, on the other hand, is driven by the sales of its products. Therefore, we should focus on forecasting revenues and the income statement.

 

Who Will Use It?

 

Other questions to ask are who will be using this model and what will they be using 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 he or she can expect.

 

Depending on these scenarios, the end 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 find yourself starting over several times until you find an approach that uses the right inputs to find the appropriate outputs.

 

On to Real Estate

 

In our scenario, we want to find out what kind of financial return we can expect from an investment property given certain information about the investment. 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 fore 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 as a result.

 

The next section we can tackle is forecasting the property management expenses. We will need to use the property value that we forecasted in order to be able to calculate property taxes, so it is important that we build the model in a certain order.

 

Once we have these projections in place, we can begin to piece together the income statement and the balance sheet. As we put these in place, we may spot items that we haven’t yet calculated and we may have to go back and add them in the appropriate places.

 

Finally, we can use these financials to project the cash flow to the investor and calculate our return on investment.

 

Laying Out the Model

 

Since we are about to build a somewhat complicated model, we should think about how we want to lay it out so we keep our workspace clean. In Excel, one of the best ways to organize financial models is to separate certain sections of the model on different worksheets.

 

By putting calculations and projections that are closely related in the same worksheet and separating other calculations that are more relevant to other sections of the model on separate tabs (worksheets), we keep our model organized.

 

We can give each tab a name that describes the information contained in it. This way, other users of the model can better understand where data is calculated in the model and how it flows.

 

In our investment property model, let’s use four tabs: property, financing, expenses and financials. Property, financing and expenses will be the tabs on which we input assumption and make projections for our model. The financials tab will be our results page where we will display the output of our model in a way that’s easily understood (in the form of financial statements).

 

Forecasting Revenues

 

First Things first, let’s start with the property tab by renaming the tab “Property” and adding this title in cell A1 of the worksheet. By taking care of some of these formatting issuing 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

 

In the cells to the right of each input label, we’ll set up an input field by adding a realistic placeholder for each value. We will format each of these values to be blue in color. This is a common modeling convention to indicate that these are input values. This formatting will make it easier for us and others to understand how the model flows. Here are some corresponding values to start with:

 

0,000.00

,550.00

95.00%

3.50%

1.00%

6.00%

4 years

 

The purchase price will be the price we expect to pay for a particular property. The initial monthly rent will be the price for which we expect to rent out the property. The occupancy rate will measure how well we keep the property rented out (95% occupancy will mean that there will only be about 18 days that the property will go un-rented between tenants each year).

 

Annual appreciation will determine the rate that the value of our property increases (or decreases) each year. Annual rent increase will determine how much we will increase the rent each year. The broker fee measures what percentage of the sale price of the property we will have to pay a broker when we sell the property.

 

The investment period is how long we will hold the property for before we sell it. Now that we have a good set of property assumptions down, we can begin to make calculations based on these assumptions.

 

A Note on Time Periods

 

There are many ways to begin forecasting out values across time. You could project financials monthly, quarterly, annually or some combination of the three. For most models, you should consider forecasting the financials monthly during the first couple years.

 

By doing so, you allow users of the model to see some of the cyclicality of the business (if there is any). It also allows you to spot certain problems with the business model that may not show up in annual projections (such as cash balance deficiencies). After the first couple of years, you can then forecast the financials on an annual basis.

 

For our purposes, annual projections will cut down on the complexity of the model. One side effect of this choice is that when we begin amortizing mortgages later, we will wind up incurring more interest expense than we would if we were making monthly principal payments (which is what happens in reality).

 

Another modeling choice you may want to consider is whether to use actual date headings for your projection columns (12/31/2010, 12/31/2011, …). Doing so can help with performing more complex function later, but again, for our purposes, we will simply use 1, 2, 3, etc. to measure out our years. In Excel, we can play with the formatting of these numbers a bit to read:

 

Year 1  Year 2  Year 3  Year 4 …

 

These numbers should be entered below our assumptions box with the first year starting in at least column B. We will carry these values out to year ten. Projections made beyond ten years do not have much credibility so most financial models do not exceed ten years.

 

On to the Projections

 

Now that we have set up our time labels on the “Property” worksheet, we are ready to begin our projections. Here are the initial values we want to project for the next ten years in our model:

 

Property Value

Annual Rent

Property Sale

Broker Fee

Mortgage Bal.

Equity Line Bal.

Net Proceeds

Owned Property Value

 

Add these line items in column A just below and to the left of where we added the year labels.

 

The property value line will simply project the value of the property over time. The value in year one will be equal to our purchase price assumption and the formula for it will simply reference that assumption. The formula for each year to the right of the first year will be as follows:

 

=B14*(1+$ B)

 

Where B14 is the cell directly to the left of the year in which we are currently calculating the property value and $ B is an absolute reference to our “Annual Appreciation” assumption. This formula can be dragged across the row to calculate the remaining years for the property value.

 

The annual rent line will calculate the annual rental income from the property each year. The formula for the first year appears as follows:

 

=IF(B12>=$ B,0,B5*12*$ B)

 

B12 should be the “1″ in the year labels we created. $ B should be an absolute reference to our investment period assumption (the data in our assumption cell should be an integer even if it is formatted to read “years,” otherwise the formula will not work). B5 should be

Tags: Financial, Investment, Model, Modeling, Property —