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.
Depending on these scenarios, the model’s calculation result 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.
READ MORE :
- 6 Simple Steps To Help Mobile Visitors To Your Website
- Why Do Small Businesses Need to Invest in Mobile Marketing?
- Nokia’s Old Flagship Devices
- Mallorca Property Market Report
- Why Invest In Property?
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 the 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 do so, 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
We should also consider how we want to arrange it 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 and how it flows.
Let’s use four tabs in our investment property model: property, financing, expenses, and financials. The tabs on which we input assumptions and make projections for our model will be property, financing, and expenses. The financials tab will be our results page, where we will display the output of our model in an easily understood way.
Forecasting Revenues
Start with the property tab by renaming “Property” and adding this title in cell A1 of the worksheet. By fixing 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 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 sale price we will have to pay a broker when selling 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.
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 monthly financials during the first few years.
After the first couple of years, you can then forecast the financials on an annual basis. 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).
For our purposes, annual projections will reduce the complexity of the model. One side effect of this choice is that when we begin amortizing mortgages later, we will incur more interest expense than we were making monthly principal payments (which is what happens in reality).
Another modeling choice is using actual date headings for your projection columns (12/31/2010, 12/31/2011, etc.). Doing so can help with performing more complex functions later, but again, for our purposes, we will 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 column B. We will carry these values out to year ten. Projections made beyond ten years have little credibility, so most financial models 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, where we added the year labels. The property value line will project the value of the property over time. The value in year one will equal our purchase price assumption, and its formula will reference that assumption. The formula for each year to the right of the first year will be as follows:
=B14*(1+$B$7)
B14 is the cell directly to the left of the year we are currently calculating the property value, and $B$7 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$10,0,B5*12*$B$6)
B12 should be the “1” in the year labels we created. $B$10 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 reference our monthly rent assumption, and $B$6 should reference the occupancy rate.
This function says that if our investment period is less than the year this value is calculated, the result must be zero (we will no longer own the property after it is sold, so we can’t collect rent). Otherwise, the formula will calculate the annual rent, the monthly rent multiplied by twelve, and the occupancy rate.
For subsequent years, the formula will look similar to:
=IF(C12>=$B$10,0,B16*(1+$B$8))
Again, if the investment period is less than the year this value is calculated, the result will be zero. Otherwise, we take the weight of last year’s rental income and increase it by our annual rent increase assumption in cell $B$8.
Time to Exit
Now that we have forecasted property values and rental income, we can predict the proceeds from the property’s eventual sale. To calculate the net proceeds from the sale of our property, we will need to forecast the above values: property sale price, broker fee, mortgage balance, and equity line balance.
The formula for forecasting the sale price is as follows:
=IF(B12=$B$10,B14,0)
This formula states that if the current year (B12) is equal to our investment period ($B$10), then our sale price will be similar to our projected property value in that particular year (B14). Otherwise, if the year is not the year we plan to sell the property, there is no sale, and the sale price is zero.
The formula to calculate broker fees takes a similar approach:
=IF(B18=0,0,B18*$B$9)
This formula states that broker fees are zero if the sale price for a particular year (B18) equals zero. If there’s no sale, there are no broker fees. If there is a sale, broker fees equal the sale price (B18) multiplied by our assumption for broker fees ($B$9).
Our mortgage and equity line balances will be calculated on the next worksheet, and we will leave two blank lines as placeholders for these values. Our net proceeds from the property sale will be the sale price, fewer broker fees, less the mortgage balance, and less the home equity line balance.
Let’s add one more line called “Owned Property Value.” This line will show the value of our property, reflecting a zero value once we have sold it. The formula will be:
=IF(B12>=$B$10,0,B14)
B12 refers to the current year in our year-label row. $B$10 refers to our investment period assumption, and B14 refers to the current year’s value in the property value line we calculated. This line represents our property value line, but it will show zero for the property value after we sell the property.
On to the Financing
Now, let’s model how we will finance the property acquisition. Let’s name a new tab, “Financing,” and add the title “Financing” at the top of the worksheet. We first need to know how much we need to finance.
Let’s type “Purchase Price” a few lines below the title. To the right of this cell, refer to our purchase price assumption from the “Property” tab (=Property!B4). We will format the text of this cell to be green because we are linking to information on a different worksheet. Formatting text in green is a common financial modeling convention to help track where information flows.
Below this line, let’s type “Working Capital.” To the right of this cell, let’s enter an assumption of $5,000.00 (formatted in blue text to indicate an input). Our working capital assumption represents additional capital we think we’ll need to cover the investment property’s day-to-day management. We may have certain expenses not fully covered by our rental income, and our working capital will help ensure we don’t run into cash flow problems.
Below the working capital line, let’s type “Total Capital Needed,” To the right of this cell, sum the values of our purchase price and working capital assumption. This sum will be the total amount of capital we will need to raise.
Capital Sources
A couple of lines below our “Total Capital Needed,” let’s create a capital sources box. This box will have six columns with the headings: source, amount, % purchase price, rate, term, and annual payment. Two typical sources of capital for acquiring a property are a mortgage and an equity line of credit (or loan). Our final source of money (for this model, anyway) will be our cash or equity.
In the sources column, let’s add “First Mortgage,” “Equity Line of Credit,” and “Equity” in the three cells below our sources heading. For a typical mortgage, a bank will usually lend up to 80% of the property’s value on a first mortgage, so let’s enter 80% on the line for the first mortgage under the % purchase price heading (again, formatted in blue to indicate an input value).
We can now calculate the amount of our first mortgage in the amount column with the following formula:
=B5*C11
B5 refers to our purchase price, and C11 refers to our % purchase price assumption.
In the current market, banks are reluctant to offer equity lines of credit if less than 25% equity is invested in the property, but let’s pretend they are willing to lend a bit. Let’s assume they will lend us another 5% of the property value as an equity line. Enter 5% (in blue) in the equity line of credit under the % purchase price heading.
We can use a similar formula to calculate the equity line amount in the amount column:
=B5*C12
Now that we have the amount of bank financing available for our purchase, we can calculate how much equity we need. Under the amount heading in the row for equity, enter the following formula:
=B7-B11-B12
B7 is our total financing needed. B11 is the financing from the first mortgage, and B12 is the financing from the equity line of credit. Again, we assume we’ll have to cough up the cash for anything we cannot finance through the bank.
